At 06:20 AM 10/7/2008, [EMAIL PROTECTED] wrote:
Date: Mon, 6 Oct 2008 15:08:02 +0200
From: Louis-David Mitterrand <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Subject: many-to-many relationship
Message-ID: <[EMAIL PROTECTED]>
X-Archive-Number: 200810/13
X-Sequence-Number: 31655
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?
Thanks,
Hi,
I think the relationship tables method works pretty well but I have
another suggestion. You could store the Foreign table name within image
table as well as the Foreign key.
|id|image_url|f_table|f_key
|1 |url......|person |1234
|2 |url2.....|event |5678
I think this is called a "polymorphic join" but I could be wrong about
that. I'd guess you could construct a rule or trigger to validate the
foreign key data on insert/update but that's out of my skill area.
Hope that helps a little,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
