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.
Having the same Chef for MenuItem
s and related BookingMenuItem
s 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 BookedMenu
table), 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.
Best Answer
I look at it this way; your root item is your "Menu Item", and everything else is basically a "variation" of the menu item; from sizes, to add-ons, to options.
I think there's scope to simplify this schema by doing the following:
For example:
Item Variation Groups Table:
These would represent logical groupings of variations; sizes, noodle optons, addons etc. - basically anything you can think of.
If you think about it from the UI point of view, it represents each dropdown, or list of items, you may use to customise your menu item.
Using something like
min_selection
andmax_selection
, you can make a selection in the group optional (0 min), mandatory (1 min), or upto X selectable options.id
(PK)account_id
name
description
min_selection
max_selection
Variation Group Items Table:
Here, you define the specific items that belong to your groups.
id
(PK)variation_group
name
description
price_adjustment
Menu Item Variation Groups Table:
And here, you assign groups to each menu item. The benefit of this approach is, if you have 10 soups on your menu, you can re-assign the same groups - no need to duplicate data.
If you need a variation for a particular item, you can introduce a new group, with some new items.
id
(PK)menu_item_id
variation_group_id
Benefits
This is, of course, just a suggestion - it may even jog your brain and help you think of an alternative.
Good luck!