At 06:20 AM 10/7/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Mon, 6 Oct 2008 15:08:02 +0200
>From: Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org>
>Subject: many-to-many relationship
>Say you have several objects (tables): person, location, event, etc.
>of which can have several images attached.
>What is the best way to manage relations between a single 'image'
>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
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.
|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,
pgsql-sql by date
|Next:||From: Louis-David Mitterrand||Date: 2008-10-08 09:25:10|
|Subject: Re: many-to-many relationship|
|Previous:||From: Pavel Stehule||Date: 2008-10-07 07:54:52|
|Subject: Re: Array from INSERT .. RETURNING in plpgsql?|