code_junkie

Designing an 'Order' schema in which there are disparate product definition tables

Nov 11th, 2011
112
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.81 KB | None | 0 0
  1. Table Product ( id PK, name, price, units_per_package)
  2. Table Product_Attribs (id FK ref Product, AttribName, AttribValue)
  3.  
  4. Table Product (id PK, name, price, units_per_package, attrName_1, attrValue_1 ...)
  5.  
  6. Attribute(attribute_id, description, is_listed)
  7. -- contains values like "colour", "width", "power source", etc.
  8. -- "is_listed" tells us if we can get a list of valid values:
  9.  
  10. AttributeValue(attribute_id, value)
  11. -- lists of valid values for different attributes.
  12.  
  13. Product (product_id, description)
  14.  
  15. ProductAttribute (product_id, attribute_id)
  16. -- tells us which attributes apply to which products
  17.  
  18. Order (order_id, etc)
  19.  
  20. OrderLine (order_id, order_line_id, product_id)
  21.  
  22. OrderLineProductAttributeValue (order_line_id, attribute_id, value)
  23. -- tells us things like: order line 999 has "colour" of "blue"
Add Comment
Please, Sign In to add comment