Date: Sun, 17 Jun 2012 17:02:53 +0100
From: Gianni Ciolli <gianni.ciolli@2ndquadrant.it>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: simon@2ndQuadrant.com, misa.simic@gmail.com,
	gabriele.bartolini@2ndquadrant.it, marco.nenciarini@2ndquadrant.it,
	pgsql@j-davis.com, pgsql-hackers@postgresql.org
Subject: Re: [PATCH] Support for foreign keys with arrays
Message-ID: <20120617160253.GA26190@leggeri.gi.lan>
References: <4FDDAA39020000250004858F@gw.wicourts.gov>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4FDDAA39020000250004858F@gw.wicourts.gov>
User-Agent: Mutt/1.5.20 (2009-06-14)

On Sun, Jun 17, 2012 at 09:58:17AM -0500, Kevin Grittner wrote:
> Simon Riggs  wrote:
> Misa Simic  wrote:
>  
> >> IMO, both approaches make sense...
> >
> > Agreed.
>  
> Can someone provide a practical example of a "foreign key with array"
> use case?  The only situations I'm able to think of right now are the
> same cases where you would now use a table with primary keys of two
> tables to provide a many-to-many linkage.  Does this proposed feature
> handle other cases or handle this type of case better?

The way I think about "array foreign keys" is that they represent the
"aggregated" form of a classical foreign key.

In the aggregated form, each row in the referencing side represents a
group of rows in the non-aggregated form.

One advantage is that constraints on each group of rows as a whole are
now possible, because they become constraints on a single row in the
aggregated form.

Example. If you have a table of points, then you can have a table of
polygons where each polygon contains an array of points. The
non-aggregated model would instead require an additional point_polygon
table which references both the point and the polygon table, because
the point <-> polygon relationship is many-to-many. In the aggregated
model, you can easily specify a CHECK constraint that requires each
polygon to have at least three points, while the corresponding
condition cannot be specified in the non-aggregated model.

Cheers,
Dr. Gianni Ciolli - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gianni.ciolli@2ndquadrant.it | www.2ndquadrant.it