到“查找表” 或不？
我目前正在为一个项目设计一个数据库。 现在我正在与自己争论是否必须创建一个查找表，例如“公民身份”数据，该数据只能包含固定值，如单身、已婚、分居、寡妇/鳏夫。 我很确定将来不会添加其他值。 我应该将它们放在单独的表中还是只是将这些值硬编码到程序代码上？
I'm currently designing a database for a project. Now I'm debating with myself whether I have to create a look-up table for example 'civil status' data which can only contain fixed values like Single, Married, Separated, Widow/Widower. I'm pretty sure that no other values will be added in the future. Should I put these on a separate table or just hardcode the values on the program code?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
对它们进行硬编码有什么好处？ 给定一个现代 ORM 系统，从数据库中读取这些值并使用它们不是很容易吗？ 如果是这样，那么我没有看到硬编码的好处，而缺点是：
“我认为他们不会改变”几乎与“他们无法改变”不同。 如果它们无法改变（如“真/假”），那就没问题。 如果你不认为他们会改变，但不会改变太多。
What's the benefit of hard coding them? Given a modern ORM system, isn't it really easy to read these values out of the DB and use them? If so, then I'm not seeing the benefits of hard-coding when the cons are:
"I can't think that they're going to change" is almost never the same as "They can't change". If they can't change, as in True/False, then fine. If you don't think they'll change, not so much.
规范化说：查找表
常识说：查找表
“民事结合”的概念不是自然法，而是民法问题 - 并且可以改变。
如果您需要根据计划中的民事结合状态做一些事情，安德鲁·凯南 (Andrew Kennan) 的方法是一个不错的方法。 否则，仅查找表就足够了
normalization says: lookup table
common sense says: lookup table
the notion of "civil union" is not a natural law, it is a matter of civil law - and that can change.
If you need to do something based on civil union status in the program, Andrew Kennan's approach is a good one. Otherwise the lookup table alone is sufficient
根据您的编程语言，将它们设置为常量或枚举。 没有理由将它们放入数据库中，因为正如您所说，它们可能不会改变。
如果进入数据库，下一步是什么？ 男/女？ 真假？ :)
我主要使用 Java，所有这些项目都作为枚举。 由于我们的数据库是 MySQL，因此我们也将列类型创建为枚举。 那里的比赛也很好。
华泰
Depending on your programming language, make them constants or enums. There is no reason to put those in the database because, like you said, they will likely not change.
If the go in the database, what's next? Male/Female? True/False? :)
I'm using Java primarily and all of those items go as enums. With our database being MySQL, we then create the column type as enum as well. Nice even match there.
HTH
您的表中应该有一个 CivilStatus (int) 列，它是对包含所有可能的公民身份的 CivilStatuses 表的外键引用。
即使您不希望它发生变化，我仍然会从数据库中查询它。 这将使未来的其他事情变得更容易，例如本地化/全球化。
然后，正如其他人所说，您可以在代码中使用枚举，以使映射更容易。 但是，任何时候向用户显示文本时，它都必须来自数据库或资源文件，而不是直接来自代码。
You should have a column in your table for CivilStatus (int) which is foreign key reference to the CivilStatuses table which contains all possible civil statuses.
Even though you don't expect it to change, I'd still query it from the database. This will make other things easier in the future like localization/globalization.
You could then have enumerations in your code, as others have said, to make the mapping easier. However, any time you display text to the user, it's got to come from the database or a resource file--not from the code directly.
我倾向于两者都做 - 使用查找表来强制引用完整性并提供枚举以使代码可读。
缺点是，如果由于某种原因查找值确实发生了变化，您需要记住更新这两个位置。
I would tend to do both - use a lookup table to enforce referential integrity and provide an enum to make the code readable.
The downside is that if for some reason the lookup values do change you'll need to remember to update both locations.
查找表。 为什么？
1) 强制数据完整性。 至少在列上放置一个检查约束。
2) 本地化
3) 查询问题。 有时数据库区分大小写。
嗯，我不知道我最好把 ToLower(MarriageStatus) = "single"。
哎呀，我不知道将函数包装在列上会阻止它使用索引:)
为自己节省一些麻烦并使用查找表，除非这是一个预期寿命较低的小项目。
Lookup Table. Why?
1) Enforces Data Integrity. At least put a check constraint on the column.
2) Localization
3) Query issues. Sometimes databases are case sensitive.
Hmm, I don't know I better put ToLower(MarriageStatus) = "single".
Oops, I didn't know wrapping a function around a column prevents it from using an index :)
Save yourself some trouble and use a lookup table unless this is a small project with a low life expectancy.