table structure help for item table

[zaira]

hi, i need some help for my thesis project in school. I need to know how would my item table looks like. It is so complicated because there are items which has different attributes like color, lens type, sizes, frames which has different prices. Or, it could be one item it has different attribute but same prices. Or, an item doesnt have an additional attribute at all.
See items example below for more detail

Items Example:

NO ItemNO ItemName Price
1 I01230B Sunglasses case $5.00
2 I01230P Sunglasses case $3.00
3 I01230BR Sunglasses case $7.00
4 I01230BL Sunglasses case $4.00
5 SBL012 Black Lens Sunglass $10.00
6 SGP0DR Driving Sunglasses $8.00
7 SGP0ND Driving Sunglasses $8.00
8 SGP0AF Driving Sunglasses $8.00

No. 1,2,3,4 is same item with different colors and different price. No. 5 is an example of item which doesn't have any other attribute at all. Then, No. 6,7,8 is an item with has different lens type but same prices.

I can give a perfect link for this on how this look like in the client side.

So if the item has attributes an option box will show

Example of item which has an attribute but has the same price

http://example.com/store.cfm?event=itemdetail&itemid=443363&returnto=http%3A%2F%2Fexample.com%2Fstore.cfm%3Fevent%3Dshowcatalog%26catid%3D37638

Example of item which has an attribute but different price

http://example.com/store.cfm?event=itemdetail&itemid=835563&returnto=http%3A%2F%2Fexample.com%2Fstore.cfm%3Fevent%3Dshowcatalog%26catid%3D9213

Example of item which doesnt have an attribute at all

http://example.com/store.cfm?event=itemdetail&itemid=605056&returnto=http%3A%2F%2Fexample.com%2Fstore.cfm%3Fevent%3Dshowcatalog%26catid%3D9217

Then my table structure too needs to support CSV upload. Thanks in advance.

[edited by: encyclo at 7:58 pm (utc) on June 24, 2009]
[edit reason] please use example.com [/edit]

[LifeinAsia]

Welcome to WebmasterWorld! [webmasterworld.com]

As it's for a school project, don't expect the answer to be given to you on a silver platter.

However, we can point you in the right direction. Instead of trying to setup one table, think "relational database" and setup multiple related tables. For example, you can have an Items table and an Attributes table.

Uploading from CSV will require some additional programming logic.

[rocknbil]

Welcome aboard zaira!

This post [webmasterworld.com] may help you understand the importance of relational tables in database record "options" (as, what you describe are options.)

A search for database normalization will lead you down a similar path.

Second, a quick look through at the TOS [webmasterworld.com], particularly #13, will explain the post edit soon to come.