How to store multiple values on a single mysql column?

@lottie 

In a MySQL column, you can store multiple values by using one of the following approaches:

1. Multiple values as a comma-separated list: You can store multiple values as a comma-separated list within a single column. However, this approach is not recommended as it violates the First Normal Form and can make data manipulation and searching difficult.

Example:

1
2
3
4
5

id  |  name       |  colors
---------------------------------------
1   |  Apple      |  red, green, yellow
2   |  Banana     |  yellow
3   |  Orange     |  orange, yellow

1. JSON data: If your MySQL version supports JSON data type (MySQL 5.7+), you can store the values as a JSON array or object within a single column. This allows you to use JSON functions for data manipulation and searching.

Example:

1
2
3
4
5

id  |  name       |  colors
---------------------------------------
1   |  Apple      |  ["red", "green", "yellow"]
2   |  Banana     |  ["yellow"]
3   |  Orange     |  ["orange", "yellow"]

1. Junction Table: If the multiple values have a relationship with another entity, you should create a separate table (junction table) to store the relationships between the main entity and each value. This is known as a many-to-many relationship.

Example:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Table: Fruits
id  |  name      
---------------
1   |  Apple     
2   |  Banana    
3   |  Orange    

Table: Colors
id  |  color     
---------------
1   |  red       
2   |  green     
3   |  yellow    
4   |  orange    

Table: Fruit_Colors
fruit_id  |  color_id
---------------------
1         |  1       
1         |  2       
1         |  3       
2         |  3       
3         |  3       
3         |  4       

In this example, the Fruit_Colors table represents the relationship between fruits and colors. Each row in the junction table represents a connection between a specific fruit and a specific color.