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 |
Model2:
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 |
From 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.
From 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?
product id
but avariant id
. Could be universal (i.e., unique in the system) or alternatively could be a variant relative to a givenproduct id
. Use the unique key for a particular variant as primary key for a table which will have a row for each variant 's attributes - that is, for the primary key it will associate an attribute and its value.