Louis-David Mitterrand wrote:
Hi,
Say you have several objects (tables): person, location, event, etc. all
of which can have several images attached.
What is the best way to manage relations between a single 'image' table
and these different objects?
For now each 'image' row has pointers to id_person, id_location,
id_event, etc. (only one of which is used for any given row).
Is there a better way, more elegant way to do it, without using
redundant id_* pointers on each row and yet still enforce foreign keys?
The typical way to do this would be to have your image table be just
about images, and then to isolate the relationship information into
mapping tables. Those would look like:
image <=> people
(image_id, person_id), with the primary key being the pair of columns.
In SQL, roughly:
create table image_people_map (
image_id integer not null,
person_id integer not null,
primary key (image_id, person_id)
);
Similarly, for locations it'd be (image_id, location_id), and for events
(image_id, event_id). Then you can have a single image associated with
any number of people, events, or locations.
Regards,
--
Dave Steinberg
http://www.geekisp.com/
http://www.steinbergcomputing.com/
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
