went out....
d
_____
From: Katz, Dov B (IT)
Sent: Sunday, January 25, 2004 11:04 PM
To: [EMAIL PROTECTED]; CF-Talk
Subject: RE: How to model "all" in a many-to-many
I'm coming into this thread kinda late, but why not take the
blacklist-whitelist approach.. This seems to me to be the proper model
for the data being discussed. A user has a LIST of FOOD... Don't forget
to create the LIST representation. I think creating it will help the
model be more precise.
Instead of a user-food relation (table) make a UserFoodList
table, which can have type="Black" or type="White"... Then another
table called "UserFoodListItems:" with <listID,FoodID> pairs in it....
You add items to the Listitems table, and depending on whether
the list is black or white, the proper logic can be applied....
i.e. if (type='black') user likes ALL items EXCEPT the ones in
the items table, if (type=white) the user likes NO items EXCEPT the ones
in the items table...
Create 2 queries (existence of row in list table which is a
blacklist defaults to all items) Union the queries, and sort the
union....
This provides the most maintainable design, which does not
require the overhead of the entire list being duplicated for every "All
Foods" lover (such as myself).
You can also set up various views to better get at this data, as
needed.
-Dov
(see example below)
---------------------
Example: (off the top of my head, dont hold me to the code, I'm
typing it here for the first time)
create TABLE UserFoodList (
ListID integer Identity(1,1) not null,
UserID integer not null,
ListType char(1) default 'W',
ListName varchar(40) /*** optional ***/
PRIMARY KEY (ListID), Foreign Key (UserID) REFERENCES
_users_table
Create TABLE UserFoodListItems(
ListID integer not null,
FoodID integer not null,
Primary key (ListID, FoodID)
Foreign key (ListID) References UserFoodList
Foreign Key (FoodID) references SomeFoodTable
------------------------------
Then to get the user's favorite food count in sql and
pseudocode, fix order by/group by's as necessary, I'm just trying to
make the point....
cfset the following: f as TOTAL_FOOD_COUNT;
select L.UserID, (Select count(*) from SomeFoodTable ) -
count(*) from UserFoodListItems I inner join UserFoodList L on
L.listid=I.listID
where L.type='B'
UNION
select L.UserID, count(*) From UserFoodListItems I inner join
UserFoodList L on L.ListID=I.ListID) where L.Type='W'
---
Group by L.UserID
------------------
-----Original Message-----
From: Taco Fleur [mailto:[EMAIL PROTECTED]
Sent: Sun 1/25/2004 10:41 PM
To: CF-Talk
Cc:
Subject: RE: How to model "all" in a many-to-many
Hmm, why handle it with code when it can be handled with
relationships?
I would never handle anything in code that could be done
with the native
functionality of the DBMS.
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
I would argue that this problem falls outside the
boundary of relational
database design for the most part, and instead falls
within business logic.
To the best of my knowledge, there's no correct answer
to this specific to
database design. In similar cases, I've simply added the
Boolean field to
the primary table as you described, and relied on other
logic within stored
procedures or application code to deal with keeping the
intersection table
updated as appropriate.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
_____
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
