I'm not sure if this kind of question has been asked before but I searched and could find anything.
我不确定之前是否曾询问过这类问题,但我搜索过并找不到任何问题。
I am working on a database at the moment that has records that have lots of boolean based values stored with them, so the table structure looks something like this:
我目前正在处理一个数据库,其中包含许多基于布尔值的记录,因此表结构如下所示:
===Table===
ID <- int
Name <- string
Bool1 <- bool
Bool2 <- bool
Bool3 <- bool
Bool4 <- bool
Bool5 <- bool
Bool6 <- bool
Bool7 <- bool
Bool8 <- bool
Bool9 <- bool
not all the boolean values are set a once, so each record can have one,many or none selected.
并非所有布尔值都设置为一次,因此每条记录可以选择一个,多个或没有。
I have thought about doing something like this:
我想过做这样的事情:
==Main Table== ===Second Table====
ID <- int PK ValueID <- PK links to Main Table ValueID
Name <- string ID <- int
ValueID <- FK Value <- Contains name of assigned value eg Bool1, Bool2
So there is a one to many relationship between the Main Table and Second Table joined on ValueID. So the the second table only has the data for the selected items and rather then a heap of empty boolean values in the main table.
因此,在ValueID上加入的主表和第二表之间存在一对多的关系。因此第二个表只包含所选项的数据,而不是主表中的一组空布尔值。
The reason I thought about doing it this way was it would allow me to add different values to be stored against the record in the future rather then changing the table structure.
我想这样做的原因是它允许我在将来添加不同的值以存储在记录中,而不是改变表结构。
Would this be a good way to store the lots of boolean values?
这是存储大量布尔值的好方法吗?
I would really like to be able to bind this to a form with check boxes , would going this way make it hard.
我真的希望能够将它绑定到带有复选框的表单,这会使这很难。
So if this is a bit hard to understand what I need, I don't really know how to explain it in text.
因此,如果这有点难以理解我需要什么,我真的不知道如何在文本中解释它。
Thanks.
3 个解决方案
#1
Leave it as it is!
保持原状!
The database will pack multiple bit fields very efficiently. I have seen people doing things like having a 32-bit int field, thus allowing them to store their 17 boolean values using bitmasking and 'leaving some room for additional fields'. This is just dumb, it is a maintenance nightmare and your queries become littered with bitmasks making them difficult to maintain.
数据库将非常有效地打包多个位字段。我见过人们做的事情就像拥有一个32位的int字段,因此允许他们使用bitmasking存储他们的17个布尔值,并“为其他字段留出一些空间”。这只是愚蠢的,这是一个维护噩梦,你的查询充斥着bitmasks使他们难以维护。
I will reiterate, keep it simple, just have the boolean columns. If you need a new column, then add it. You do not need to create a separate table.
我将重申,保持简单,只需要布尔列。如果您需要新列,请添加它。您无需创建单独的表。
#2
If you expect the number of booleans for a main record to change, I recommend that you go for the separation into two tables.
如果您希望主记录的布尔数量发生变化,我建议您分成两个表格。
Do not put the FK (ValueID) into the main table, but put the MainID into the second table instead.
不要将FK(ValueID)放入主表中,而是将MainID放入第二个表中。
Some things that are not clear from your question: If a value is not set, is this equivalent to a false value or do you have ternary logic with null values? If the latter is the case, you will have to put a boolean field into you second table. Otherwise the absence of a record in the second table is enough to give the logical false.
从您的问题中不清楚的一些事情:如果未设置值,这是否等于假值,或者您是否具有空值的三元逻辑?如果是后者,则必须在第二个表中放置一个布尔字段。否则,第二个表中缺少记录就足以使逻辑错误。
If the number of boolean attributes does not change, you could put them into a single integer field and mask them as Mike Chaliy suggests.
如果布尔属性的数量没有改变,你可以把它们放到一个整数字段中,并像Mike Chaliy建议的那样掩盖它们。
#3
Just an idea, you can use int to store bits, bitwise operation are efficient, however I am not sure how well SQL support them. In C# there are Bit Enums, so you can save its value directly.
只是一个想法,你可以使用int来存储位,按位操作是有效的,但我不确定SQL如何支持它们。在C#中有Bit Enums,因此您可以直接保存其值。
#1
Leave it as it is!
保持原状!
The database will pack multiple bit fields very efficiently. I have seen people doing things like having a 32-bit int field, thus allowing them to store their 17 boolean values using bitmasking and 'leaving some room for additional fields'. This is just dumb, it is a maintenance nightmare and your queries become littered with bitmasks making them difficult to maintain.
数据库将非常有效地打包多个位字段。我见过人们做的事情就像拥有一个32位的int字段,因此允许他们使用bitmasking存储他们的17个布尔值,并“为其他字段留出一些空间”。这只是愚蠢的,这是一个维护噩梦,你的查询充斥着bitmasks使他们难以维护。
I will reiterate, keep it simple, just have the boolean columns. If you need a new column, then add it. You do not need to create a separate table.
我将重申,保持简单,只需要布尔列。如果您需要新列,请添加它。您无需创建单独的表。
#2
If you expect the number of booleans for a main record to change, I recommend that you go for the separation into two tables.
如果您希望主记录的布尔数量发生变化,我建议您分成两个表格。
Do not put the FK (ValueID) into the main table, but put the MainID into the second table instead.
不要将FK(ValueID)放入主表中,而是将MainID放入第二个表中。
Some things that are not clear from your question: If a value is not set, is this equivalent to a false value or do you have ternary logic with null values? If the latter is the case, you will have to put a boolean field into you second table. Otherwise the absence of a record in the second table is enough to give the logical false.
从您的问题中不清楚的一些事情:如果未设置值,这是否等于假值,或者您是否具有空值的三元逻辑?如果是后者,则必须在第二个表中放置一个布尔字段。否则,第二个表中缺少记录就足以使逻辑错误。
If the number of boolean attributes does not change, you could put them into a single integer field and mask them as Mike Chaliy suggests.
如果布尔属性的数量没有改变,你可以把它们放到一个整数字段中,并像Mike Chaliy建议的那样掩盖它们。
#3
Just an idea, you can use int to store bits, bitwise operation are efficient, however I am not sure how well SQL support them. In C# there are Bit Enums, so you can save its value directly.
只是一个想法,你可以使用int来存储位,按位操作是有效的,但我不确定SQL如何支持它们。在C#中有Bit Enums,因此您可以直接保存其值。