Misa Simic <misa(dot)simic(at)gmail(dot)com> wrote:
> 2012/6/17 Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)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