title: Shopping Cart tables - Feedback Database Design
body: <p>I am developing online shopping system.</p>
  
  <p>This is how the product structure work:</p>
  
  <p>There are number of categories..</p>
  
  <p>Each Category has number of items.</p>
  
  <p>Each Item have one or more Options</p>
  
  <p>An Option can have extra(s) or without extra(s)</p>
  
  <p>The following tables I have:</p>
  
  <pre><code>mysql&gt; desc categories;
  +------------+--------------+------+-----+---------+----------------+
  | Field      | Type         | Null | Key | Default | Extra          |
  +------------+--------------+------+-----+---------+----------------+
  | cat_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
  | company_id | int(11)      | NO   |     | NULL    |                |
  | name       | varchar(100) | NO   |     | NULL    |                |
  +------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Eg: 12, 2, "Google"</p>
  
  <p>Items Table:</p>
  
  <pre><code>mysql&gt; desc items;
  +-------------+--------------+------+-----+---------+----------------+
  | Field       | Type         | Null | Key | Default | Extra          |
  +-------------+--------------+------+-----+---------+----------------+
  | item_id     | int(11)      | NO   | PRI | NULL    | auto_increment |
  | cat_id      | int(11)      | NO   |     | NULL    |                |
  | name        | varchar(150) | NO   |     | NULL    |                |
  | description | varchar(150) | NO   |     | NULL    |                |
  +-------------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Eg: 2, 12, "Item 1", "Desc... 1"</p>
  
  <p>Eg: 3, 12, "Item 2", "Desc... 2"</p>
  
  <p>Options Table</p>
  
  <pre><code>mysql&gt; desc items_options;
  +-----------+--------------+------+-----+---------+----------------+
  | Field     | Type         | Null | Key | Default | Extra          |
  +-----------+--------------+------+-----+---------+----------------+
  | option_id | int(11)      | NO   | PRI | NULL    | auto_increment |
  | item_id   | int(11)      | NO   |     | NULL    |                |
  | name      | varchar(150) | NO   |     | NULL    |                |
  | price     | decimal(6,2) | NO   |     | NULL    |                |
  +-----------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Eg: 45, 2, "Regular", "2.20"</p>
  
  <p>Eg: 46, 3, "Small", "1.20"</p>
  
  <p>Eg: 47, 3, "Large", "2.20"</p>
  
  <p>Extras Table:</p>
  
  <pre><code>mysql&gt; desc items_options_extras;
  +-----------+--------------+------+-----+---------+----------------+
  | Field     | Type         | Null | Key | Default | Extra          |
  +-----------+--------------+------+-----+---------+----------------+
  | extra_id  | int(11)      | NO   | PRI | NULL    | auto_increment |
  | option_id | int(11)      | NO   |     | NULL    |                |
  | name      | varchar(150) | NO   |     | NULL    |                |
  | price     | decimal(6,2) | NO   |     | NULL    |                |
  +-----------+--------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Eg: 64, 47, "With Bag", 0.10"</p>
  
  <p>Is this good database design? What could be improved?</p>
  
  <p>I did not create a relationship table, is this necessary? If so, im not sure how do I create a relationship table. </p>
  
  <p>At the moment I use multiple SELECT queries to get the relationship between those tables, like this below:</p>
  
  <pre><code>&lt;?php
  $q =  mysql_query("SELECT item_id, name FROM items where cat_id = '3'");
  while($row = mysql_fetch_assoc($q)) {
       echo $row['name'];
       $q2 = mysql_query("SELECT price FROM items_options_extras where item_id =" . $row['item_id']);
          while($row2 = mysql_fetch_assoc($q2))
              echo $row2['price'];
          }
  }
  ?&gt;
  </code></pre>
  
  <p>When I want to delete an Item and including options, I use similar php code like above. </p>
  
  <p>Edit: Forgot to add Options table</p>
  
  <p>Edit: Updated some data example. </p>