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.

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

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to