many-to-many relationship

[Louis-David Mitterrand-13]

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,

--
http://www.lesculturelles.net

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Dave Steinberg-2]

Louis-David Mitterrand wrote:

> 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?

The typical way to do this would be to have your image table be just
about images, and then to isolate the relationship information into
mapping tables.  Those would look like:

image <=> people
(image_id, person_id), with the primary key being the pair of columns.
In SQL, roughly:

create table image_people_map (
   image_id integer not null,
   person_id integer not null,
   primary key (image_id, person_id)
);

Similarly, for locations it'd be (image_id, location_id), and for events
(image_id, event_id).  Then you can have a single image associated with
any number of people, events, or locations.

Regards,
--
Dave Steinberg
http://www.geekisp.com/
http://www.steinbergcomputing.com/

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Louis-David Mitterrand-13]

On Mon, Oct 06, 2008 at 09:25:09AM -0400, Dan McFadyen wrote:
> Hello,
>
> Simplest way I can think of is create 3 relation tables, a person/image
> table, location/image table and event/image table.
>
> Each is just made up for 2 foreign keys to the first ID and image ID,
> using both as the PK for the table.

On Mon, Oct 06, 2008 at 09:30:41AM -0400, Dave Steinberg wrote:
> The typical way to do this would be to have your image table be just  
> about images, and then to isolate the relationship information into  
> mapping tables.  Those would look like:
>
> image <=> people
> (image_id, person_id), with the primary key being the pair of columns.  
> In SQL, roughly:

Thanks Dan and Dave,

you suggested the same solution which seems the most reasonable.

--
http://www.lesculturelles.net

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Steve Midgley-3]

At 06:20 AM 10/7/2008, pgsql-sql-owner@... wrote:

>Date: Mon, 6 Oct 2008 15:08:02 +0200
>From: Louis-David Mitterrand <vindex+lists-pgsql-sql@...>
>To: pgsql-sql@...
>Subject: many-to-many relationship
>Message-ID: <20081006130802.GA23018@...>
>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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Louis-David Mitterrand-13]

On Tue, Oct 07, 2008 at 05:16:39PM -0700, Steve Midgley wrote:

>
> 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.

Hi Steve,

So in your solution the f_table column is just text which needs to be
validated by a custom trigger?

--
http://www.lesculturelles.net

--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Steve Midgley-3]

At 05:20 PM 10/8/2008, pgsql-sql-owner@... wrote:

>Date: Wed, 8 Oct 2008 11:25:10 +0200
>From: Louis-David Mitterrand <vindex+lists-pgsql-sql@...>
>To: pgsql-sql@...
>Subject: Re: many-to-many relationship
>Message-ID: <20081008092510.GA23361@...>
>Mail-Followup-To: pgsql-sql@...
>References: <20081007132118.C80641E81548@...>
><20081008001705.88A7B5B93F8F9@...>
>In-Reply-To: <20081008001705.88A7B5B93F8F9@...>
>X-Archive-Number: 200810/23
>X-Sequence-Number: 31665
>
> >
> > |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.
>
>Hi Steve,
>
>So in your solution the f_table column is just text which needs to be
>validated by a custom trigger?

Hi,

Yup - that's exactly what I'm suggesting. Storing the text value of the
related tables right in the table in question. It might seem insane,
but in my experience it works out reasonably well. Ruby on Rails has
popularized the approach, using it both in the data backend, as well as
in the OO frontend (so Rugy object class to be instantiated is chosen
by the text value of "f_table" for a given row - hence the
polymorphism).

http://wiki.rubyonrails.org/rails/pages/UnderstandingPolymorphicAssociations

There are some situations where this approach could create problems but
if in general all you're doing is select statements along these lines:

select * from images where
f_table = 'person' and f_id = '1234'

There's not much to go wrong. (Famous last words).

And regarding the custom validation by trigger, I'd think that would
work just fine. I'm not an expert on triggers, rules and constraints in
Pg though. (I do all my validation in the middleware, which might give
some people here high blood pressure). :)

Keep us posted on which solution you choose and how it works out for
you!

Steve


--
Sent via pgsql-sql mailing list (pgsql-sql@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql