查找表最佳实践：数据库表...或枚举
如果我们必须存储公司的可用职位（即经理、团队负责人等）。 存储它的最佳做法是什么？ 我有两个意见和评论......“当然，欢迎你的”
- 将其存储为包含 ID 和 Name 列的数据库表，并使用查询和联接对其进行处理。
- 将其存储为枚举并忘记数据库表。
我认为，如果我有更改的项目，我会选择第一个解决方案。 这样我就不会将这些选项硬编码为枚举。
如果我确信数据不会改变（例如，性别：男、女），我可能会选择 Enum 解决方案。
注意：我用英语编码，UI 文化可能是阿拉伯语。 如果我使用枚举解决方案，我将在表示层中对基于区域性的字符串进行硬编码，从最佳实践的角度来看可以吗！！！
我想知道您的意见以及我的想法是否符合最推荐的“最佳实践”？
If we have to store the available positions at a company (i.e. Manager, Team Lead, ... etc). What are the best practices for storing it? I have two opinions with comments... "sure, welcoming yours"
- Storing it as DB table with columns ID and Name, and deal with it using queries and joins.
- Storing it as Enum and forget about the DB table.
In my opinion, I will choose the first solution if I have changing items. So that I won't hard code these options as Enum.
I may choose the Enum solution, if I have no doubt that data won't change (for example, Gender: Male, Female).
NOTE: I code in English, and the UI Culture may be Arabic. If I will work with the Enum Solution, I will hard code the culture-based strings in the presentation layer, is it okay from the best practices perspective!!!!
I would like to know your opinions and if my thoughts correspond to what is most recommended "Best Practices"??
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（9）
一般来说，您应该只在有一组明确的不会改变的项目的情况下使用枚举，例如原色或大陆名称。 否则，具有适当实现的外键的查找表几乎总是最佳选择。
查找表选项可能有一个变化，您可能有大量用于简单 ID/值关系的查找表。 域/查找表对可以显着减少所需的表数量，尽管会带来一些额外的编码复杂性。 在这种情况下，您将有一个域表
和一个键/值表
因此，将在与您将使用的每个查找表相对应的域表中添加一行，并将所有（键域）/值对添加到值中桌子。 除了简化数据库结构之外，这种方法还具有可以在应用程序代码中动态创建“查找表”的优点，这在某些应用程序中非常有用。
Generally you should only use enumeration where there is a clear set of items that will not change, e.g. primary colours, or continent names. Otherwise lookup tables with appropriately implemented foreign keys are pretty much always the best option.
There is a possible variation on the lookup table option where you potentially have a large number of lookup tables for simple id/value relationships. A domain/lookup table pair can dramatically reduce this the number of tables required, albeit with some additional coding complexity. In this case you'd have a domain table
and a key/value table
Hence a row is added to the Domain table corresponding to each lookup table that you would otherwise use, and all (key-domain)/value pairs added to the value table. Apart from simplifying the database structure this approach also has the advantage that 'lookup tables' can be created in the application code dynamically, which in some applications can be extremely useful.
选项 1：将其存储为包含 ID 和 Name 列的数据库表，并使用查询和联接处理它是您至少应该做的事情。
来自“五个简单您应该避免的数据库设计错误”：
推荐的最佳实践是实现数据库，就像它对用户界面一无所知一样。 也就是说，数据库应该执行与数据相关的所有规则； 在这种情况下，数据库应该强制执行适当的值。 要枚举合适的值，可以使用 查找表。 很多时候，应用程序来了又去，但数据库仍然存在并被重用。
如果您想在应用程序中强制枚举，您当然可以这样做，但无论您做什么，请确保数据库履行其作为数据库的职责并维护数据的完整性。 如果困难，就克服困难； 你正在奠定基础。 在“数据库设计和比萨斜塔”，作者强调了为什么在数据库中正确奠定基础如此重要。
我希望这有帮助。
Option 1: Storing it as DB table with columns ID and Name, and deal with it using queries and joins is the minimum that you should do.
From "Five Simple Database Design Errors You Should Avoid":
The recommended best practice is to implement your database as if it knows nothing about the user interface. That is, the database should enforce all rules pertaining to the data; in this case, the database should be enforcing what values are appropriate. To enumerate what values are appropriate, a lookup table for each type of lookup value is usually the way to go. Many times, applications come and go, yet the database remains and is reused.
If you want to enforce enumerations in the application, you can certainly do so, but whatever you do, make sure the database does its job as a database and maintains the integrity of the data. If it's troublesome, go through the trouble; you are laying the groundwork. In "Database Design and the Leaning Tower of Pisa," the writer emphasizes why laying the groundwork properly in a database is so very important.
I hope this helps.
我总是选择数据库选项，因为它有几个优点，主要优点之一是您可以更改查找列表中项目的名称/描述，而无需更改任何代码。 还同意使用一个表而不是许多小表，这样您就可以编写一个例程来从数据库中检索值，从而减少维护成本。 拥有单一的例程意味着您可以投入更多的精力来使其表现良好。
根据上面 Cruachan 的回复，如果您有父子关系，其中没有父项的行描述域，那么您可以使用一张表。 属于某个域的所有记录都将域行作为其父记录。
例如，货币列表可以包含：
I always go for the database option as that has several advantages, one of the main ones being you can change the names/descriptions of items in the look-up lists without having to change any code. Also agree with having a single table as opposed to lots of little tables, that way you code one single routine to retrieve the values from the database which cuts down on maintenance costs. Having a single routine means you can invest more effort in making it perform well.
Further to Cruachan's reply above, you can get away with one table if you have a parent-child relationship where the rows with no parent describe the domain. All the records belonging to a domain have the domain row as their parent.
So for example a list of currencies could contain:
我倾向于选择数据库选项，因为这样可以轻松查询有意义的数据（即名称而不是 ID）。
如果我相当有信心这些值不会改变，那么我将在应用程序中枚举它们，并且当您不必记住项目的 ID 时，这会使开发变得更加容易，并且还使代码更具可读性。
这种方法允许我选择是否在查询中包含查找表。 例如，我会将其包含在报告查询中，我想在其中显示查找值，但如果我可以从枚举中推断出它，则在我的应用程序中加载数据时可能不会包含它。
显然，如果值可能发生更改或修改，则可能无法进行枚举。
只有你可以判断 UI 文化的影响，我 100% 确定我的用户的文化，所以不必太担心:)。
I tend to go for the database option as this enables easy querying with meaningful data (i.e. names rather than ID's).
If I am fairly confident that the values will not change then I will enumerate them in the application as wellas it makes development much easier when you do not have to remember the ID of an item and also makes the code much more readable.
This approach allows me to choose whether to include the lookup table in queries or not. For example I would include it in a report query where I want to display the lookup value but may not include it when loading data in my application if I can infer it from the enumeration instead.
Obviously if the values are subject to change or modification enumeration may not be possible.
Only you can judge the impact of UI culture, I am 100% certain of the culture of my users so do not have to worry about it too much :).
您唯一计划将雇主列表放入数据库吗？
如果是，请将其放入文件中，然后完成。
数据库很棒，但如果您需要一个简单的键值，那么它们就太过分了。 他们给你很多东西。 但它们也在幕后做了很多事情，它们是您需要支持的另一件事...如果您可以使用包含 20 个制表符分隔行的单个文件，那就简单一点。
如果您有很多内容需要此类查找，或者您认为在客户端尝试读取它们时可能需要更新它们，或者您可能希望稍后交叉引用内容 - 那么请选择数据库。
Is the only thing you are planning on putting into a db this list of employers?
If yes, drop it in a file, and be done with it.
DBs are great, but if you need a simple key-value, they are overkill. They give you a lot of stuff. But they also do a lot of stuff behind the scenes, they are one more thing you need to support... if you can get away with a single file with 20 tab-delimited lines, go with simplicity.
If you have a lot of things that require these kinds of lookups, or if you think you might need to update them while a client is trying to read them, or if you might want to cross-reference things later on -- then go for the DB.
查找、查找、查找（此处插入猴子跳舞视频）
我个人的“最佳实践”是将所有内容都存储在数据库中。 公司的职位绝对是一个查找表。
翻译也是如此，这可能有点棘手，但通常将表与翻译表连接起来的视图定义是一个好的开始。
此外，如果Position只是一个int值，并且您的应用程序是单语言的，您可以将职位标题直接添加到数据库中。
Lookups, Lookups, Lookups (insert monkey-dancing video here)
My personal "best practice" is to have everything in the database. Positions in a company is definitely a lookup table.
The same goes for translations, which may be a bit trickier, but generally a view definition joining the table with a translation table is a good start.
Besides, if the Position is only an int value, and your app is single-language, you can add the position titles directly to the database.
我几乎总是把这种东西放在桌子上。 如果需要很多，我会缓存它。 如果我需要以编程方式引用其中的一些，我将创建一个枚举，其值设置为查找项的键值。
例如，代理主键（通常这样我的数据访问层可以无缝地添加/更新它），项目值的字段，加上“候选”（候选在引号中，因为它不是必需的，所以它是唯一的或为空）。 这样，我可以使用引号中的枚举来引用表中的特定/重要项目，但仍然可以灵活地让最终用户添加新项目。 使用的数据模型的具体细节实际上取决于您的偏好（有些人现在可能会因为我不只是使用“候选”字段作为真正的主键而对我大喊大叫）。
I tend to almost always put this kind of thing in a table. If it's needed a lot I'll cache it. If I need to programmatically refer to some of them I'll then create an enum which has its values set to the key value of the lookup item.
For example, a surrogate primary key (usually so my data access layer can seemlessly add/update it), a field for the value of the item, plus a "candidate" (candidate being in quotes since it's not required, so it's either unique or null). That way I can use the enum in quotes to refer to specific/important items in the table, but still have the flexibility of letting end users add new items. The specifics of the data model used really depend on your preference (some people may be screaming at me right now for not just using the "candidate" field as the true primary key).
我一般都比较喜欢两者。 我在 dal 层中使用表，因为它们可用于诸如用于报告、SQL 查询和其他目的的 tableau 等工具。
我在前端和业务层使用枚举，因为使用它们进行开发很容易，并且您不必从 dal 中获取列表，也不必记住表中的值。
如果表和枚举中的列表相同，C# 会自动将枚举转换为相应的 ID。
在 MVC 中，您可以在视图中使用带有枚举的下拉列表作为 @Html.EnumDropdownListFor() ，这样您就不必为下拉列表点击数据库本身。
I generally prefer both. I use table in the dal layer as they can be used for tools like tableau for reporting, SQL queries and other purpose.
I use enums in the front end and the business layer because its easy to develop with them and you dont have to get the list from the dal neither you have to remember the values from the table.
C# automatically convert the enums to corresponding ID for you if the list is same in the table and the enum.
In MVC you can use the dropdown with the enums as @Html.EnumDropdownListFor() in the View so you dont have to the hit the database itself for dropdowns.
如果你能让它们保持同步，我认为没有太多理由不两者兼而有之。 当我使用数据库存储开发一些状态机功能时，在 IDE 中枚举可用对象状态使事情变得更加容易。
不久前，我自己编写了一个 EnumGenerator Visual Studio Addin，它根据您在 IDE 中选择的 DB Tablet 的 ID 和 DESC 列，用 C# 创建了一个枚举代码片段。 我还没有发布它，因为这是我第一次使用 VS Addins，而且它非常垃圾，但我敢打赌，具有插件/代码生成经验的人可以接受这个想法并运行它。
If you can keep them synchronized I don't think there is much of a reason not to have both. When I was developing some state-machine functionality with a DB store it made things a lot easier to have an enumeration of the available object states within my IDE.
I wrote myself a little EnumGenerator Visual Studio Addin a while back that creates an Enumeration code snippet in C# from a the ID and DESC columns of a DB Tablet you select from right within the IDE. I haven't released it because it was the first time I'd worked with VS Addins and it was pretty junky but I bet somebody with addin/code generation experience could take the idea and run with it.