SQL Server数据库中的枚举 [英] enums in SQL Server database
问题描述
除了简单地为每个数据库（特别是Id，代码和名称作为列）创建查找表之外，是否存在更好或更简单的方法来将枚举（在C＃等编程语言中可用的枚举）存储在SQL Server数据库中？每个表中只有很少的行时）？我发现了文章建议为所有枚举只创建一个查询表，并且有人批评这种方法违反了参考数据的完整性。如果枚举仅由一个表使用，那么使用一些预定义的代码然后为它们添加约束（也许使用扩展属性）是一种好习惯吗？
Is there a better or easier way to store the enums (Enumerations available in programming languages like C#) in SQL Server database other than simply creating a lookup table (with Id, code and name as columns) for each of them (especially when there are very few rows in each of those tables)? I found an article that suggests creating just one lookup table for all enumerations and the approach is criticised by some people in comments saying it violates referential data integrity. if at all the enumeration is used by only one table, is it a good practice to use some predefined codes and then add a constraint for them (may be using extended properties)?
推荐答案
-
我个人希望为每个枚举定义一个查找表，因为它也是一种文档。如果有人想知道id代表什么，他会在表中轻松找到它。在列约束中查找该信息并不明显。
Personally, I like to define one lookup table per enum, because it is a kind of documentation as well. If someone wants to know what an id stands for, he would find it easily in a table. Looking for this information in a column constraint is not evident.
在表中添加新值比在约束中更容易。
It is also much easier to add new values in a table than in a constraint.
如果创建数据库图，则单个查找表显得更合逻辑。
If you create a database diagram, individual lookup tables appear more logical.
您可以添加除ID和文本外，还向个人查找表提供了其他信息（如果需要）（例如注释，排序列，某种标记等）。
You can add additional information to individual lookup tables besides id and text, if required (like a comment, a sort column, some sort of flag etc.).
您已经说过，最好是参照完整性
And as you have said, it is better for referential integrity
如果您以代码优先方式使用o / r-mapper，那么使用编程语言提供的枚举会很自然。这是因为您不是在设计数据库，而是在设计对象模型。 o / r-mapper为您自动创建数据库。
However; if you are using an o/r-mapper with the code-first approach, using enums provided by the programming language feels quite natural. This is because you are not designing a database but an object model. The o/r-mapper creates the database automatically for you.
这篇关于SQL Server数据库中的枚举的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
