e-commerce structure for products (MySQL)

I am considering how to structure my MySQL database in e-commerce solution. To be more specific I am looking开发者_如何学编程 at the product structure.

These are the tables I have come up with so far. What do you think?

Explanation of structure

The application is multilingual. Therefore the product table are split in 2 tables.

If a products has e.g. 2 variants (Small, Medium) a total of 3 rows will be inserted. This is because each variant can have different information for each variant. When the product is shown on the webpage product 1 will be shown with a drop down box with Small & Medium. A product with no variants will naturally only insert 1 row.

products
id  master  product_number
1   0       123
2   1       456
3   1       678


products_descriptions
id  product_id  country_code  image      name     description   vat  price
1   1           en-us         image.jpg  t-shirt  Nice t-shirt  25   19.99
2   2           en-us         image.jpg  t-shirt  Nice t-shirt  25   19.99
3   3           en-us         image.jpg  t-shirt  Nice t-shirt  25   19.99

products_to_options
product_id  option_id
2           1
3           2

options
id  name
1   Small
2   Medium

Your Products table is schizophrenic, its entity is sometimes Product and sometimes Variant. This leads to very cumbersome behavior. For example, you'd like the question "how many different products do we have?" be answered by select count(*) from products, but here this gives the wrong answer, to get the correct answer you have to know the Magic Number 0 and query select count (*) from products where master=0. "List all products and how many variants we have for each" is another query that should be straightforward but now isn't. There are other anomalies, like the fact that the first line in products_descriptions is a shirt that has a price and a picture but no size (size is stored in the variants, but they have prices and pictures of their own).

Your problem sounds like you have products in two contexts: (1) something that can be displayed as an item in your store, and (2) something that can be ordered by your customer. (1) probably has a name like "Halloween T-Shirt" or so, and it probably has an image that the customer sees. (2) is what the customer orders, so it has a (1), but also a variant specification like "small" or maybe a color "red". It probably has a price, too, and an order_id so your shop can know what specific item to ship.

You should give each context an entity. Here's how i'd do it

displayable_product
id   name
1    "Baseball Cap"
2    "T-Shirt"

orderable_product
id   d_product_id order_id  size    color   price
1    1            123               red      9.99
2    2            456       small           19.99
3    2            789       medium          21.99

displayable_content
id   d_product_id  locale  name                 image
1    1             en_US   "Baseball Cap"       baseballcap.jpg
2    1             es_US   "Gorra de Beisbol"   baseballcap.jpg
3    2             en_US   "Nice T-Shirt"       nicetshirt.jpg
4    2             es_US   "Camiseta"           nicetshirt.jpg

You should probably use locale instead of country in the display tables to account for countries with more than one language (USA, Switzerland, and others), and you might separate the size and color into its own variants table. And if you need country-dependent data on the orderables (like different prices/currencies for shipping to different countries), you'd have to extract a country-dependent orderable_content table, too.