From: | Steve Midgley <science(at)misuse(dot)org> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | vindex+lists-pgsql-sql(at)apartia(dot)org |
Subject: | Re: many-to-many relationship |
Date: | 2008-10-08 00:16:39 |
Message-ID: | 20081008001656.366F537BA2C@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Louis-David Mitterrand | 2008-10-08 09:25:10 | Re: many-to-many relationship |
Previous Message | Pavel Stehule | 2008-10-07 07:54:52 | Re: Array from INSERT .. RETURNING in plpgsql? |