Storing lots of user information into one MySQL table?

I'm working on a application where each user has to fill out an extensive profile for themselves.

The first part of the user profile consists of about 25 or so fields of general information

The next section of the user profile is a section where they evaluate themselves on a set list of criteria. ie, "Rate how good you are at cooking" and then they tick a radio box from one to five, there is also a check box that the can check if they are 'extra interes开发者_如何学Cted' in the activity/subject they rated themselves on.

There are about 40 of these that they rate themselves on.

So my question is, how should I store this information, should there be columns in my users table for every field and item? This would be nearly 70 fields

or should I setup a table for user_profile, and user_self_evaluation, and have the columns for each in there and have a one-one relationship with the users?

---

Use separate tables. In this way when you update only self evaluation, you does not need to update the user_profile table. The idea here is to separate the often updated fields in another table, leaving the rarely updated on another location. If the table became large, and the username/password is in separate table, the performance of lookup by userid / username won't be affected by the large amount of update queries, nor you'll bring the whole site down if you alter the self_evaluation table.

But if you are planning to add new evaluations, I'd suggest a different design: user_profile table with the 25 profile field self_evaluations table, with id and name, and any meta information about the question; with 1 record per evaluation user_profile_evaluation with userid, evaluationid, score, extra - with one record for each evaluation of the user.

This way your schema will be much more flexible and you won't need to alter the table in order to add another evaluation.

---

or should I setup a table for user_profile, and user_self_evaluation, and have the columns for each in there and have a one-one relationship with the users?

Yes, this is how you should do it, if you know you won't expand the table in the future. The other option is too bad.

---

If you think you will expand the evaluations in the future, then you can do it like this:

user_self_evaluation table

user_id | evaluation_type | evaluation_value
1       | cooking         | 5
1       | singing         | 3
2       | cooking         | 2
2       | singing         | 5

Make the combined columns (user_id, evaluation_type, evaluation_value) a unique or the primary.

---

I think the latter one is better, a table with 70 columns is really bad-looking and can get really worse if you try to manage it.

When every question is multiple choice you can also add numbers in one field for each answer.

Let's say you've got four questions with 4 choices:

You could save them as 1433 in one column called answerers, (the first question is answer 1, second answer 4, third answer 3, and last but not least question 4 is answer 3)

Just giving you some choices here.

But if I had to choose between one-one relationship and 1 table, I would choose one one relationship because it's easier to manage later on.