I have a dating site where each user has own profile with more than 20 attributes like age, gender, hobbies etc. For this I have table user_profile where all attributes are in 1 row.
For searching and match calculator, it would be probably better to have attributes values in int, better than having it in varchar. But now I am not sure how to print all attributes names if name is not also a value.
So I was thinking to create two tables:
user_profile
user_id | gendre | like_pets | drinks | body_type | eyes | hair_color | ...
1 | 1 | 3 | 2 | 4 | 5 | 5 | ...
2 | 2 | 4 | 2 | 3 | 3 | 5 | ...
user_attributes
id | attr_group | value_name | attr_value
1 | 1 | male | 1
2 | 1 | famale | 2
3 | 2 | Like pets | 1
4 | 2 | Have pets | 2
5 | 2 | Don't like pets | 3
6 | 3 | Blue | 1
7 | 3 | Gray | 2
8 | 3 | Green | 3
9 | 3 | Brown | 4
…
But that means I need to create 20 queries to print all value names for all attributes. For example to print eyes color I would need
SELECT * FROM user_attributes WHERE attr_group=3
then again the same for gender, and for each other attribute. Or I can use joins, but I would need 20 joins in 1 query. Is that a correct way?