I wouldn't add an "All foods" in the intersection table. You certainly
could have an all food boolean in the persons table (note: table names
are normally plural since they contain a set of information) You could
write a stored procedure to delete all entries for a person and add an
entry for each food type for that person in the intersection table. I
would think that's the purist way to handle is as your counts would
always be correct. If you added a food, just run that stored procedure
to update anyone with "All foods" after the new food type is created.
If you delete a food type, you'd of course need to delete all entries in
the intersection table first, then delete the food type most likely in
one transaction and/or stored procedure.
That's how I'd do it.
Tom
-----Original Message-----
From: John Quarto-vonTivadar [mailto:[EMAIL PROTECTED]
Sent: Sunday, January 25, 2004 6:46 PM
To: CF-Talk
Subject: How to model "all" in a many-to-many
I ran into a fascinating problem today, but I'm pretty sure it's not
unique.
Surely someone on here has thought about this before:
Imagine you have a table of Person and a table of Food, and you have a
PersonFood table that models which people like which food using two
foreign
keys, such as PersonID and FoodID. Simple enough
Now, that's great if Sally like apples, and Billy likes apples and
walnuts,
and Joe likes tacos. But what should be done about BigBob, who likes
*all*
foods?
If we just mark off BigBob as liking all the foods using the PersonFood
then
any time a new Food is added , BigBob will be out of date (he really
does
like *all* foods!). Yet if we create a special food called "All Food"
and
mark BigBob with that then the "count" of how many foods he likes (which
is
really just the count of all Foods) is equal to one instead of the
actual
food count, and it's still possible to mark BigBob as liking "All Food"
as
well as "apples" and get a food count of 2! ugh. And it's just as wrong
to
say "well anyone who does not have any records in the PersonFoods table
means that they like all foods" because then you have no way of showing
a
Person who really like zero of the Foods. Ugh, again.
So I'm curious, what do you much more experienced DB guys do in such a
case?
Solve it with a stored procedure? Use a boolean field in the Person
table
for "all food eaters"? Or is there an actual way to model this with a
field
in the PersonFood table?
thanks for any hints!
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
