Database design question 1

[StevenB]

Looking for suggestions on database design, I'm a bit of a newbie.

I've been asked to put together a database to store apparel product data for a small (approx. 25) number of products. However, they seem to come with a lot of variety.

For example, we clearly need this information:

product id
product name
long description

But then we have variety in price, size, and color. For example, one product line looks like this:

Product Color Size Price
Golf shirt white s $8
" blue s $8
" red s $8
" white xl $10

And so on, with more variation than that. Obviously, this database will require multiple tables, but I haven't a clue how to handle it. Basically, each product can have multiple sizes, multiple prices, and multiple colors.

Any suggestions would be greatly accepted!

Steve Battisti

 

[Tim1]

Hi Steve,

The normal way to go about designing a database is to normalize your data as much as possible, so that you don't have redundant information in many tables, so for your situation, you could start along the lines of:

Table 1 : product_id,
product_name,
long_description

Table 2 : product_id,
colour,
size,
price

You can then join between the two tables using product_id, and if you change a product name / description, you will not have to update massive numbers of rows.

Hope this gives you a start,

Tim

 

[StevenB]

OK, but would this structure work if each product could have multiple combinations of colors, sizes, and prices?

For example, let's say I had product 001, a shirt available in red or white, in sizes from XS-XL, with prices that vary based on size.

With the table structure above, wouldn't I have to enter a ridiculous number of rows in the second table?

For example (I'll use comma-separated):
001,red,XS,$8
001,red,S,$10
001,red,M,$12
001,red,L,$14
001,red,XL,$16
001,white,XS,$8

and so on, so that for that one product, I'd end up with 20 rows of data.

Is that how you would imagine it happening?

Thanks!

 

[Tim1]

Hi Steve,

That is exactly how I would expect it to happen. You have some small tables that contain master data, and then larger and larger tables with other information in.

You could, if you really wanted, have a second table with some information, a third table with a bit more information, but you need to decide which gives you the best compromise between keeping data easily processable and keeping data redundancy to a minimum.

Tim