code_junkie

What is best practice for this problem (different properties for different categories)?

Nov 11th, 2011
70
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.27 KB | None | 0 0
  1. Product
  2. ProductId*
  3. CategoryId: FK to Category.CategroyId
  4. Name
  5.  
  6. Category
  7. CategoryId*
  8. Name
  9.  
  10. Property
  11. PropertyId*
  12. Name
  13. Type
  14.  
  15. CategoryProperty
  16. CategoryId*: FK to Category.CategoryId
  17. PropertyId*: FK to Property.PropertyId
  18.  
  19. ProductProperty
  20. ProductId*: FK to Product.ProductId
  21. PropertyId*: FK to Property.PropertyId
  22. ValueAsString
  23.  
  24. SELECT
  25. Product.ProductId,
  26. Product.Name AS ProductName,
  27. Category.CategoryId,
  28. Category.Name AS CategoryName,
  29. Property.PropertyId,
  30. Property.Name AS PropertyName,
  31. Property.Type AS PropertyType,
  32. ProductProperty.ValueAsString
  33. FROM
  34. Product
  35. INNER JOIN Category ON Category.CategoryId = Product.CategoryId
  36. INENR JOIN CategoryProperty ON CategoryProperty.CategoryId = Category.CategoryId
  37. INNER JOIN Property ON Property.PropertyId = CategoryProperty.PropertyId
  38. INNER JOIN ProductProperty ON ProductProperty.PropertyId = Property.PropertyId
  39. AND ProductProperty.ProductId = Product.ProductId
  40. WHERE
  41. Product.ProductId = 1
  42.  
  43. tProduct
  44. productID
  45. <other product details>
  46.  
  47. tCategory
  48. categoryID
  49. <other category details>
  50.  
  51. tProperty
  52. propertyID
  53. <other property details>
  54.  
  55. tProductXCategory
  56. productyID
  57. categoryID
  58.  
  59. tCategoryXProperty
  60. categoryID
  61. propertyID
Add Comment
Please, Sign In to add comment