Misa Simic <misa.si...@gmail.com> wrote: > 2012/6/17 Kevin Grittner <kevin.gritt...@wicourts.gov> >> 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? > > I can't imagine either other usablity... Just many-to-one > linkage... or to have many-to-many link with less rows in middle > table... The many-to-one case seems like it is better handled in the other direction -- with the referenced table holding the set of valid keys and the referencing table holding the single key. (I believe the general case of this is what Jeff called an "inclusion constraint" -- a feature he wants to add at some point.) I can't think of a use case where that would not be better for this type of relationship than putting the array on the referencing side. The many-to-many relationship does seem like a potentially useful feature, at least in some cases. For example, a message board where a limited number of tags can be attached to each message -- the message could contain an array of tag IDs. Clearly this could be done as a table holding message_id and tag_id, but it seems potentially more convenient from a programming perspective to have an array of tag_id values in the message table. Logically, you are associating each of these with the primary key of the row it is in. Are there other obvious use-cases? If nobody can put one forward, this seems like a good "reality test" for proposed behaviors in any corner cases where "correct" behavior isn't obvious -- what would you want to do to the data if it were in the separate table with just the primary keys to link the two tables? > What is better - I think should be measured... It would make an interesting test to compare performance of a suitably sized data set and reasonable workload for both techniques. Of course, that's a non-trivial amount of work. It seems like people may be able to justify this just on ease-of-use, versus claiming that it's an optimization. -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers