I have a product entity which has several classes (each product type has different fields with thousands of product types). One product class could be a hammer which would have fields of "handle length", "head weight", etc. Another could be a chair with fields of "cushion material", "box spring", etc. Customers add fields based on their specific needs, almost like Keywords to an image catalog. These end up being search fields, but we don't want to use plain text searching because the products will have specific forms that cater to the product class.
The product breaks down in to three kinds of fields of description, image, and price similar to:
Product.Desc.HandleLength
Product.Desc.HeadWeight
Product.Image.FrontFace
Product.Price.RetailCost
Product.Price.ManufacturersSalePrice
Which I could simplify to:
Product.Desc["HandleLength"]
Product.Desc["HeadWeight"]
Product.Image["FrontFace"]
Product.Price["RetailCost"]
Product.Price["ManufacturersSalePrice"]
Would it be best to have the repository entity be an object with three arrays of content that varies? Any ideas on a good way to represent an object like this? I was even considering some sort of "factory repository" lol.
If your desctiptions (or images) can be shared between multiple products, define a separate table for them, otherwise just keep HandleLength etc. as the fields of the main table.
If your properties are heavily product-dependent (i. e. there are at least ten properties that are not shared by all products), then you should consider creating a class table:
classId parent
[Tool] [NULL]
[Screwdriver] [Tool]
, a class-property table
classId property
[Screwdriver] Type
[Screwdriver] Size
, and a value table
ItemId classId property value
1 [Screwdriver] Type PH
1 [Screwdriver] Size 2
Prices tend to be slowly changing dimensions.
You should pick either of the ways to store them (type 1, type 2 etc.), depending on how often they change and what are your requirements for keeping the price history.