Should Foreign Keys be used in a structure where multiple options can be selected by a user? If so, how so?

Question

In MySQL, I was advised to store the multiple choice options for "Drugs" as a separate table user_drug where each row is one of the options selected by a particular user. I was also advised to create a 3rd table drug that describes each option selected in table user_drug. Here is an example:

user
id    name  income
1     Foo   10000
2     Bar   20000
3     Baz   30000

drug
id    name
1     Marijuana
2     Cocaine
3     Heroin

user_drug
user_id drug_id
1       1
1       2
2       1       
2       3
3       3

As you can see, table user_drug can contain the multiple drugs selected by a particular user, and table drug tells you what drug each drug_id is referring to.

I was told a Foreign Key should tie tables user_drug and drug together, but I've never dealt with Foreign Key's so I'm not sure how to do that.

Wouldn't it be easier to get rid of the drug table and simply store the TEXT value of each drug in user_drug? Why or why not?

If adding the 3rd table drug is better, then how would I implement the Foreign Key structure, and how would I normally retrieve the respective values using those Foreign Keys?

(I find it far easier to use just 2 tables, but I've heard Foreign Keys are helpful in that they ensure a proper value is entered, and that it is also a lot faster to search and sort for a drug_id than a text value, so I want to be sure.)

Answer 1

Wouldn't it be easier to get rid of the drug table and simply store the TEXT value of each drug in user_drug? Why or why not?

Easier, yes.
But not better.

1. Your data would not be normalized, wasting lots of space to store the table.
2. The index on that field would occupy way more space again wasting space and slowing things down.
3. If you want to query a drop-down list of possible values, that's trivial with a separate table, hard (read: slow) with just text in a field.
4. If you just drop text fields in 1 table, it's hard to ensure misspellings do not get in there, with a separate link table preventing misspellings is easy.

If adding the 3rd table drug is better, then how would I implement the Foreign Key structure

ALTER TABLE user_drug ADD FOREIGN KEY fk_drug(drug_id) REFERENCES drug(id);

and how would I normally retrieve the respective values using those Foreign Keys?

SELECT u.name, d.name as drug
FROM user u
INNER JOIN user_drug ud ON (ud.user_id = u.id)
INNER JOIN drug d ON (d.id = ud.drug_id)

Don't forget to declare the primary key for table user_drug as

PRIMARY KEY (user_id, drug_id)

Alternatively
You can use an enum

CREATE TABLE example (
  id UNSIGNED INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
  example ENUM('value1','value2','value3'),
  other_fields .....

You don't get all the benefits of a separate table, but if you just want a few values (e.g. yes/no or male/female/unknown) and you want to make sure it's limited to only those values it's a good compromise.
And much more self documenting and robust than magic constants (1=male, 2=female, 3= unknown,... but what happens if we insert 4?)

Answer 2

Wouldn't it be easier to get rid of the drug table and simply store the TEXT value of each drug in user_drug? Why or why not?

Normally, you'd have lots of other columns on the drug table -- things like description, medical information, chemical properties, etc. In that case, you wouldn't want to duplicate all of that information on every record of the user_drug table. In this particular case however, you've only got one column, so that issue is not really a big deal.

Also, you want to be sure that the drug referenced in the user_drug table actually exists. For example, if you store the field as text, then you could have heroin and its related misspellings like haroin or herion. This will give you problems when you try to select all heroin records later. Using a foreign key to a lookup table forces the id to exist in that table, so you can be absolutely sure that all references to heroin are accurate.