我正在开发一个小型调查系统，该系统包括一次插入所有表格数据（将回答约90个问题）。 此外，您可以稍后查阅数据。 考虑到这一点，我想问一下使用这样的架构有什么好处：
我在调查中有几个问题，有多个复选框（有些有15个以上），人们可以选择多个复选框作为答案，我会将所有选中的选项存储在数据库中。 我通过在所有这些复选框输入（属于一个问题）中使用相同的名称属性来实现这一点： name="q_01[]" 。 这里的“问题”（不是真正的问题，更像是性能/存储优化增强）是我不想一遍又一遍地存储相同的值。 比方说，如果我有20个复选框，并且它们的值（HTML value="Real Value"属性）长的像草莓，某物等等......，我会一遍又一遍地重复相同的值，导致空间被浪费了。
相反，我想存储一个整数，它将映射到一个包含实际值的表。 这样，我只存储4个字节，而不是（UP TO）255个字符作为VARCHAR。
我听说过这样的系统，但我自己没有这样做，而且我不知道任何命名或惯例。 你们可以指出我正确的方向（通过发布一个示例/ youtube vid或有一个页面）？ 这些表是如何调用的？ 我知道外键和关系表的基础知识，所以我知道答案就在那里。
此外，如果您可以包含或给我一些如何查询此类表格的提示，那将是非常棒的！
提前谢谢你的帮助！
干杯!
I am developing a small survey system that consists on inserting all the form data at once (around 90 questions will be answered). Also, you can consult the data later . Having this in mind, I wanted to ask what were the advantages of using a schema like such:
I have several questions in the survey that will have multiple checkboxes (some have 15+), and people will be able to select MULTIPLE of those checkboxes as their answer, and I will store all their selected options in the DB. I am achieving this by using the same name attribute in all those checkbox inputs (belonging to a question) as such: name="q_01[]". The "problem" here (not really a problem, more like a performance/storage optimization enhancing) is that I don't want to store the same values over and over. Say, if I had 20 checkboxes, and their values (attribute in HTML value="Real Value") were something long like Strawberry, Something, etc.. etc.., I would be duplicating the same value over and over, leading to space being wasted.
Instead, I want to store an integer, that will map to a table that holds the real value. That way, I would only store 4 bytes, instead of (UP TO) 255 chars as VARCHAR.
I have heard of such systems, but I have not done it myself, and I don't know any namings or conventions. Could you guys point me in the right direction (by posting an example/youtube vid or a page where there is one)? How are these tables called? I know the basics of foreign keys, and relational tables, so I know the answer lays somewhere in there.
Also, if you could include or give me a hint of how I could query such tables, that would be awesome!
Thank you for your help in advance!
Cheers!
最满意答案
如果一个问题有15个可能的答案，可以彼此独立地选择，那么每个可能的答案将成为答案表的一个表格列，具有草名， raspberry等列名。列的名称不必在数据库本身中表示（尽管它将出现在属于MySQL的信息架构列表https://dev.mysql.com/doc/refman/5.0/en/columns-table.html中，如果提前使用例如，您可以从您的软件中读取）。 获取复选框值的列将全部为boolean或tinyint(1) ; 这些值既不应包含字符串strawberry也不应包含包含strawberry的记录的某些外键。
If a question has 15 possible answers which can be chosen independently of each other, then each possible answer will become one table column of your answer table, having column names like strawberry, raspberry, etc. The name of the column doesn't have to be represented in the database itself (although it will be present in the information schema columns table https://dev.mysql.com/doc/refman/5.0/en/columns-table.html belonging to MySQL that, if for advance use cases, you can read from your software). Your columns for taking the checkbox values will all be boolean or tinyint(1); these values should neither contain the string strawberry nor some foreign key to a record containing strawberry.
具有映射到另一个表的表的效率，其中它们的实际值保存在MySQL中(Efficiency of tables that have a mapping to another table where their real value is held in MySQL)我正在开发一个小型调查系统，该系统包括一次插入所有表格数据（将回答约90个问题）。 此外，您可以稍后查阅数据。 考虑到这一点，我想问一下使用这样的架构有什么好处：
我在调查中有几个问题，有多个复选框（有些有15个以上），人们可以选择多个复选框作为答案，我会将所有选中的选项存储在数据库中。 我通过在所有这些复选框输入（属于一个问题）中使用相同的名称属性来实现这一点： name="q_01[]" 。 这里的“问题”（不是真正的问题，更像是性能/存储优化增强）是我不想一遍又一遍地存储相同的值。 比方说，如果我有20个复选框，并且它们的值（HTML value="Real Value"属性）长的像草莓，某物等等......，我会一遍又一遍地重复相同的值，导致空间被浪费了。
相反，我想存储一个整数，它将映射到一个包含实际值的表。 这样，我只存储4个字节，而不是（UP TO）255个字符作为VARCHAR。
我听说过这样的系统，但我自己没有这样做，而且我不知道任何命名或惯例。 你们可以指出我正确的方向（通过发布一个示例/ youtube vid或有一个页面）？ 这些表是如何调用的？ 我知道外键和关系表的基础知识，所以我知道答案就在那里。
此外，如果您可以包含或给我一些如何查询此类表格的提示，那将是非常棒的！
提前谢谢你的帮助！
干杯!
I am developing a small survey system that consists on inserting all the form data at once (around 90 questions will be answered). Also, you can consult the data later . Having this in mind, I wanted to ask what were the advantages of using a schema like such:
I have several questions in the survey that will have multiple checkboxes (some have 15+), and people will be able to select MULTIPLE of those checkboxes as their answer, and I will store all their selected options in the DB. I am achieving this by using the same name attribute in all those checkbox inputs (belonging to a question) as such: name="q_01[]". The "problem" here (not really a problem, more like a performance/storage optimization enhancing) is that I don't want to store the same values over and over. Say, if I had 20 checkboxes, and their values (attribute in HTML value="Real Value") were something long like Strawberry, Something, etc.. etc.., I would be duplicating the same value over and over, leading to space being wasted.
Instead, I want to store an integer, that will map to a table that holds the real value. That way, I would only store 4 bytes, instead of (UP TO) 255 chars as VARCHAR.
I have heard of such systems, but I have not done it myself, and I don't know any namings or conventions. Could you guys point me in the right direction (by posting an example/youtube vid or a page where there is one)? How are these tables called? I know the basics of foreign keys, and relational tables, so I know the answer lays somewhere in there.
Also, if you could include or give me a hint of how I could query such tables, that would be awesome!
Thank you for your help in advance!
Cheers!
最满意答案
如果一个问题有15个可能的答案，可以彼此独立地选择，那么每个可能的答案将成为答案表的一个表格列，具有草名， raspberry等列名。列的名称不必在数据库本身中表示（尽管它将出现在属于MySQL的信息架构列表https://dev.mysql.com/doc/refman/5.0/en/columns-table.html中，如果提前使用例如，您可以从您的软件中读取）。 获取复选框值的列将全部为boolean或tinyint(1) ; 这些值既不应包含字符串strawberry也不应包含包含strawberry的记录的某些外键。
If a question has 15 possible answers which can be chosen independently of each other, then each possible answer will become one table column of your answer table, having column names like strawberry, raspberry, etc. The name of the column doesn't have to be represented in the database itself (although it will be present in the information schema columns table https://dev.mysql.com/doc/refman/5.0/en/columns-table.html belonging to MySQL that, if for advance use cases, you can read from your software). Your columns for taking the checkbox values will all be boolean or tinyint(1); these values should neither contain the string strawberry nor some foreign key to a record containing strawberry.
发布评论