Database design problem

I'm trying to built a database for ecommerce and I'm stuck with this problem.

I'll use a grocery store for the example.

First here's the tables.

table product_prd
id_prd
name_prd
description_prd

A product may have different "sizes" like a bag of chips. The table product_prd is linked (one-to-many) to product_size_pds

table product_size_pds
id_pds
idprd_pds //Foreign Key - product_prd.id_prd
name_pds //this contains the name of the size (ex. small,medium, large)
price_pds
weight_pds
lenght_pds
height_pds

I thinks there's a problem with my structure if the product as only one size like a camping chair.

I just can't leave the name_pds field empty or repeat the name from name_prd. It seems bad practice.

Is there a structure that could hold single size product and multiple size products?

Hope this makes sense.

Thanks

Quote:

Originally Posted by ZoeNet

I'm trying to built a database for ecommerce and I'm stuck with this problem.

I'll use a grocery store for the example.

First here's the tables.

table product_prd
id_prd
name_prd
description_prd

A product may have different "sizes" like a bag of chips. The table product_prd is linked (one-to-many) to product_size_pds

table product_size_pds
id_pds
idprd_pds //Foreign Key - product_prd.id_prd
name_pds //this contains the name of the size (ex. small,medium, large)
price_pds
weight_pds
lenght_pds
height_pds

I thinks there's a problem with my structure if the product as only one size like a camping chair.

I just can't leave the name_pds field empty or repeat the name from name_prd. It seems bad practice.

Is there a structure that could hold single size product and multiple size products?

Hope this makes sense.

Thanks

What you might need in your design is transform the product_size_pds table into a mapping table and define the product sizes in a separate table named size:

table product
id_prd //Primary Key (Identity)
name_prd
price_prd
description_prd

table map_product2size
id_prd2size //Primary Key (Identity)
id_prd //Foreign Key from product_prd table
id_size //Foreign Key from size table

table size
id_size //Primary Key (Identity)
name_size //this contains the name of the size (ex. small,medium, large)
weight_size
lenght_size
height_size|||

Quote:

Originally Posted by davef

What you might need in your design is transform the product_size_pds table into a mapping table and define the product sizes in a separate table named size:

table product
id_prd //Primary Key (Identity)
name_prd
price_prd
description_prd

table map_product2size
id_prd2size //Primary Key (Identity)
id_prd //Foreign Key from product_prd table
id_size //Foreign Key from size table

table size
id_size //Primary Key (Identity)
name_size //this contains the name of the size (ex. small,medium, large)
weight_size
lenght_size
height_size

If I take a single size product (the camping chair) with your structure, I still need to enter a size to the product witch I don't think is applicable for a single size product. And for the price it should be in the table size because the price varies with the different size of the product.|||

Quote:

Originally Posted by ZoeNet

If I take a single size product (the camping chair) with your structure, I still need to enter a size to the product witch I don't think is applicable for a single size product. And for the price it should be in the table size because the price varies with the different size of the product.

If you define a size, can it be applied to different products? If yes, then you move the price column to the mapping table such that the product price is defined by the combination of product id and size id. And it's fine to have a product with a single size - it just happens to be a one-to-one relationship inside the mapping table.|||

Quote:

Originally Posted by davef

If you define a size, can it be applied to different products? If yes, then you move the price column to the mapping table such that the product price is defined by the combination of product id and size id. And it's fine to have a product with a single size - it just happens to be a one-to-one relationship inside the mapping table.

I'll try that thanks