Skip site navigation (1) Skip section navigation (2)

Re: many-to-many relationship

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 (view raw or flat)
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


pgsql-sql by date

Next:From: Louis-David MitterrandDate: 2008-10-08 09:25:10
Subject: Re: many-to-many relationship
Previous:From: Pavel StehuleDate: 2008-10-07 07:54:52
Subject: Re: Array from INSERT .. RETURNING in plpgsql?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group