Confusing

[Bert666]

Hi there people,

i am having trouble with the design of my products table - my actual situation might be confusing - so i will use the following analogy of a Resturant to describe my problem:

Ok here goes:

A Fast Foo resturant Sells individual products - like Fries, Burgers, drinks etc. but in order to boost sales also has special packages like Large Combo (which includes a large burger, 2 Fries, A large drink and 3 dounuts) - We'll call these special packages "Combos".

Ok - a customer comes in places an order (it can consist of inividual items as well as combos)- the order is transfered to the kitchen - the order is prepared and handed to the customer. Simple? now the problem i am facing:

Idividual items and Combos are both products
Each combo can consist of many individual items as well as smaller combos (eg. dounut combo)
I need for the order table to know what combos are ordered, but the kitchen to know just how many items have to be prepared (i mean the cooks would only want to be told how many burgers, dounuts etc. not the combos)
At the end of the day i want to know how many combos were sold as well as in terms of items.
Then there's the inventory - ----- urrg i will go crazy..

Any suggestions.

Bert.

 

[Bert666]

Hi there Pat,

Thanks for your reply and the links - i have finally sorted out y problem thanks to the links provided. What i have one is created two tables one products and one sub products- then when i want to know how many sub products will be required to complete a product - i just build a query and repeat the joins again and again - at the moment i have created queries that go 4 levels deep (which is because that is the deepest level the products will go - for the database i am designing). but what if i don't know the number of levels - or if the number of branches can be infinite.

Waiting for a response.

Thanks again.

Bert.