I'm trying to wrap my head around designing my database which will store one or more preferences for many categories for each user. So in other words, each user can select one or more options from the Colors category, one or more options from the Shapes category, and so on.
My initial thought was to first have a User table with generic user information. Next, there would be a table to store all the different categories as so:
CATEGORY_ID | CATEGORY_VALUE
--------------------------------
1 | Colors
2 | Shapes
3 | Sizes
I'd separate each Category into it's own table (Colors for example):
OPTION_ID | OPTION_VALUE
------------------------------
1 | Red
2 | Blue
3 | Green
Finally, I would have a User Preferences table:
USER_ID | CATEGORY_ID | OPTION_ID
----------------------------------------
1 | 1 | 2
1 | 1 | 3
1 | 3 | 2
2 | 1 | 3
Am I on the right track here or is there a better/more efficient way to designing this. I will be setting up a search results page which will allow visitors to filter through these different categories.
Thanks!
