I'm developing shopping online system.

This is the way the merchandise structure work:

You will find quantity of groups..

Each Category has quantity of products.

The Items have a number of Options

A Choice might have extra(s) or without extra(s)

The next tables I've:

mysql> 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    |                |
+------------+--------------+------+-----+---------+----------------+

Eg: 12, 2, "Google"

Products Table:

mysql> 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    |                |
+-------------+--------------+------+-----+---------+----------------+

Eg: 2, 12, "Item 1", "Desc... 1"

Eg: 3, 12, "Item 2", "Desc... 2"

Options Table

mysql> 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    |                |
+-----------+--------------+------+-----+---------+----------------+

Eg: 45, 2, "Regular", "2.20"

Eg: 46, 3, "Small", "1.20"

Eg: 47, 3, "Large", "2.20"

Extra supplies Table:

mysql> 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    |                |
+-----------+--------------+------+-----+---------+----------------+

Eg: 64, 47, "With Bag", .10"

Is good database design? What is enhanced?

I didn't produce a relationship table, is necessary? If that's the case, i am not sure how do you produce a relationship table.

Right now I personally use multiple Choose queries to obtain the relationship between individuals tables, such as this below:

<?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'];
        }
}
?>

When I wish to remove a product and including options, I personally use similar php code like above.

Edit: Didn't remember to include Options table

Edit: Up-to-date some data example.

Take into account that a product may fit in with multiple Groups.

enter image description here

Listed here are points to consider:

  • When the cost of the item changes using the category, then your ItemPrice column adopts the CategoryItems table. Otherwise, it is going in to the Products table.
  • When the cost of the option changes using the item, then your OptionPrice column adopts the ItemOptions table. Otherwise, it is going in to the Option table.