title: How to design a db for sales that incorporates attributes into products in an inventory?
body: <p>I am trying to design a database that incorporates products, product types, product attributes, which also stores cost and the stock (inventory). I would like this to have little as possible const/hardcoding and also be scalable for the future. </p>
  
  <p>The problem I am running into is that I would like to have multiple attributes tied to a product, some of the attributes would cause the cost of the product to increase, and to keep inventory of the product with all of those attributes.</p>
  
  <p>Here is my current database diagram:
  <img src="https://i.ibb.co/27PRykW/php-diagram.png" alt="db diagram"></p>
  
  <p>I have thought about appending a 'ProductAttributeID' column onto the ProductPricing and Product Stock/Supply tables.</p>
  
  <p>This would work for a single attribute, but not if two were to come into account.</p>
  
  <p>ie:</p>
  
  <pre><code>Product = Shirt
  Attribute = Size (small, medium, large)
  Attribute#2 = Color (red, green, blue)
  ...
  </code></pre>
  
  <p>I suppose I could create a ProductAttributeModifier table, to add/subtract/divide/multiply a value to the overall value.</p>
  
  <pre><code>ProductAttributeModifier
  ========================
  ProductAttributeID (bigint) FK_,
  Operator (char(1)), //+, -, *, /, %
  CostValue decimal(7,2)
  </code></pre>
  
  <p>That way I could do an aggregate summary of the total value of a product with multiple attributes.</p>
  
  <p>Are there any pitfalls to this method?</p>
  
  <p>How would I go about the ProductSupply? I've thought about adding multiple columns as foreign key constraints to different ProductAttributes, but that isn't scalable and requires future knowledge of the product.</p>
  
  <p>Perhaps would another table working as a junction with a separate quantity? With an iteration of all attributes? Are there are pitfalls with this method either?</p>
  
  <pre><code>ProductAttributeSupply
  ======================
  ProductSupplyId (bigint) FK_,
  AttributeID (bigint) FK_,
  Quantity (int/bigint)
  </code></pre>
  
  <p>Would it make more sense to put the overall supply aggregate in a view/sproc?</p>
  
  <p>I expect the diagram to handle the following scenarios:</p>
  
  <ol>
  <li>Product with no, single or multiple attributes</li>
  <li>Be prepared for attributes modifying cost (medium to a large)</li>
  <li>Have the cost modifier be optional</li>
  <li>Calculate the proper amount of product with all attributes matching in our inventory</li>
  <li>Properly save the amount of product matching each attribute when we receive within our database. 'ProductStock'</li>
  </ol>