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

in mysql, advised store multiple choice options "drugs" separate table user_drug each row 1 of options selected particular user. advised create 3rd table drug describes each option selected in table user_drug. here 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 can see, table user_drug can contain multiple drugs selected particular user, , table drug tells drug each drug_id referring to.

i told foreign key should tie tables user_drug , drug together, i've never dealt foreign key's i'm not sure how that.

wouldn't easier rid of drug table , store text value of each drug in user_drug? why or why not?

if adding 3rd table drug better, how implement foreign key structure, , how retrieve respective values using foreign keys?

(i find far easier use 2 tables, i've heard foreign keys helpful in ensure proper value entered, , lot faster search , sort drug_id text value, want sure.)

wouldn't easier rid of drug table , store text value of each drug in user_drug? why or why not?

easier, yes.
not better.

1. your data not normalized, wasting lots of space store table.
2. the index on field occupy way more space again wasting space , slowing things down.
3. if want query drop-down list of possible values, that's trivial separate table, hard (read: slow) text in field.
4. if drop text fields in 1 table, it's hard ensure misspellings not in there, separate link table preventing misspellings easy.

if adding 3rd table drug better, then how implement foreign key structure

alter table user_drug add foreign key fk_drug(drug_id) references drug(id); 

and how retrieve respective values using foreign keys?

select u.name, d.name drug 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 declare primary key table user_drug

primary key (user_id, drug_id) 

alternatively
can use enum

create table example (   id unsigned integer not null primary key auto_increment,   example enum('value1','value2','value3'),   other_fields ..... 

you don't benefits of separate table, if want few values (e.g. yes/no or male/female/unknown) , want make sure it's limited only values it's compromise.
and more self documenting , robust magic constants (1=male, 2=female, 3= unknown,... happens if insert 4?)