I need search functionality on a website where among other things you should be able to select multiple categories. The searches will be stored in the database but each unique combination of search parameters should only be stored once, this also includes the unique combination of selected categories.
The problem is that I cannot figure out how to store the combinations of selected categories. I have looked at arrays and found this http://ift.tt/Tm9EKM but apparently that feature was never implemented.
So if I need to use multiple tables I was thinking I could have one table for the combinations that has an id for each unique combination that can be referenced and compared easily and then another table linking the categories to the combination. But with this approach how can I check if a combination already exists? The only way I can think of is to loop through all existing combinations and check if any one of those equal the compared combination.
I feel like this cannot be such an uncommon problem but I can't find any examples that do this. I also feel like my approach may not be the best. Any suggestions are very much welcome.
I have these two tables currently:
- CategoryId (int)
- Name (string)
- SearchId (int)
- Keywords (string)
- ExampleOption1 (bool)
- ExampleOption2 (bool)
- CategoriesCombinationId (int) -- this would represent the unique combination of categories and links to the combination table
And this is how I might try to solve the problem (if there is a good way to check if a combination already exists):
CategoriesCombinations -- unique combinations
- CombinationId (int)
- CombinationId (int) -- links to id in combinations table
- CategoryId (int) -- links to id in categories table