E-Comeerce Database ERD Help

Question

This is frying my brain, I really need help! Here is the thing i want to achieve.

I have a Table name Product. The product may or may not have up to two Optional field. Example Color and Size.

If the product does not have the optional field, it will have only one row of Price and Quantity, else for each row of optional field, there will be one price and Quantity.

I know this sound Confusing, pardon me. I'm confused too. ): But i can give you guys fews example below.

So the one million dollar question is, what are the tables and it's field i should create?

---

[ Product Without Optional Field ]

Price | Quantity

$1.00 | 2

---

[ Product With One Optional Field ]

Price | Quantity | Size

$1.00 | 2 | Large

$2.00 | 1 | Small

---

[ Product With Two Optional Field ]

Price | Quantity | Size | Color

$1.00 | 2 | Large | Green

$2.00 | 1 | Small | Blue

---

I come up with an idea of having Two entity named Product and Optional to have many to many relationship with the Optional Entity to store the field name, example Size and the junction-entity name Product_Optional will store the value, example Large.

However I'm still stuck with the issue of how to bind the Two Optional Field of one product to the same price and quantity! SORRY to be confusing :(

Answer 1

I have run into the same problem, and I think you need more than one table as well. try this:

products

id
product_name
product_price
.....

Product_options

id
product_option_name

product_options_values

link your product_options_values to your product_options using product_options_id as foriegn key. example: product_option 'size' has the product_option_values of 'small', 'medium', 'large'*

id
product_options_id (fk)
options_value
options_value_price

product_options_to_products

this table links your products to your options. here you'd assign different options to different products.

id
product_id
product_options_id

Answer 2

Edit:

Since your options are unknown, you could do something like this

Products

id
product_name
product_description
...

ProductOptions

id
option (size, color, whatever)
value (large, blue, anything)

ProductInventory

id
product_id
product_option_id
quantity
price

Then your records in ProductInventory would look like:

1 | 1 | 1 | 5 | 2.00
1 | 1 | 2 | 3 | 3.00

etc etc

More detailed example, using the table structure above:

Products

1 | Product 1 | Prod 1 Description
2 | Product 2 | Prod 2 Description
3 | Product 3 | Prod 3 Description

ProductOptions

1 | Size | Small
2 | Size | Medium
3 | Size | Large
4 | Color | Blue
5 | Color | Red
6 | Color | Green
7 | Width | 10 Inches
8 | ... (as many as you want)

ProductInventory

1 | 1 | 1 | 5 | 2.00
(says for product 1, size small, there are 5 quantity, and cost is 2.00

2 | 1 | 2 | 17 | 3.00
(says for product 1, size medium, there are 17 quantity, and cost is 3.00

etc