From: Gianni Ciolli <gianni.ciolli@2ndquadrant.it>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: simon@2ndQuadrant.com
Cc: misa.simic@gmail.com
Cc: gabriele.bartolini@2ndquadrant.it
Cc: marco.nenciarini@2ndquadrant.it
Cc: pgsql@j-davis.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [PATCH] Support for foreign keys with arrays
Date: Sun, 17 Jun 2012 17:02:53 +0100
Message-ID: <20120617160253.GA26190@leggeri.gi.lan> (raw)
In-Reply-To: <4FDDAA39020000250004858F@gw.wicourts.gov>
References: <4FDDAA39020000250004858F@gw.wicourts.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: gianni.ciolli@2ndquadrant.it, Kevin.Grittner@wicourts.gov, simon@2ndQuadrant.com, misa.simic@gmail.com, gabriele.bartolini@2ndquadrant.it, marco.nenciarini@2ndquadrant.it, pgsql@j-davis.com
Subject: Re: [PATCH] Support for foreign keys with arrays
In-Reply-To: <20120617160253.GA26190@leggeri.gi.lan>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox