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?

Answer 1

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

Answer 2

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.