MYSQL - multiple many to many selection

Question

I have multiple many to many relations related with one main entity.

Tables (they are different in names, but its easier to demo):

products
    - id
    - every other data


colors
    - id
    - every other data

sizes
    - id
    - every other data

product_color
    - product_id
    - color_id

product_size
    - product_id
    - size_id

I want to have a number stored somewhere, that I have e.g. 50 pieces of GREEN and XXL sized T-shirt product, but only 20 pieces of green and M sized T-shirt product. Where would I store this number? Is my DB structure efficient for that one at all? I'm using eloquent + mysql.

Answer 1

I'd lose the product_color and product_size tables, and add a stock table with foreign keys to the product, size and color:

stock
  - id         -- May be redundant, depends on your application
  - product_id -- FK to products.id
  - color_id   -- FK to colors.id
  - size_id    -- FK to sizes.id
  - amount