newb question regarding tons of columns

[Malkavbug]

Hi there,
I am new to having to deal with any large databasing problems but here it is.

I am trying to make an inventory table for clothing (this will eventually be used for several programs) where each different colored item will be tracked by a different UPC (each color item will be a unique row or entry), but the sizes will be tracked via columns .

So here is the issue. I am currently writing it like this
prod_UPC|prod_size_s|prod_size_m|prod_size_l|prod_size_xl|prod_size_xxl|prod_size_1|prod_size_2|insert about 300 other sizes here|prod_total

on and on, but this will end up leaving me with a table with over 400 columns using MySQL, is this a bad thing? will it grind anything to a halt? is there a point where you just can't slap on another column?

Not sure how big these can get and still hold their speed, stability, and integrity.

should I just make a table for
ProdUPC | ProdSize | InStock

and a seperate table to store all the product size possibilities for individual product categories?

Any advice is appreciated.

~Malkavbug

[drawmack]

Products
    UPC - primary key
    Color
    Description

Size
    size_id - primary key (autonumber)
    size_name

Prod_2_Size
    p2s_is - primary key (autonumber)
    UPC
    size_id
    InStock - number in stock

[dnast]

I haven't read about the performance of a table like that, but I can tell it would be very difficult to worth with. Multiple tables is the way to go.

You should read up on database normalization. I think this would make it much simpler, as well as more efficient.

[Malkavbug]

yeah I think making the sizes a table of thier own then making a product to size table would probally be better. I need to get out of the spreadsheet mindset.

For my purposes I will probally add

Products
total_in_stock

as well which will update when something is added or subtracted.

The end idea from the web shopping cart standpoint is to not have out of stock items show on the webpage(think having the total_in_stock so I can have it skip it if total_in_stock <= 0, and to have the available sizes dynamically generated based off the sizes in stock.

Has anyone worked on something like this before or could possibly point me to a place that gives advice based off of DBs for clothing retailers.

<rant> selling "stuff" is easy to figure out, having "stuff" with size options is abit more difficult</ramt>