Storing user interests and yes/no questions in db

whats the best approach?

[phph]

Hi everyone,

I need help choosing the way to store user interests and yes/no type questions into the USERS table of my site. I use MySQL + PHP.

For yes/no questions I was thinking about three methods:

ENUM('y','n')
TINYINT(1) (using 0 for no and 1 for yes)
SET (i got around 15 yes/no questions in all so they will fit into two bytes i guess)

So whats the best approach? I need to have that info retrieved from db when user logins and have it available on every page (maybe store them as SESSION vars, i use PHP), so that I can customize pages accordingly).

As to the interests, I was thinking about using a VARCHAR field and store them like [i1][i2][i5] etc. and check using strpos in PHP for example. Or maybe use the SET datatype? I think it's quite efficient, but will it let me select users that are interested in, say, i4 and i9 no matter what their other interests are? (if any).

All help is greatly appreciated.

[carguy84]

I'd break it out like this

tblQuestion
questionId int - AutoIncrement
questionText varchar(250)
statusId int

tblAnswer
answerId int
questionId int
answerText varchar(250)

tblUserAnswer
userAnswerId int - AutoIncrement
userId int
answerId int (int allows you for future expansion if you want to have more then 2 choices)

then when a user logs in, you can just do a join and select the data you want

select q.*, a.*, ua.*
from tblQuestion q, tblAnswer a, tbleUserAnswer ua
where q.questionId = a.questionId and a.answerId = ua.answerId and ua.userId = @userId

or something along those lines.