How to map combinations of things to a relational database?
- by Space_C0wb0y
I have a table whose records represent certain objects. For the sake of simplicity I am going to assume that the table only has one row, and that is the unique ObjectId. Now I need a way to store combinations of objects from that table. The combinations have to be unique, but can be of arbitrary length. For example, if I have the ObjectIds
1,2,3,4
I want to store the following combinations:
{1,2}, {1,3,4}, {2,4}, {1,2,3,4}
The ordering is not necessary. My current implementation is to have a table Combinations that maps ObjectIds to CombinationIds. So every combination receives a unique Id:
ObjectId | CombinationId
------------------------
1 | 1
2 | 1
1 | 2
3 | 2
4 | 2
This is the mapping for the first two combinations of the example above. The problem is, that the query for finding the CombinationId of a specific Combination seems to be very complex. The two main usage scenarios for this table will be to iterate over all combinations, and the retrieve a specific combination. The table will be created once and never be updated. I am using SQLite through JDBC. Is there any simpler way or a best practice to implement such a mapping?
