Reputation: 825
I am currently setting up a simple database with two tables at the moment. One of them contains the users, their login information, etc. This table also has a column for foods they want to eat.
The other table contains the foods they can choose from. Each food has an ID number.
Currently, I was thinking about separating the User Food column values by commas, and then fetching each food from the other table by iterating through them.
For example:
User: Food I Want to Eat - "1, 4, 3"
Foods: 1 - Apple 2 - Orange 3 - Banana 4 - Cookie
However this looks like the wrong way to do it to me. Could anybody suggest an alternate/correct way to accomplish this?
Upvotes: 1
Views: 125
Reputation: 30942
The correct way is to use a third table something like
Create table UserFoods (UserID int, FoodID int)
and for each user have as many rows as the user has preferred foods, i.e. If user 1 likes apples and oranges, and user 2 likes apples and bananas, you would have the following values in UserFoods:
UserID | FoodID
---------------
1 | 1
1 | 2
2 | 1
2 | 3
Some advantages to this method:
UserFoods
table, instead of wrangling all preferred food strings.Upvotes: 3
Reputation: 1120
You should use a third table users_food (user_id, food_id)
to have a N-N relation between users an food.
Look at http://en.wikipedia.org/wiki/Database_normalization
Upvotes: 6