trust my answer maybe should be asking the question in a DB mailing list or
forum?
The only CORRECT way of doing this is to store an entry for each food in the
foreign table.
And I'd suggest you stick non plural naming anywhere, because as soon as you
start to use plural naming you will have to start thinking "now what did I
use there, food or foods?" Stick to non-plural...
Taco Fleur
Blog <http://www.tacofleur.com/index/blog/>
http://www.tacofleur.com/index/blog/
Methodology http://www.tacofleur.com/index/methodology/
0421 851 786
Tell me and I will forget
Show me and I will remember
Teach me and I will learn
-----Original Message-----
From: Matthew Walker [mailto:[EMAIL PROTECTED]
Sent: Monday, 26 January 2004 11:23 AM
To: CF-Talk
Subject: RE: How to model "all" in a many-to-many
What about the case where I like all foods except mushrooms? Perhaps a nice
approach would be to have a flag on the person's record saying
"automatically assume I like newly added foods". Then when you add a food,
either a stored procedure or whatever would add the relationship for all
people with this flag set. If later it turned out that a person doesn't like
octopus thickshakes after all then that single relationship could be
deleted.
-----Original Message-----
From: Tom Nunamaker [mailto:[EMAIL PROTECTED]
Sent: Monday, 26 January 2004 1:04 p.m.
To: CF-Talk
Subject: RE: How to model "all" in a many-to-many
Hi John,
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]
