Db design for 'sizes' options for products

[waltonics]

Hi, its been too long since Ive messed with php/mysql on any serious level...

Im doing a site with products that need sizes, and I cant seem to find a good way to design sizes into the database... (see last par of this message for the simple explanation of what Im trying to acheive here...)

It seems to me the best way to do this would be something like the following tables..

PRODUCT (0f course)

SIZE (with fields like size_id, size_name [eg - "sizes 8-12"])

SIZEOPTIONS (with fields like size_id, sizeoptions_id, size_option [eg - "size 8"]

PRODUCT_SIZE (this will link a product_id with the size_id, so we can say 'this product comes in this range of sizes')

Why I thought this system would be a good idea was because we could create a sizerange (size table), then fill in the different options for that range (sizeoptions table) then link a product with one or more 'size ranges'..

All this would be so that the size 'range' could be added to the product, and I thought I would still have the ability to quickly remove a size, say 'size 12' from a particular product if that became out of stock, but not have to continually make a new 'size range' if you get my drift.

Just explaining this makes me think its probably not the best way to go about this... added to this the fact that I ultimately just added a 'sizes available' field to the product_size table, which, eg, if we were missing size 10, would just fill in that field with a series of comma separated sizes "8,12,14,16" (filled in when the admin selects the size range, then unchecks the size options which are temporarily unavailble...) which would then have to be broken down and listed..

Does anyone have a clue what Im trying to explain ?

Is this even close to a typical table design that would solve the basic "being able to add a size range to a product then remove a size option temporarily if needed" ??

thanks for any input..

edit: (if it helps to understand I probably should have just made the size table an option table, as that is essentially what I want to add to the product, options...)

[Elite]

I think I'd go with something like this:

PRODUCTS
id
name

SIZES
id
name

PRODUCTS_2_SIZES
product_id
size_id

Where PRODUCTS is your table of products, SIZES is all the sizes you offer for any product and PRODUCTS_2_SIZES links the two together...

When adding a new product I'd have checkboxes to allow the administrator to select which sizes were available for each product...

Hope this makes some sense!

[waltonics]

Hey Elite, sorry for not getting back to thank you, Id almost forgot about this...

Anyway, it was sort of hacked together in a bad sort of way in the end, I didnt really need it done 'properly', just wanted to...

Thanks again though, I think your simpler way would actually fix the problem, I was just getting a bit stuck on "size sets" and "available sizes in selected size set"... Instead of just thinking that a list of options could be defined without worrying about sets...

ahh, confusing, but thankyou. I will also hunt around for an open source script to see how others have solved the 'options' problem.... I was suprised on first glance to see that most of the known ecommerce scripts seem to just rely on adding a unique product for each size or color...