I need to model the persistence of combinatorial information. For example, suppose that the combination of 3 attributes (A, B, and C) are used to reference a given product. Besides that, supposed that a given product can belong to different combinations of A, B and C. The queries will be used to constraint the possibilities of product selection based on user requirements. Here, I'm giving a simple example, but this system is intended to combine hundreds of tables with thousands of combinations.
For now I can think in two approachs: Model1:
Table: PRODUCT_SEARCH_CONDITIONS | key search id | attribute 1 | attribute 2 | product id | | 1 | value 1 | value 1 | 1 | | 2 | value 1 | value 2 | 1 | | 3 | value 2 | value 3 | 2 | | 4 | value 2 | value 4 | 2 | | 5 | value 3 | value 5 | 1 |
Table: PRODUCT_SEARCH_CONDITIONS | key search id | product id | | 1 | 1 | | 2 | 2 | | 3 | 1 | Table: PRODUCT_SEARCH_ATTRIBUTE_1 | key search id | attribute 1 | | 1 | value 1 | | 2 | value 2 | | 3 | value 3 | Table: PRODUCT_SEARCH_ATTRIBUTE_2 | key search id | attribute 1 | | 1 | value 1 | | 1 | value 2 | | 2 | value 3 | | 2 | value 4 | | 3 | value 5 |
model1, the queries and the modeling are simple, basically because I have only one table that store all possible combinations extensively. However, I have the combinatorial explosion problem because each line represents a valid combination. Indeed, if I add more variables and values, then it grows exponentially according to the valid combinations.
model2 the queries and the modeling are more complex. However I don't need to store all possible combinations extensively, I can group valid combinations together (e.g. key search id 1 that group values of attribute 2). Besides that, the queries can also be solved more efficiently, as I can calculate the intersection between the IDs found for each attribute given the user requirements.
My question is, there exists a better modeling approach that can have the advantages of both modelings (simplicity and performance)? Or even, a specific database (or search engine) technology that better support this kind of problem?