Although its a 3 years old thread, still I'm replying it thinking that it might be helpful to somebody.
Table Structure
Table_Offer
--------
ID FK
Name
start time
end time
MandatoryGroup
--------
ProductId (FK to product)
MixGroup1
--------
ProductId (FK to product)
MixGroup2
--------
ProductId (FK to product)
Table_offerDetails
------------------
ID PK
offerId (FK to table_offer)
MandatoryProduct(FK to MandatoryProducts.productID
firstProduct ( fk to MixGroup1.productId)
secondProduct (FK to MixGroup2.productId)
Offer
----
ID name startDate EndDate
--- ---- -------- ------
1 COMBO
MandatoryGroup
-------------
ID name
--- ----
1 A
2 B
MixGroup1
---------
ID name
--- ----
3 P
4 Q
MixGroup2
---------
ID name
--- ----
5 x
6 Y
7 Z
OfferHeader
-------------
ID Customer count(B) B_disc time_disc stat_disc DiscTotal orderPayableTotal
-- --------- ------- ---- -------- --------- -------- ------------
1 BOB 2 20 5 2
OfferDetails
------------
ID offerID MandatoryProduct 1stProduct 2ndProduct
---- ------- --------------- ---------- ----------
1 1 A P X
2 1 A P Y
3 1 A P Z
4 1 A Q X
5 1 A Q Y
6 1 A Q Z
7 1 B P X
8 1 B P Y
9 1 B P Z
10 1 B Q X
11 1 B Q Y
12 1 B Q Z
To construct an offerDetails , you must provide a mandatory , a mixgropu1 and amixGropu2. So caseB , has only 2 offers as follows :
1 A and 2 B, 2 Q, 1 S, 2 X and 1 Z
2B 2Q and 2X =2 offers
1A 1Q and 1X=1st offer
1B 1Q and 1X=2nd Offer ... etc other combinations are vivid.
Other offers are based on business logic:
For quantity offer discount:
create query to find the count of B in an offer for a customer . lets call it QTYB. find QTYB %2 and multiply it with quantityOffer value ( which is $20)
For statistics offer, Just add another bit called earnedPoint for a customer's purchase. and set the earnedPoint an expiry date. earned point can be the same as purchased amount. Check if the purchased amount >=$X , them issue discount as per policy.
Similar is the comparison of order time if its before 5PM and after 10AM then issue 5% else issue 10% discount.
There's a joke I heard awhile back:
Q How does a BASIC coder count to 10?
A 1,2,3,4,5,6,7,8,9,10
Q How does a C coder count to 10?
A 0,1,2,3,4,5,6,7,8,9
Q How does a DBA count to 10?
A 0,1,many
The truth behind this joke is that once you have two (or more) of the same thing in a database structure (columns or tables), you're doing it wrong.
A schema that looks like:
+----------+
| id |
| name |
| phone1 |
| phone2 |
| |
+----------+
Is wrong because where will you put a third phone number if someone has it?
The same applies to tables themselves. Its also a Bad Thing to be modifying the schema at runtime, which the "new table for each list" seems to imply. (Related: MVC4 : How to create model at run time?)
And thus, the solution is to create a todo list that is comprised of two tables. There are two things you have - lists and items.
So, lets make a table structure that reflects this:
+----------+ +-------------+
| List | | Task |
+----------+ +-------------+
| id (pk) <---+ | id (pk) |
| name | +---+ listid (fk) |
| | | desc |
| | | |
+----------+ +-------------+
The list has an id (the primary key for the list), and a name. The task has an id (the primary key) a listid (a foreign key) and the description of the task. The foreign key relates back to the primary key of another table.
I will point out that this doesn't begin to encompass all the possibilities in various requirements for the software and the table structure to support it. Completed, due date, repeating, etc... these are all additional structures that will likely need to be considered when designing the table. That said, if the table structure isn't one that is appropriately normalized (or realizing the tradeoffs that you've made because it's not normalized), you will have many headaches later.
Now, all that relates to writing this as a relational database. But thats not the only type of database out there. If you consider a list to be a document the document styled nosql databases may also offer an approach that isn't wrong.
While I'm not going to delve into it too far, there are numerous tutorials out there for todo lists in couch. One such that came up with a search is A simple Task-list application in CouchDB. Another shows up in the couchdb wiki: Proposed Schema For To-Do Lists.
In the approach appropriate for a couch, each list is a JSON document stored in the database. You would just put the list in a JSON object, and put it in the database. And then you read from the database.
The JSON could look like:
[
{"task":"get milk","who":"Scott","dueDate":"2013-05-19","done":false},
{"task":"get broccoli","who":"Elisabeth","dueDate":"2013-05-21","done":false},
{"task":"get garlic","who":"Trish","dueDate":"2013-05-30","done":false},
{"task":"get eggs","who":"Josh","dueDate":"2013-05-15","done":true}
]
(from creating a shopping list with a json file on Stack Overflow).
Or something approaching that. There is some other record keeping that couch has as part of the document.
The thing is, its not the wrong way to approach and a todo list in a document database may be perfectly suited to what you are trying to do with less concept overhead for how to do it.
Best Answer
Having the same Chef for
MenuItems and relatedBookingMenuItems might be a good default setting, but nothing which should be enforced through the database. In reality, it could be perfectly possible that Chef A has defined a menu with it's items, but then he/she is not available and a different Chef B has to take over for the cooking. So in short: let the application manage this, expect exceptions.For your second issue, a technical solution could be implemented through the application, or through triggers at the the database level. It is not always possible or sensible to remove every kind of cycle in a relational DB scheme.
However, I would recommend to reconsider the design first:
expect a customer to have "extra wishes" for items which are not offered by default on the menu - so it should be possible to book extra items (and those should have a price as well), or existing items with some special requirements for their preparation, and maybe a price modification ("I want to have my salad without tomatoes, but with an extra portion of cottage cheese" - Ok, that's fine, we can prepare this for you for only 3 bucks 49 cents).
expect that not all the menu items offered at the time of the booking might be available at the time when the Chef wants to cook it, and they have to find a replacement (maybe with some reduced pricing)
bookings have a date, and menu base prices as well as planned menu items for a certain menu can change over time, but the price at the time when the booking was placed must be usually fixed for the customer. So when a booking is placed, I would suggest to make a copy of the menu with its current price (maybe into a
BookedMenutable), copy the menu items belong to this booked menu and allow all kind of individual changes to be applied to this copy.So better model these things separately:
the available items (independently from the menu, the chef and the current pricing)
the offered menus and items, with the offered prices
the booked menus / items with the price at the time of the booking, and extra customer wishes
the items at the time when the meal is preparated and served, maybe with the changes due to supply shortages etc
After you got this model complete, you should recheck which redundancies/potential inconsistencies exist. Currently, the second issue seems to exist because the model does not clearly differentiate between the different states in time, the menu as a "template" for a booking, the menu how it was booked and the menu how it was cooked and served.