I got headache to get a correct result when using FIND_IN_SET
or LIKE
.
Example my value in database like this 1,2,3,4,5,6,7,8
.
Lets say I have one form with many checkboxes. Each checkbox is coming with dynamic value which can add by users. Example value is colors and have green, yellow, white, black, red, pink, brown, etc...
My current structure tbl_colors
& tbl_users
color_id color_name
----------------------
1 yellow
2 black
3 green
. ...
. ...
20 pink
tbl_users
user_id color_id
----------------------
1 1,2,3,4
2 3,4,5,6,8,9,10
3 1
. ...
. ...
20 1,10,20
Question
How do I normalize & restructure my current database above and easy me to count how much users like yellow color, brown color etc..
you need an extra link table.
tbl_colors
----------
color_id
color_name
tbl_users
---------
user_id
...
tbl_users_colors_link
---------------------
user_id
color_id
Please note that link table shouldn't include unique fields. Also no primary key is required. Only indexing id fields make sense.