MySQL DB multiple data for field

Maybe I have never searched in the right place but I often wonder what is the best practice to store multiple data in one field and I have never found an answer that I could use.

Let me be more clear with an example.

Let’s say I have a “hobbys” field in a user_table

When the user signup he want to add tennis, video game, and piano to his profile.

Is it better:

1. To create different fields for every hobby like: hobby_id_1, hobby_id_2, hobby_id_3

2. Or create a single field called hobbys and insert data separated with a comma ? hobbys_id => 1,4,5
   (is this solution is actually possible ?)

And then when we want to update those data and add a new hobby for example, how do we do that ?

Any small help would be greatly appreciated.

Thank you in advance 🙂

Answer

This falls under many to many relationship.

For storing many-to-many relationships, an intermediate table that mainly stores the primary keys (IDs) of each relationship is required. In your case,

users_table(user_id, firstname,…)

hobbies_table(hobby_id, name,…)

users_hobbies_table(id, user_id, hobby_id)

Here is a more elaborate explanation.