Database Design for a takeaway shop

[lawphotog]

hey guys,

first time on this forum so sorry if i make mistakes ..

My problem is .. I have done a final year project for a local pizza shop. It has some basic functions such as customer registration, shopping catalogue and ordering online. The website is linked to a MySQL database and it has done with PHP. The problem with the client is that he wants to update the prices himself via a content management system. So i have done it as well. My project was done and my tutor was happy. In my database design, apart from other tables such as customers and staffs, it has two tables that made up the Menu Catalogue. It's Menu table and Menu Catagory table which has 1 to many relationship. In Menu Catagory table it has 2 fields which are Category and Description. In Menu table it has 4 fields which are Name, Category, Description and Price. I used few sample menus for the project so it was fine but when i actually try to do it for the client i know my design is totally wrong. The shop sells pizza, kebabs, burgers, etc has arond 15 categories. For pizzas, they have 19 pizzas and each pizza has 4 different sizes and different prices. Depending on this design, i have to put 4 time in the table for each pizza. So finally i have 19 x 4 = 76 lines on the menu page. It is too messy to look and the page needs to be too long.

So, please suggest me of a new design for this. Do i need 15 seperate tables for 15 categories ? or how can i solve this problem. thanks so much,Laurence.

[urtrivedi]

Do not create tables for categories. 2 is enough

Do you have actually 76 kind of pizzas with distinct type and price? if yes then i think you can not compact it anymore.

One complex design I can suggest, it is self referencing (though it may not solve your problem) this design if for to many sub categories.

it has only 2 table say menu_master (both category and item in same table AND TREE LEVEL IN SECOND TABLE) .

(menu_id, menu_desc, paraent_id, is_group, price)
0 , MAIN MENU, -1, Y, 0.00
1, PIZZA, 0 , Y, 0.00
2, PIZZA1, Y, 0.00
3, PIZZA1-1, 2, N,50.00
4, PIZZA1-2, 2, N,75.00
5, kebabs, 0,Y, 0.00
6, KEBAB1,5,N,20.00
7, KEBAB2,5,N,30.00
8, burgers, 0, Y, 0.00 
9, burger1, 8, N, 15.00

Here you can see that pizza has one more subgroup
Also groups is having is_group flag =y and price =0
actual item is having is_group flag =n and not zero price

For better reporing you may keep one more table to track levels

item_id, parent_id, level
1, 0, 1
2, 1, 1
2, 0, 2
3, 2, 1
3, 1, 2
3, 0, 3
4, 2, 1
4, 1, 2
4, 0, 3
5, 0, 1
6, 5, 1
6, 0, 2
7, 5, 1
7, 0, 2
8, 0, 1
9, 8, 1
9, 0, 2

When you create make sure that item is not created under item, only groups could be treated as parent

I have succesfully get rid of sub-sub-sub categories with above design

[lawphotog]

Thanks urtrivedi,

Your design is really good to have sub category in one table. I will learn that and use that in the future. thanks.

If there is not many things wrong with the design of my database than it's my web page design that gives me problem. The below is the testing link of the site i mention. It is just very simple website.

http://www.azure-photography.net/riopizzaupdate

Please choose Pizza from the select box and u will see 24 lines of items but it is basically just 6 types of pizza. I just select from the database eg. SELECT menu FROM menu_table WHERE category = $category and loop it and show it in a table. If i continue adding more pizza finally i will endup having 76 rows on the table that i need to change the CSS and the page will be too long.

So, is there any way to show nicely in a table with my current datebase design .. ideally, the size of the pizzas (such as 9" or 10") should show as a column so one type of pizza has just one row in the table. 19 types of pizza will have only 19 rows on the table. when i say Table it's the table on the website, not the table in database. Thankssssssssssssssssssss. Laurence.

[urtrivedi]

on second page i could give you following query for

<?php
if ($category=='id of pizza')

   $query=" select menu_title, max(if(description like '%9"%',price,null)) Nine_Inch
    , max(if(description like '%10"%',price,null)) ten_Inch
    , max(if(description like '%12"%',price,null)) twelve_Inch
    , max(if(description like '%14"%',price,null)) fourteen_Inch
    from tablename
    where category='$category'
    group by menu_title a"

else //normal query
    $query="     select menu_title, description ,price
    from tablename
    where category='$category'"
?>

I would not recommend such hard coding, this is just for your learning how to convert rows to columns
first try in phpmyadmin then integrate in php page