Store list of values (such as interests)

Question

I've got a table of Users (ID, FNAME, LNAME, INTERESTS,...) plus another table to hold the specific set of INTERESTS they can choose from: Film, TV, Radio, Stage, Standup.

They can have more than one interest, so how can I store this information in the Users INTERESTS's field? Or what is the best alternative method to achieve this?

This helped me: http://stackoverflow.com/questions/1009025/facebook-database-design — Jared Burrows, Feb 24 '14 at 04:13

Neville Kuyt · Answer 1 · 2011-10-14T12:59:13.930

9

It's a many-to-many relationship. You store these by intoducing a "join table".

Table Users:
-----------
UserID PK
Name
...


Table Interests
-------
InterestID PK
Description.
....


User_interest
-----------
UserID PK, FK
InterestID PK, FK

edited Oct 14 '11 at 12:59

answered Oct 14 '11 at 10:28

Neville Kuyt

29,247
1
37
52

2

The column `UserInterestID` in `User_interest` is redundant. The table should have just two columns and its primary key should be `(UserID, InterestID)`. – Hammerite Oct 14 '11 at 10:48
3

sounds good but how to I query the database to bring me a list of a user's interests using this method? – Dan Oct 15 '11 at 18:09
6

uhm - if you need to ask, you probably need to read up on SQL, joins, the relational model etc. A good place to start is http://www.w3schools.com/sql/sql_intro.asp – Neville Kuyt Oct 16 '11 at 12:12

score 8 · Answer 2 · answered Oct 14 '11 at 10:26

8

Read about database normalisation in your MySQL book. It's an important topic, so there's probably a large chapter about it.

In short, you remove interests and end up instead with a third table, like:

user_id | interest_id
--------+------------
   1    |     1
   1    |     2
   2    |     1
   3    |     4

This gives you your many-to-many relationship.

answered Oct 14 '11 at 10:26

Lightness Races in Orbit

378,754
76
643
1,055

Here is a video on database normalization which helped me: https://youtu.be/UrYLYV7WSHM – Matt G May 19 '17 at 03:22

score 3 · Answer 3 · answered Oct 14 '11 at 10:26

3

the best solution to this is to use 3 tables (a third one for storing the connection of interests to useres):

user (id, name)
interest (id, description)
user_interest (user, interest)

this is called database normalization.

answered Oct 14 '11 at 10:26

oezi

51,017
10
98
115

Store list of values (such as interests)

3 Answers3

Linked