Mysql, storing multiple value in single column from another table

Question

Bear with me, im really bad at explaining thing and i dont even know an appropriate title for this problem
Ok guys i have this problem
I already have one table name meal

+------+--------+-----------+---------+
|  id  |  name  | serving   |  price  |
+------+--------+-----------+---------+
|  1   | soup1  |  2 person |  12.50  |
+------+--------+-----------+---------+
|  2   | soup2  |  2 person |  15.50  |
+------+--------+-----------+---------+
|  3   | soup3  |  2 person |  23.00  |
+------+--------+-----------+---------+
|  4   | drink1 |  2 person |  4.50   |
+------+--------+-----------+---------+
|  5   | drink2 |  2 person |  3.50   |
+------+--------+-----------+---------+
|  6   | drink3 |  2 person |  5.50   |
+------+--------+-----------+---------+
|  7   | frui1  |  2 person |  3.00   |
+------+--------+-----------+---------+
|  8   | fruit2 |  2 person |  3.50   |
+------+--------+-----------+---------+
|  9   | fruit3 |  2 person |  4.50   |
+------+--------+-----------+---------+

Ok now i want to allow admin to create a combo meal from this meal table
So that mean, a combo meal can have unlimited number amout of meal

Currently im puzzle how to store/link combo meal to the meal I donw want to store something lke below

+------+--------------+-----------+-----------+
|  id  |  combo_name  | serving   |  meal_id  |
+------+--------------+-----------+-----------+
|  1   |   combo1     |  2 person |   1,4,7,9 |
+------+--------------+-----------+-----------+
|  2   |   combo2     |  2 person |   2,5,8   |
+------+--------------+-----------+-----------+
|  4   |   combo3     |  2 person |   3,5,6,9 |
+------+--------------+-----------+-----------+

Look at the meal_id column, i dont think that is a good way to store a data

Answer 1

Create a many-to-many link table:

combo_id    meal_id
1           1
1           4
1           7
1           9
2           2
2           5
2           8
3           3
3           5
3           6
3           9

To select all meals for a given combo:

SELECT  m.*
FROM    combo_meal cm
JOIN    meal m
ON      m.id = cm.meal_id
WHERE   cm.combo_id = 1

Answer 2

No. It's definitely not a good way to store data. You will be better with a combo_header table and a combo_details table.

combo_header will be something like:

+------+--------------+-----------+
|  id  |  combo_name  | serving   |
+------+--------------+-----------+
|  1   |   combo1     |  2 person |
+------+--------------+-----------+
|  2   |   combo2     |  2 person |
+------+--------------+-----------+
|  4   |   combo3     |  2 person |
+------+--------------+-----------+

And then, combo_details will be something like:

+------+-----------+
|  id  |  meal_id  |
+------+-----------+
|  1   |  1        |
+------+-----------+
|  1   |  4        |
+------+-----------+
|  1   |  7        |
+------+-----------+
|  1   |  9        |
+------+-----------+
... / you get the idea!

By the way, by using multiple values in a single column you are violating first normal form of relational databases.

The way I'm proposing will let you answer queries like get all name of the meals of combo1 very easy to resolve.

Answer 3

This is called a many-to-many relationship between meals and combo. A meal can be listed in multiple combos and a combos can contain multiple meals. You will need a link table (instead of the combo.meal_id field) that contains all possible meal-combo pairs.

In the end, you will have three tables:

1. meal (meal_id, serving, name)
2. combo (combo_id, serving, name)
3. meal_combo (autoid, meal_id, combo_id)

meal_combo.autoid is not strictly needed, it's just a general recommendation.

To list a combo with all it's meals in it:

SELECT meal.id, meal.name FROM comboINNER JOIN meal_combo ON meal_combo.combo_id = combo.id INNER JOIN meal ON meal.id = meal_combo.meal_id WHERE combo.id = 132

Google for 'many-to-many relationship' or 'database link table' for details.