Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
CLEE25
Starting Member
14 Posts |
 Posted - 2010-02-27 : 11:38:45
|
| Hello all, I am hoping you can offer some advice on a project I am working on. It is an online ordering system for a deli. Pretty straight forward except for all the modifications one can make on a sandwich :) For example, you can select a Roast Beef Sandwhich, but then you have like 50 options such as Tomatoes, Onions, lettuce, Mayo, Mustard, Peppers, etc, etc. My initial thought was to do a Master/Detail -- Master being the item ordered (the sandwich) and a detail table (mods) and then a Xref table which would include the SandwichID and the ModID (which would connect to the ModTable of toppings)However, that xRef table of SandwichID/ModID is quickly going to grow to thousands of records when you consider a typical order of 4 sandwhiches might have as many as 200 mods. I am thinking a conservative estimate might be as many as 10,000 new rows a day. And we do want to have an "order history" so that people can come back and select thier customer sandwhich again and again, so we can't really delete the orders to save/make space. So now, I am thinking that I should serialize the mods into a field. For example, Sandwhich ID and Mods Field such as(132 | 120,130,165,175,190,210,312,412) -- this would keep every Item ordered on its own row and won't fill up as quickly. And then implode that string on the server side to fill out the order and customization form, as well as be abale to calculate the total of the item. I am sure I am not the first person to have such a query, and I thought I could just post it up here to get your thoughts. Has anyone done something similar in the past, is there a new/better method I am not thinking of, any comments helpful and thanks! |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2010-03-01 : 01:22:17
|
| The better way is to normalise your data properly - i.e have the master/detail. Don't worry about "too many rows". That's what databases are designed for.If you're feeling really concerned you can create a heirachy and have pre-built "mods" that you can use in addition to the individual ones. That would allow for pre-selection of menu orders or histories.But whatever you do, do no do what you proposed with the single field. It's the only real wrong solution there is! |
 |
|
|
|
|
|
|
|
Serialized vs Non Serialized -- your thoughts?