Database design advice

General question for the database experts on here :nerd_face:

For ecommerce functionality, I need to add products, along with option and extras to an order table in the database. Here’s what I have so far…

products
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI |         | auto_increment |
| name     | varchar(100) | NO   |     | NULL    |                |
| price    | decimal(13,2)| NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
product_size
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI |         | auto_increment |
| name     | varchar(100) | NO   |     | NULL    |                |
| price    | varchar(200) | NO   |     | NULL    |                |
| productID| int(10)      | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
extras
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI |         | auto_increment |
| name     | varchar(100) | NO   |     | NULL    |                |
| price    | varchar(200) | NO   |     | NULL    |                |
| productID| int(10)      | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

Let’s say that the Product is a Pizza. The user would select a size (small, medium, large) which would override the default product price. Then they would choose any extras, (toppings such as pepperoni) which would be added along with the price of each extra topping.

Here’s the question, how would I store the product + size + extras as a line item on an order? An order would contain multiple of these configurations depending on the products chosen, the sizes and any extras.

What would you recommend I do? Add another table between these three and the main ‘order’ details table?

Thanks!

Community Page
Last updated: