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.cio...@2ndquadrant.it | www.2ndquadrant.it -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
