Database design

[Anon]

I´m about to start designing a database for a webshop, but I´m not sure if my solution is the best.

The company sells products, of course. Each product has a name and a short description and comes in different sizes and different colors. Each size in a given color has a unique price. Which tables and columns would you set up? Where should the price column be, for example?

[[deleted]]

A very clean method is:
Create a table for the product names,
a table for the colors,
a table for the sizes
and a crossreference table that contains the price and links the products to the colors and the sizes.

So each entry in the products table would have several entries in the xref table, that each link to a color and a size.

If you feel like cheating you could make the xref table contain the color and size data.
Usually a product costs the same in different colors, so you can store the color and an aray of sizes and prices in one xref record.

[Anon]

OK here is what I would do.

I would set up four tables as follows:-

PRODUCTS

PRD_ID - primary key
PRD_NAME
PRD_DESC

COLOURS

COL_ID - primary key
COL_SHADE

SIZES

SIZ_ID - primary key
SIZ_SIZE

PRODUCT_VARIATIONS

PRV_ID - primary key
PRV_PRD_ID - foreign key link to PRODUCTS
PRV_COL_ID - foreign key link to COLOURS
PRV_SIZ_ID - foreign key link to SIZES
PRV_PRICE

The first three tables are simple self explanatory tables, the fourth is a three way intersection table allowing you to dscribe which products are available in which size and colour combinations with a unique price for each of these.

All the tables above have a numeric primary key which I usually make system generated (if the database you are using supports this) and it is these ID values that are linked in the intersection table. The main reason for doing this is that it is very easy to change a product name for instance as it is not repeated in other tables as a foreign key link.

Note that some web databases don't support embedded foreign keys (e.g. MySQL), but you can still use them and maintain the integrity of the database via your application.

Hope this helps.

chrber.