CODEWITHSUNDEEP
sqlprimary-key
byteWalrus
byteWalrus

Reputation: 825

SQL - How to appropriately setup multiple values in one column linked to values in another table?

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

Answers (2)

SWeko
SWeko

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:

  • you can add a food to a user with a simple insert, instead of an append
  • you can delete a food from a user with a simple delete, instead of a costly string manipulation
  • you can delete a food from the system (i.e. no more cookies), by simply deleting all relevant rows from the UserFoods table, instead of wrangling all preferred food strings.
  • you can query if a food is used, or by how many persons a food is used easily
  • you can have a simple "most liked foods" query
  • etc, etc...

Upvotes: 3

Thomas B. Lze
Thomas B. Lze

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

Related Questions