Menu
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)?
665
There is no enum datatype available in SQL Server like in MySQL. But using the CHECK constraint enum functionality can be implemented.
The ENUM data type in MySQL is a string object. It allows us to limit the value chosen from a list of permitted values in the column specification at the time of table creation. It is short for enumeration, which means that each column may have one of the specified possible values.
By default, when an enum is a part of an entity, JPA maps its values into numbers using the ordinal() method. What it means is that without customizations JPA stores enum value as numbers. These numbers are associated with the order in which you define values in the enum.
Enumerated (enum) types are data types that comprise a static, ordered set of values. They are equivalent to the enum types supported in a number of programming languages. An example of an enum type might be the days of the week, or a set of status values for a piece of data.
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.
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
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.
107
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
