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
Subject: Re: many-to-many relationship
Date: 2008-10-09 19:16:02
Message-ID: 20081009191653.99AF137BD49@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-sql
At 05:20 PM 10/8/2008, pgsql-sql-owner(at)postgresql(dot)org wrote:
>Date: Wed, 8 Oct 2008 11:25:10 +0200
>From: Louis-David Mitterrand <vindex+lists-pgsql-sql(at)apartia(dot)org>
>To: pgsql-sql(at)postgresql(dot)org
>Subject: Re: many-to-many relationship
>Message-ID: <20081008092510(dot)GA23361(at)apartia(dot)fr>
>Mail-Followup-To: pgsql-sql(at)postgresql(dot)org
>References: <20081007132118(dot)C80641E81548(at)mx2(dot)hub(dot)org> 
><20081008001705(dot)88A7B5B93F8F9(at)zenon(dot)apartia(dot)fr>
>In-Reply-To: <20081008001705(dot)88A7B5B93F8F9(at)zenon(dot)apartia(dot)fr>
>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 


pgsql-sql by date

Next:From: Bart DegryseDate: 2008-10-10 07:06:00
Subject: Re: trigger parameters, what am I doing wrong ??
Previous:From: Tom LaneDate: 2008-10-09 17:22:16
Subject: Re: trigger parameters, what am I doing wrong ??

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