Re: [PATCH] Support for foreign keys with arrays

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(dot)ciolli(at)2ndquadrant(dot)it | www.2ndquadrant.it