Re: many-to-many relationship

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>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: many-to-many relationship
>Message-ID: <20081006130802(dot)GA23018(at)apartia(dot)fr>
>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