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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
