Say you have an online store, and each product belongs to one catalog. In each catalog, you specify what custom attributes its products have. Eg:
- Catalog: Transformers
- Product Attributes: Character, Allegiance
- Catalog: Games
- Product Attributes: Number of Players, Average Game Time
When viewing a catalog, you want to enable users to filter by any custom product attribute. This is done by choosing which attribute to filter by from a dropdown, and then choosing the desired value for that attribute from a dropdown. Eg: filter products where “Character” is “Megatron”.
Here’s the question: How do you determine what values to put in the second dropdown?
Obviously, you can just iterate over each of the products in the catalog, gathering the values for each custom attribute. However, that requires you to load every product from the database. Yick.
The only alternative that I can think of is to store (in each catalog) the values for its products’ custom attributes.
I'd love to have a standard set of attributes, but it's just not possible. This is because so many products are so different. For example, the "Character" and "Allegiance" attributes are needed for Transformers products, but don't apply to Barbie or Board Game products.
Your suggestion of storing the custom attributes and their possible values on a per-catalog basis in an index is a more efficient approach to the alternative that I wrote in the gist. It still requires data denormalization, which I think is unavoidable here.