Message-ID: <4B714E12.1090207@archonet.com>
Date: Tue, 09 Feb 2010 11:59:14 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.8pre) Gecko/20100129 Lightning/1.0b1 Shredder/3.0.2pre
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: 'image' table with relationships to different objects
References: <20100209074909.GA11838@apartia.fr>
In-Reply-To: <20100209074909.GA11838@apartia.fr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 09/02/10 07:49, Louis-David Mitterrand wrote:
> Hello,
>
> In my database I have different object types (person, location, event,
> etc.) all of which can have several images attached.
>
> What is the best way to manage a single 'image' table with relationships
> to (potentially) many different object types while keeping referrential
> integrity (foreign keys)?

The "clean" way to do this would be with a number of joining tables:

images    (img_id, file_name, title ...)
persons   (psn_id, first_name, last_name, ...)
locations (loc_id, loc_name, lat, lon, ...)
events    (evt_id, evt_name, starts_on, ends_on, ...)

person_images   (psn_id, img_id)
location_images (loc_id, img_id)
event_images    (evt_id, img_id)

You might then want a view over these joining tables to see what images 
go where...

CREATE VIEW all_images AS
SELECT
   i1.img_id,
   i1.file_name,
   'PERSON'::text AS link_type,
   p.first_name || ' ' || p.last_name AS linked_name
FROM
   images i1
   JOIN person_images pi ON i1.img_id = pi.img_id
   JOIN persons p ON pi.psn_id = p.psn_id
UNION ALL
SELECT
   i2.img_id,
   i2.file_name,
   'LOCATION'::text AS link_type,
   l.loc_name AS linked_name
FROM
   images i2
   JOIN location_images li ON i2.img_id = li.img_id
   JOIN locations l ON li.loc_id = l.loc_id
...

You could do something clever with inheritance on the joining tables, 
but it's better to keep things simple imho.

-- 
   Richard Huxton
   Archonet Ltd