实现一个仅包含两行 Female 和 Male 的单独表是一种不好的做法吗？
假设我们有一个包含 3 列的 Person 表：
PersonId类型为int。 （主键）名称，类型为字符串。int类型的GenderId（外键引用Gender表）。
Gender 表由 2 列组成：
GenderId类型为int。字符串类型的名称。
我的问题是： 是否值得实施Gender 表？或者它会导致性能下降？处理这个问题的最佳方法是什么？
编辑 1：
我必须在 UI 中使用固定性别（女性和男性）列表填充下拉控件。
Assume we have a Person table with 3 columns:
PersonIdof typeint. (Primary key)Nameof typestring.GenderIdof typeint(Foreign key referencingGendertable).
The Gender table consists of 2 columns:
GenderIdof typeint.Nameof typestring.
My question is:
Is it worth implementing the Gender table? Or it causes performance degradation? What is the best way to handle this?
Edit 1:
I have to populate a drop down control with a list of fixed genders (female and male) in my UI.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我认为在这种情况下最好的方法是妥协：
现在，您只需要从一张表中进行查询，但仍然可以通过外键防止数据不一致，并且如果需要，您可以从“性别”表中提取下拉列表的值。两全其美。
此外，它还让数据库工作人员的工作变得更加轻松，因为他们不需要记住您分配给男性/女性的任意 ID。
I think the best approach in this case is a compromise:
Now you only need to query from one table, but you're still protected from data inconsistencies by the foreign key, and you can pull the values for your dropdown from the Gender table if you want to. Best of both worlds.
Additionally, it makes life easier for someone working in the database, because they don't need to remember which arbitrary ids you've assigned to Male/Female.
如果您的字段只有两个可能的值，则不需要另一个表。您可以仅使用
BIT（0=男性，1=女性）或CHAR（“M”和“F”）之类的内容。If you have a field with only two possible values, you don't need another table for it. You can just use something like a
BIT(0=male, 1=female) or aCHAR('M' and 'F').我坚信查找表可以实现这一点——这本质上就是所提议的，但有一个区别：使用友好的非自动生成的PK。
例如，PK 可能是：“M”、“F”、“N”（并且可能有 2-4 行左右，具体取决于可接受的性别分类）。使用简单的 PK 可以轻松查询，同时仍然允许更高形式的规范化和引用一致性约束，而无需使用检查约束。
正如问题所提出的，我还使用了其他列，例如适当的名称/标题/标签（这些可用作参考并向身份添加自我文档）。麦卡锡主张使用这些数据本身作为 PK（这是一种选择），但我认为这是身份的一个特征，并使用更简洁的精心挑选的 PK。
从这个意义上说，我认为查找表的整个概念是为了提供与代码中“常量”相同的角色。
I am firm believe in lookup-tables for this -- which is essentially what is being proposed but with one distinction: use friendly non-auto-generated PKs.
For instance the PKs might be: "M", "F", "N" (and there might be 2-4 or so rows depending upon accepted gender classifications). Using a simple PK allows easy queries while still allowing a higher form of normalization and referential consistency constraints without having to employ check-constraints.
As the question proposes, I also employ additional columns, such as a Name/Title/Label as appropriate (these are useful as a reference and add self-documentation to the identities). McCarthy advocates using this data itself as the PK (which is one option), but I consider this a trait of the identity and use more terse hand-picked PK.
In this sense, I hold the entire concept of lookup-tables to provide the same sort of role as "constants" in code.
在这种情况下，将性别规范化到单独的表中是多余的。
为什么不在第一个表中将 GenderType 作为字符串呢？
这样您就不必生成和存储额外的 GenderID（尝试尽量减少 ID 的使用，否则表中的所有列都只是指向其他表......过度标准化）
Normalizing gender into a separate table is overkill in this instance.
Why not just have GenderType as a string in the first table?
That way you save having to generate and store an extra GenderID (try to minimise the use of IDs as otherwise all you'll have in a table is a whole lot of columns just pointing to other tables... over normalization)
添加其他人所说的内容，您还可以创建一个 INDEX ( PersonId, GenderId ) 来加快计算速度。
Adding to what other people are saying, you can also create an INDEX ( PersonId, GenderId ) to fasten up the calculations.
鉴于您只有两种可能的性别，并且将来极不可能需要更改，所以我不会费心去建立一个单独的表。只需向您的人员表添加一列即可。如果需要，联接可能会很高效，但它总是比没有联接慢。
如果出于某种原因，您觉得需要两种以上可能的性别，您仍然可以将它们存储在 Person 表的单个列中。
Given that you only have two possible genders, and that this is extremely unlikely to need to change in the future, I would not bother to have a separate table. Just add a column to your Person table. A join can be efficient if needed, but it is always slower than no join.
And if, for whatever reason, you feel the need for more than two possible genders, you can still store them in a single column in the Person table.