Hello...

Hello guys... I´m designing an ecommerce database...

* A product can have several sizes and several colors
* Each combination of products´s size/color must have a specific price

So, I can have a Product X with sizes: A,B,C and colors: Green,Black and White

And each combination have its price...

It´s implemented in Amazon, one example here : Amazon LINK Sample

What I´ve done so far:

What you guys think? Suggestions?

Thanks

Are all products available in all sizes and all colours?

pootle flump ur codings are working excelent.

Hi...

No... It can be different...
In some cases the product has no color or size at all...

Thanks

Ta.

There are these two rows in the database:
Please could you explain if it is valid for me to enter 1 for SizeProductFk and 1000 for ColorProductFk? If it is not, then what is there to stop me?

pootle flump ur codings are working excelent.

If there is a Product with ID 1000 and 1, yes should be valid...

Just to be sure you understand what I am saying:
It is correct to have a row in ProductPrice that references a row in ProductSize for a product of ID 1 and that also references a row in ProductColor for a product of ID 1000?

In other words, a row in ProductPrice can reference one product for the size and another product for the coloru?

pootle flump ur codings are working excelent.

I understood now...
No, It should never happen ...

"Should", "could" and "oh sh*t, it has" are all very different beasties.
So - any ideas how to stop it happening? I'm talking declaratively, by properly constraining the database, not "ah, the app will check....".

pootle flump ur codings are working excelent.

I got your point, but I found other problens that I must fix before setting constraints...

1 - Analysing my design how can I have one ProductPrice without color AND without size ?
2 - What you think change my design to something like that :

• A Product table that will contain a record for every combination of item, size, and colour, with a unique SKU and Price for each.
• A Size table that normalizes out the common sizes Products may have. Product has a foreign key to this table.
• A Colour table, as above.

Thanks

either create an entry in the size table for "all sizes"
OR
carry a default price at the product level, if no price quoted at the colour/size entries then use the price at product level.

the second approach requires additional programming input and development to make sure you pick the right price. it also means that the users have to be more careful about where and how they manipulate the price element. it would be all to easy adjust the wrong price, not appreciating that there may be multiple prices for that product line

Thanks!
So, You think I should stay with that database design (Original post) ?

No
Im saying you need to analyse your business requirements and develop a strategy that allows you to have products, with different colours, different sizes, which seemingly also have different prices. how you do that is up to you. sadly in the development world theres often many answers and more than one that is right, and more than one that is workable.

the issue is how you design your schema such that it supports the business requirement.

its arguable that the real issue is how you define the product
is the product, say, a specific style / cut / fabric of pair of trousers and then sub classified by colour and size,
or
is the product a pair of chinos in navy blue waist 32, leg 29, with belt, and say wasit 24 is another product.
to me thats the nub of your problem.
persoanlly I'd expedct it to be the latter, I'd be surprised if any retailing outlet saw a product as a pair of Chinos, and then dropped down a level to sub classify them.