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
用户配置文件的第一部分包含大约25个左右的一般信息字段
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 interested' in the activity/subject they rated themselves on.
用户配置文件的下一部分是一个部分,用于在设置的标准列表中评估自己。即,“评价您烹饪的好坏”,然后他们勾选一到五的收音机盒,还有一个复选框,可以检查他们是否对自己评价的活动/主题“特别感兴趣”。
There are about 40 of these that they rate themselves on.
他们对自己的评价大约有40个。
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
所以我的问题是,如果每个字段和项目的用户表中都有列,我应该如何存储这些信息?这将近70个领域
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?
或者我应该为user_profile和user_self_evaluation设置一个表,并在其中包含每个列的列并与用户建立一对一的关系?
3 个解决方案
#1
2
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.
使用单独的表格。这样,当您仅更新自我评估时,您不需要更新user_profile表。这里的想法是将另一个表中经常更新的字段分开,而很少在另一个表上更新。如果表变大,并且用户名/密码在单独的表中,则用户ID /用户名的查找性能不会受到大量更新查询的影响,如果您更改了更新查询,也不会将整个站点关闭自我评估表。
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.
但是,如果您计划添加新的评估,我建议使用不同的设计:user_profile表,其中包含25个配置文件字段self_evaluations表,id和name,以及有关该问题的任何元信息;每个评估user_profile_evaluation有1个记录,其中包含userid,evaluationid,score,extra - 每个评估用户有一条记录。
This way your schema will be much more flexible and you won't need to alter the table in order to add another evaluation.
这样,您的架构将更加灵活,您无需更改表以添加其他评估。
#2
0
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?
或者我应该为user_profile和user_self_evaluation设置一个表,并在其中包含每个列的列并与用户建立一对一的关系?
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.
使组合列(user_id,evaluation_type,evaluation_value)成为唯一列或主列。
#3
0
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.
我认为后一个更好,一个70列的表真的很糟糕,如果你试图管理它会变得更糟。
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)
您可以将它们保存为一个名为answerers的列中的1433(第一个问题是答案1,第二个答案4,第三个答案3,最后但并非最不重要的问题4是答案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.
但是,如果我必须在一对一关系和一个表之间做出选择,我会选择一个关系,因为以后更容易管理。
#1
2
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.
使用单独的表格。这样,当您仅更新自我评估时,您不需要更新user_profile表。这里的想法是将另一个表中经常更新的字段分开,而很少在另一个表上更新。如果表变大,并且用户名/密码在单独的表中,则用户ID /用户名的查找性能不会受到大量更新查询的影响,如果您更改了更新查询,也不会将整个站点关闭自我评估表。
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.
但是,如果您计划添加新的评估,我建议使用不同的设计:user_profile表,其中包含25个配置文件字段self_evaluations表,id和name,以及有关该问题的任何元信息;每个评估user_profile_evaluation有1个记录,其中包含userid,evaluationid,score,extra - 每个评估用户有一条记录。
This way your schema will be much more flexible and you won't need to alter the table in order to add another evaluation.
这样,您的架构将更加灵活,您无需更改表以添加其他评估。
#2
0
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?
或者我应该为user_profile和user_self_evaluation设置一个表,并在其中包含每个列的列并与用户建立一对一的关系?
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.
使组合列(user_id,evaluation_type,evaluation_value)成为唯一列或主列。
#3
0
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.
我认为后一个更好,一个70列的表真的很糟糕,如果你试图管理它会变得更糟。
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)
您可以将它们保存为一个名为answerers的列中的1433(第一个问题是答案1,第二个答案4,第三个答案3,最后但并非最不重要的问题4是答案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.
但是,如果我必须在一对一关系和一个表之间做出选择,我会选择一个关系,因为以后更容易管理。