查找表最佳实践:数据库表...或枚举 [英] Lookup Tables Best Practices: DB Tables... or Enumerations

问题描述

如果我们必须将可用职位存储在公司(即经理，团队负责人等)中.存储它的最佳实践是什么?我有两种意见，有评论...确定，欢迎您"

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"

1. 将其存储为具有ID和Name列的数据库表，并使用查询和联接对其进行处理.
2. 将其存储为Enum，而忘了DB表.

1. Storing it as DB table with columns ID and Name, and deal with it using queries and joins.
2. Storing it as Enum and forget about the DB table.

我认为，如果我要更改项目，则将选择第一个解决方案.这样我就不会将这些选项硬编码为Enum.
如果我毫无疑问数据不会更改(例如，性别:男，女)，则可以选择Enum解决方案.

注意:我用英语编码，UI文化可能是阿拉伯语.如果我将使用枚举解决方案，那么我将在表示层中对基于文化的字符串进行硬编码，从最佳实践的角度来看还可以！！！

我想知道您的意见，以及我的想法是否与最推荐的最佳做法"相对应?

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"??

推荐答案

通常，您仅应在存在明确的不会改变的项目集的情况下使用枚举，例如原色或大洲名称.否则，具有适当实现的外键的查找表几乎总是最佳选择.

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.

查找表选项可能有所不同，您可能拥有大量用于简单ID/值关系的查找表.域/查找表对可以显着减少所需的表数量，尽管会增加一些编码复杂性.在这种情况下，您将拥有一个域表

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

DomainID int identity
Domain   varchar(255)

和键/值表

DomainID int
ID       int identity
Value    varchar(255)

因此，将在域表中添加一行，该行与您将要使用的每个查找表相​​对应，并且所有(键域)/值对都添加到值表中.除了简化数据库结构之外，此方法还具有可以在应用程序代码中动态创建查找表"的优点，这在某些应用程序中可能非常有用.

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.

这篇关于查找表最佳实践:数据库表...或枚举的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！