Pizza & Food - database design
I want to create a website that allow customer to order food from the website.
There are two food type:
- regular food/drink (eg: burger, donner kebab, chip, coke, pepsi etc)
- Pizza food (eg: Margherita Pizza, meat Pizza, etc)
If they select pizza from the list - they may need to select Base (thin crust, dep crust), Extras, and the pizza size/Option.
How to design the tables in this situation?
Note: Each item have 1 or more options. An option may have extra (1 or more) or without extra. If item is pizza type - then it may have Base (crust)
Screenshots prototype
See two screenshots I am trying to implement, I on the right path on the database design or what could have done better?
Pizza customize:
Beef Burger customize:
Extras functionality (Dropdown / tickboxes)
On the extras, sometime I need to add multiple extras for the dropdown instead tickboxes. That means the customer can only choose 1 from 1, 2 or 3 dropdowns.
Database design
How would you set up your database modell to implement something like the above customization options? Here is what I have came up with:
categories Table:
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| cat_id | int(11) | NO | PRI | NULL | auto_increment |
| cat_name | varchar(100) | NO | | NULL | |
+----------+--------------+------+-----+---------+----------------+
items Table:
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| item_id | int(11) | NO | PRI | NULL | auto_increment |
| cat_id | int(11) | NO | | NULL | |
| item_name | varchar(100) | NO | | NULL | |
| item_type | int(11) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
- item_type (0 = normal, 1 = pizza, 2 = set meal)
item_options Table:
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| option_id | int(11) | NO | PRI | NULL | auto_increment |
| item_id | int(11) | NO | | NULL | |
| option_name | varchar(100) | NO | | NULL | |
| price | decimal(6,2) | NO | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
item_extras Table: (Do you think should have separate tables for pizza toppin and extras?)
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| extra_id | int(11) | NO | PRI | NULL | auto_increment |
| option_id | int(11) | NO | | NULL | |
| name | varchar(50) | NO | | NULL | |
| cost | decimal(6,2) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
item_pizza_base Table:
+-----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+----------------+
| base_id | int(11) | NO | PRI | NULL | auto_increment |
| option_id | int(11) | NO | | NULL | |
| base_name | varchar(50) | NO | | NULL | |
| cost | decimal(6,2) | NO | | NULL | |
+-----------+--------------+------+-----+---------+----------------+
SQL Result:
mysql> select * from categories;
+--------+----------+
| cat_id | cat_name |
+--------+----------+
| 1 | Pizzas |
| 2 | Burgers |
mysql> select * from items;
+---------+--------+------------------+-----------+
| item_id | cat_id | item_name | item_type |
+---------+--------+------------------+-----------+
| 1 | 1 | Vegetarian Pizza | 1 |
| 2 | 2 | Beef Burger | 0 |
mysql> select * from item_options;
+-----------+---------+-------------+-------+
| option_id | item_id | option_name | price |
+-----------+---------+-------------+-------+
| 1 | 1 | 12 Inches | 5.60 |
| 2 | 1 | 14 Inches | 7.20 |
| 3 | 2 | 1/4lb | 1.80 |
| 4 | 2 | 1/2lb | 2.50 |
mysql> select * from item_extras;
+----------+-----------+-----------+------+
| extra_id | option_id | name | cost |
+----------+-----------+-----------+------+
| 1 | 1 | Mushroom | 1.00 |
| 2 | 1 | Pepperoni | 1.00 |
| 3 | 2 | Mushroom | 1.00 |
| 4 | 2 | Pepperoni | 1.00 |
| 5 | 3 | Chips | 0.50 |
| 6 | 4 | Chips | 0.50 |
As you can see extras from burger and pizza in 1 table.. should it be separated?
mysql> select * from item_pizza_base;
+---------+-----------+------------+------+
| base_id | option_id | base_name | cost |
+---------+-----------+------------+------+
| 1 | 1 | Thin Crust | 0.00 |
| 2 | 1 | Deep Crust | 0.00 |
| 3 | 2 | Thin Crust | 0.00 |
| 4 | 2 | Deep Crust | 0.00 |
+---------+-----------+------------+------+
keep in mind, price extras for each item is not always the same. For example: Pizza size 10" will cost 1.00 for each extra but 0.50 for 12" pizzas. Also there will be a case for each pizza will have different cost of extras.
Is the database design correct or what could be improved?
Answer
Im stuck with Extras functionality - how to design table and fields for the dropdown extras? See my question "Extras functionality (Dropdown / tickboxes)"
If you need to make a dropdown, put the values for the dropdown in a table.
It's OK to put extra values in there, as long as you can easily separate out the values you need in the dropdown.
Example
table option_labels
-------------------
id integer auto_increment PK
name varchar(40)
table toppings
--------------
id integer auto_increment PK
option_label_id integer foreign key references option_labels(id)
item_id integer foreign key references items(item_id)
price decimal(10,2)
As soon as you know the item, you can populate the dropdown using:
SELECT ol.name, t.price FROM toppings t
INNER JOIN option_labels ol ON (t.option_label_id = ol.id)
WHERE t.item_id = '$item_id'
Normalize those tables
This table has a major flaw:
mysql> select * from item_extras;
+----------+-----------+-----------+------+
| extra_id | option_id | name | cost |
+----------+-----------+-----------+------+
| 1 | 1 | Mushroom | 1.00 |
| 2 | 1 | Pepperoni | 1.00 |
| 3 | 2 | Mushroom | 1.00 |
| 4 | 2 | Pepperoni | 1.00 |
| 5 | 3 | Chips | 0.50 |
| 6 | 4 | Chips | 0.50 |
+----------+-----------+-----------+------+
It's not normalized, put the names in a separate labels table, just like in the example above. If a row (excluding the id) is not unique then your data is not normalized and you're doing it wrong.
Because you are using a lot of joins, it's IMHO best to use InnoDB, it has some cool features to speed up joins that use PK's a lot.
No matter what people say
Don't denormalize until slowness starts.
Use indexes
Put an index on all fields named *_id.
Also put an index on alls fields that are used in where clauses often.
Do not put indexes on fields with low cardinality, so no index on a boolean or enum field!
Indexes slow down inserts and speed up selects.