Simple takeaway database

[Dave_20]

Hey guys.

So this should be a pretty simple question (but doesn't appear to be) that I'm hoping some of the database wizards out there can help me with. I'm not too long outta university with a BSC in Computing, I didn't get a full classification due to the fact that yup I failed the Data Management module. I was never very good with normalization and splitting a database down into the correct amount of tables and relationships. Anyway I've been asked to produce a simple web site (first paid project) for a fish and chips take away down the road. The guy is only paying me £100 but I still wanna do a good job.

I'm using Laravel PHP framework and PHP is my preferred language. I know how to do all the creation of a user login backend system, however creation of the database is really getting to me. The menu is as follows and I've managed thus far to break it down:-

11 Categories:

Main - Only has 2 items on it, which I'd usually put into a sides menu if it were me but this is how he wants it done so it appears right (chips, cheesey chips)
Fish
Chicken
Burgers
Pies & Pasties
Pizza (only available Friday and Saturday)
Veggie
Meal Deals
Sides
Sauces
Drinks

2 Sizes:

Large
Medium

and obviously 1 price for each size for example:

Large - £4.90
Medium - £3.90

1 Category has Many Menu Items
1 Menu Item has Many Sizes (Large, Medium)
1 Menu Item has Many Prices (Large Price, Medium Price)

Have I split that down right? Obviously I'm not asking for someone to do the work for me, I just need a little direction or correction on what I've produced thus far before I go to building up the table migration files for Laravel.

Any help would be much appreciated. Yes I know as a graduate I should probably already know this stuff but hey we can't always be good at every aspect.

Thanks

Dave

[Dave_20]

Or am I over complicating this? I'm seeing 4 tables right now - Categories, MenuItems, Size, Price. Or should there be only 2 tables - Categories, MenuItems - with the menu items table containing both the size and price for each item.

[john_111]

Yes, you are overcomplicating things.

Go for one table {id, item, size, cost} or table {id, item, size, cost, description}
Things available on certain days you handle with the php date() and an if which replies not available when its not available and doesn't add it to the list., or add it to the list of chosen food items available today.

With a database, the easy way is to work with entities, which almost automatically puts it in the correct normal form. You only entity is a menu item, with its attributes.

Now you might tell me there are several types of fish or pie etc...

{1, cod, small, 4.50, maybe a description}
{2, cod, large, 5.50, maybe a description}
{3, haddock, small, 4.00....}
{4, chicken pie, small, 3.99,..}
{5, chicken pie, large, 4.99...}
{6, pork pie, small, 2.25...}
{7, pizza-name, small, 5.50, needs the description}
{8, other-pizza-name, large, 7.50, needs its description}
{9, curry sauce, small, 1,50...}
{10, apple sauce, large, 2.50, finest bramble apples from dorset}

Your way would lead to lots of tables, each with an id and a name, and lots with id, name, otherid (linking to other table) and would require lots of joins.

[john_111]

Second thought
As I am not well (got a throat bug and headache) and replied at midnight, I realised one other table was required when I got into bed, your category table.
It will be a single column table with just the various cateogries in it.
Like this category {category}
What!! you say, shouldn't it have an id to identify the category to use? NO.

{fish}
{pie}
(sauce}
{pizza}

The word fish uniquly identifies the category, as does pie, etc, it is a natural primary key. If you had category table like this

{1,fish}
{3, sauce}
{2, pie}
{4, pizza}

Then you'd require a join to get at the actual category. This is really just a look-up table, whose main purpose if to populate a drop-down list of categories in the data entry form in the admin section. This prevents typos like fish and fihs, or pie and pei, which would result in things being missed out when you did a query on fish or pie.

It's a common beginner's mistake when you have a table as simple as this. An id is only required when there are several attributes for the entity (like the cod or chicken pie examples above, where you'd need a compound primary key to identify it uniquely, or an id.) And if you are struggling with databases in general, then you'd definately struggle with joins in sql.

So your foodItem table needs one more column
table {id, item, size, cost, description, category}
which enables you to pull out and group by the food category, like a normal shop menu.

{2, cod, large, 5.50, maybe a description, fish}
{3, haddock, small, 4.00.... fish}
{4, chicken pie, small, 3.99,.., pie}
{5, chicken pie, large, 4.99..., pie}
{6, pork pie, small, 2.25..., pie}

I am assuming that you are not designing something to take online orders, but to display a menu online that the shop owner can easily update.

Your error in database design is simple realy, you are giving each attribute of a food item it's own table. Entities have either one attribute (rare, but normal) or a set of attributes (very common).

[Tarek_2]

Both answers (one table, many tables) are correct but you must choose the right one. So, you must answer this question :

"Does Category have any useful additional information ?"

If Yes then you must create it as a table.
If No then you must keep it simple and use one table.

A table that contains one column (the auto increment id is not a usefull additional information) can be ignored.

[overwraith]

One of the things I had to learn is first, there is always a database. Second, always go back to the database in order to get current data, don't rely on cached values, each page always goes to the database for all persistance. Furthermore your database entries should reflect your programatical 'classes'. Once you realise that you realize that you need some form of object relational mapping. I also know that your lists can be mapped from the database using one to many tables or many to many relationships. The many to many relationships always require a bridge entity. Once you realize that the bridge is simply a roster for associating objects from A with objects from B in a list like fashion you realize that the cardinalities are fairly easy to set up. A few more things that are helpful are the creation of some good repository classes which essentially compartmentalize database queries via the objects which are most germaine to their operation, the objects the query returns.