From: Rob Sargent <robjsargent@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: 'image' table with relationships to different objects
Date: Tue, 09 Feb 2010 08:01:35 -0700
Message-ID: <4B7178CF.60502@gmail.com> (raw)
In-Reply-To: <4B714E12.1090207@archonet.com>
References: <20100209074909.GA11838@apartia.fr>
<4B714E12.1090207@archonet.com>
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.
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: robjsargent@gmail.com
Subject: Re: 'image' table with relationships to different objects
In-Reply-To: <4B7178CF.60502@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox