How to store arrays in MySQL? (A: Many to many tables)

I am trying to create a way for users to select nutrients they are interested in. I want to store this nutrient preference in a MySQL table.

The best way I can think to do this is to store the nutrients as an array. So

[‘Protein’, ‘Carbs’, ‘Fat’, ‘Sodium’, ‘Cholesterol’, ‘Vitamin-B12’]

I like this solution since a user might select 2-20 nutrients of interest.

However, storing arrays in MySQL is messy, and I have been told it is better to normalize the data, or make it relational.

How do I do this?

Right now I have::
Table: Users
User ID, email

Table: Preferences
Columns: User Id, Nutrient_Preference_Array

How to normalize the array data?

The answer from the slack is Many to Many relational tables.

This course will help:
https://www.udemy.com/course/the-ultimate-mysql-bootcamp-go-from-sql-beginner-to-expert/

2 Likes