We have a main table, let's say, Product. It has several general attributes like 'Product Name', Manufacturer ID, etc. As of now, all these attributes are implemented in the data model as columns to the Product.
Now we have a new requirement where there could be thousands of attributes. Color, size, weight, dimensions, style, etc. Each of these attributes have pre-defined values. Like color can only be ('green', 'yellow', 'red'). The value set is defined for each attribute. But there could be thousands of attributes and each attribute could have thousands of pre-defined values.
So, for this new feature, we defined these attributes as 'row values' instead of 'columns'.
Something like this:
ProductID Name Date_Created 1 Candy some date 2 Shirt some date 3 Pants some date
AttributeID Name 1 Color 2 Dimension 3 Weight
AttributeValueID AttributeID Value 1 1 Green 2 1 Yellow 3 1 Red 4 2 11x11 5 2 12x12 6 2 13x13 7 3 1 lb 8 3 2 lb 9 3 3 lb
Now to create association between Product Table and Attribute table, we have an association table called 'ProductAttributes'
ProductAttributeId ProductID AttributeID AttributeValueID 1 1 1 1 2 1 2 4 3 1 3 9 4 2 1 2 5 2 3 8 6 3 3 8
Does this model make sense? Could you suggest any improvements? As of now this ProductAttributes Table is expected to grow to 200 Million as there are a more than 100K attributes.
1. A product could have more than one attribute
2. A product could have more than one of the same attribute. A shirt is both 'brown' and 'green'
3. All Attributes and its Values have to be pre-defined (loaded from text file)
4. Once the data is loaded (once every few months), there is heavy SELECTing. So the model needs to be read-optimized.