person table named 'likesAllFood', and then write a stored proc that'll
reset the personFood table to contain all relationships between any person
with that flag set and ALL foods. Finally, set that stored proc as a
trigger to fire on INSERT on the food table, and you'll be set.
Cheers,
barneyb
> -----Original Message-----
> From: John Quarto-vonTivadar [mailto:[EMAIL PROTECTED]
> Sent: Sunday, January 25, 2004 4: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]
