13


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

Improve this question

3 Answers 3

Reset to default
24

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
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

While retrieving data wont this take more cycles to process in comparison to asker's structure where one can process that string to get multiple values. I'm also confused in this!
@AyushGoyal: retrieving which data exactly?
Hi, if there is select all option which contains almost 300 values, is it good to go with this approach? or I should use a flag to identify that it is for all.
@himanshu: please post it as a question, with your data layout and a sample query.
@Quassnoi : yep sure, here is my question, please give your opinion on that. dba.stackexchange.com/questions/165396/…
|
5

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.

Improve this answer

Comments

1

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.

Improve this answer

1 Comment

I personally felt Infinite Power Wisdom when used it the very first time as a boy...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.