What's the most elegant MySQL schema for products, options and categories?

Question

I've worked with a dozen or so template systems (Zen Cart, Cube Cart, etc.). Each of these has their own bizarre way of structuring products, options and categories. All the add-on features result in a McGuyver'd stack of cards situation that makes working with the code a total drag.

So six years ago I built my own webstore engine, which has evolved over the years and become its own stack of cards. Now I'm doing a total overhaul on the engine. While no one engine will suit all webstore needs, I was wondering if the following model has any drawbacks, or if there's a better way to create a flexible, normalized, non-obnoxious database for commerce:

Notes:
option_types = colors, sizes, materials
options = red, white, blue, S, M, L, cotton, spandex, leather

Other than basic stuff omitted on purpose (position, active, etc.), anyone see a way to improve this?

Answer 1

Here are my notes/opinions on this. You're missing cardinalities, but I'll do my best to guess them.

• Categories is ok.

• Remove id from item_categories as you're not using it. Create a composite primary key on category_id and item_id.

giving each record a unique id is smarter in many ways: faster to look-up on one field than on two, safer to delete, etc

What lookup would you do on that id? The queries you'll run are: "Getting all categories for an item" and "Getting all items for a category". I don't understand why it would be safer to delete. However, I'd say adding an id might be unsafer to insert as you might have different ids but same category_id and item_id pairs. You'll have to check the constraints there and make sure the pairs are unique (and aren't that what PKs are used to?)

• items is ok... (see comments below)
• Remove id from item_options (same case as above and see comments below)
• option_types is ok

Now, I think the way items and options are related will require more thinking. It seems to be a many-to-many relationship. As an item, such as a T-Shirt can have many sizes it makes sense to say that each pair items and options should have a different size. But what happens when, apart from the size you also have a different material, such as cotton and leather. You will have to have information on the pairs cotton-S, cotton-M, cotton-L and leather-S, leather-M and leather-L. This makes sense as I'm pretty sure all of them will have a different price and weight. But now let's add 2 colors to our T-Shirts. You'll have to add a price and weight for each of the 12 combinations we'll have now.

Not to mention that if a user would like to see the price of an item he'll have to choose all the options until he reaches a price. I'm not sure how this should be done as I'm not aware of the requiremets. I'm just throwing an idea: you could apply prices and weight variations over a base price and weight that would be part of the item.

Just some unprocessed thoughts before going to sleep:

• option_types could be some kind of hierarchy
• Carefully think of how you would handle stock given that design. You'll have 10 items for a Black T-Shirt... but how many items will you have for a Black Leather T-Shirt? How is that number related to the 10 original ones?