Database Layout For Oscommerce

[Vulcan]

I have a Category and Product table, it lists all the categories, and then inside those it lists all the products assigned to that category. Simple stuff.

But what would I do if I had a product that had multiple sizes?

Would I just create the exact same product but with different data in the `product.size` column, and then if more than 1 match was found for `product.title`, cycle through all the matches and list the content in the `product.size` column in a <select>?

Thanks.

 

[Jammin1984]

The normalised way to achieve this is to have a new ProductSizes table, which has, for each product a size.

Therefore a product with one size would have one entry in this new table, and product with 10 sizes would have 10 entries.

The un normalised way would be to have sizeOne, sizeTwo, sizeThree columns in the products table. This is only really possible if your sure of a maxminum number of sizes per product, eg in my example 3. De normalising like this isnt usually a good idea unless you have a specific reason, eg performance or reporting. Also, for example you knew a product would only ever have say two sizes. Even then it is frowned upon.

Database Normalisation

 

[Vulcan]

Would I remove the stock & size columns from products entirely then? And then add those two columns in the size table?

Or just have additional sizes in the size table and leave the default ones in the products table?

 

[sist_si]

if you were to split out the sizes to their own table, you'd have something like this:

Products_Table:
- product_id
- category_id
- size_id
- product_name
- etc....

Product_Sizes:
- size_id
- size_description

..then, it'd be something like this to select the distinct products by category:

select prod.product_id, prod.category_id, prod.size_id, prod.product_name, sizes.size_description
from Products_Table prod
inner join Product_Sizes sizes on prod.size_id = sizes.size_id
where prod.category_id = [x]