Pizza & Food - database design

Question

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 1

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.

Answer 2

You have an order table. Then an order item or order line table. With items that can have ingredients on it, you end up with a table that holds the ingredients. With pizza, in particular, you have to worry about halfs, so people can order different things on each half. So far it looks like you are heading in the right direction.

The way I go through design is I figure out the "objects" first. There are tangible objects, like a burger or a pizza, and there are intangible objects, like an order, an order line.

Pizza Hut has one of the best user interfaces for setting up pizza on the web. I would look at it, as it will give you some ideas on how to store the data.