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

Database deployment - SQL Express

I have an application being developed in VS 2005 using SQL Express. I've built up some new roles and have several users pre-populated that I'd like to deploy to a shared hosting evironment.

The problem is that I seem to keep getting:

Database 'd:\hosting\member\asanga\App_Data\ASPNETDB.mdf' already exists. Choose a different database name.
Cannot attach the file 'd:\hosting\member\r2agility\site1\App_Data\ASPNETDB.MDF' as database 'ASPNETDB'.

I saw another post with a similar issue, but that resolution has not seemed to help. Can anybody give me insights as to where the "asanga" is coming from? That might help me diagnose the issue.

Thanks in advance

Problem solved. Just had to rename the "Initial Catalog" value from ASPNETDB to ASPNETDB1