Database schema for Product Subcategories, Colors, & Sizes
Posted on 2009-06-29
I have a general working knowledge of mySQL and I'm developing an e-commerce application using Coldfusion.
Before I dive into the client side, I want to make sure my database schema is properly in place.
Can anyone show me the most efficient way to set up my database to allow for product Subcategories, colors, and sizes? (I'm selling T-shirts).
A few things to know:
>>Subcategories - I want my users to be able to find items under multiple subcategory searches (i.e. Long-sleeve Pocket T-shirts can be found under "T-shirts>>Longsleeve", "T-shirts>>Pocket", "T-shirts>>Longsleeve>>Pocket", or "T-shirts>>pocket>>longsleeve" respectively. Many subcategory examples I've seen only allow one subcategory per product, which is not what i want.
>>Colors & Sizes - My items don't come in all colors. most of my items have a range of colors unique to that item number, so I need to only show colors on that item that are actually available in that item. the same goes for sizes, not all products come in every size.
Any help on how the schema should look for this portion of my database would be appreciated, including an Entity Relationship diagram would help, too.
Thank you for your help. :)