title: Pizza & Food - database design
body: <p>I want to create a website that allow customer to order food from the website.</p>
  
  <p>There are two food type:</p>
  
  <ul>
  <li>regular food/drink (eg: burger, donner kebab, chip, coke, pepsi etc)</li>
  <li>Pizza food (eg: Margherita Pizza, meat Pizza, etc)</li>
  </ul>
  
  <p>If they select pizza from the list - they may need to select Base (thin crust, dep crust), Extras, and the pizza size/Option.</p>
  
  <p>How to design the tables in this situation?</p>
  
  <p><strong>Note</strong>: 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)</p>
  
  <h2>Screenshots prototype</h2>
  
  <p>See two screenshots I am trying to implement, I on the right path on the database design or what could have done better?</p>
  
  <h3>Pizza customize:</h3>
  
  <p><img src="https://i.stack.imgur.com/xI23x.jpg" alt="GUI-Mockup to customize pizza"></p>
  
  <h3>Beef Burger customize:</h3>
  
  <p><img src="https://i.stack.imgur.com/18fD6.jpg" alt="GUI-Mockup for the burger options"></p>
  
  <h3>Extras functionality (Dropdown  / tickboxes)</h3>
  
  <p>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. </p>
  
  <p><img src="https://i.stack.imgur.com/UvZ0K.jpg" alt="GUI-Mockup extra dropdowns"></p>
  
  <h3>Database design</h3>
  
  <p>How would you set up your database modell to implement something like the above customization options? 
  Here is what I have came up with:</p>
  
  <p><strong>categories Table:</strong></p>
  
  <pre><code>+----------+--------------+------+-----+---------+----------------+
  | Field    | Type         | Null | Key | Default | Extra          |
  +----------+--------------+------+-----+---------+----------------+
  | cat_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
  | cat_name | varchar(100) | NO   |     | NULL    |                |
  +----------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p><strong>items Table:</strong></p>
  
  <pre><code>+-----------+--------------+------+-----+---------+----------------+
  | 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    |                |
  +-----------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>- item_type (0 = normal, 1 = pizza, 2 = set meal)</p>
  
  <p><strong>item_options Table:</strong></p>
  
  <pre><code>+-------------+--------------+------+-----+---------+----------------+
  | 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    |                |
  +-------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p><strong>item_extras Table:</strong>
  (Do you think should have separate tables for pizza toppin and extras?)</p>
  
  <pre><code>+-----------+--------------+------+-----+---------+----------------+
  | 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    |                |
  +-----------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p><strong>item_pizza_base Table:</strong></p>
  
  <pre><code>+-----------+--------------+------+-----+---------+----------------+
  | 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    |                |
  +-----------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p><strong>SQL Result:</strong></p>
  
  <pre><code>mysql&gt; select * from categories;
  +--------+----------+
  | cat_id | cat_name |
  +--------+----------+
  |      1 | Pizzas   |
  |      2 | Burgers  |
  
  mysql&gt; select * from items;
  +---------+--------+------------------+-----------+
  | item_id | cat_id | item_name        | item_type |
  +---------+--------+------------------+-----------+
  |       1 |      1 | Vegetarian Pizza |         1 |
  |       2 |      2 | Beef Burger      |         0 |
  
  mysql&gt; 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&gt; 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 |
  </code></pre>
  
  <p>As you can see extras from burger and pizza in 1 table.. should it be separated?   </p>
  
  <pre><code>mysql&gt; 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 |
  +---------+-----------+------------+------+
  </code></pre>
  
  <p>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.</p>
  
  <p>Is the database design correct or what could be improved?</p>