Message-ID: <4B7178CF.60502@gmail.com>
Date: Tue, 09 Feb 2010 08:01:35 -0700
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.5) Gecko/20091130 SUSE/3.0.0-1.1.1 Thunderbird/3.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: 'image' table with relationships to different objects
References: <20100209074909.GA11838@apartia.fr>
 <4B714E12.1090207@archonet.com>
In-Reply-To: <4B714E12.1090207@archonet.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

You can also invert this, making all the image owner share a common base
table and then images are dependent on that base

base (id, type) where type is an enumeration or some such
person (id, name, etc) where id is FK to base id
locations (id, address, etc) where id is FK to base.id
events(id, date, etc) where id is FK to base.id
images(id, baseid) where baseid is FK to base.id

views across base to the "data" tables for easier sql if desired
ORM: person location and event would inherit from base

On 02/09/2010 04:59 AM, Richard Huxton wrote:
> 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.
>