6 ms·

It's way better to just use a DBMS that supports enums. I know SQL server isn't one of those but I still don't store my coded values as strings.

andy81 7mo ago
The way to do enums in SQL (generally, not just MSSQL) is another table. It's better that they don't offer several ways to do the same thing.
- SigmundA 7mo ago
  Mostly agree separate tables can have multiple attributes besides a text description and can be exposed for modification to the application easily so users or administrators can add and modify codes. A common extra attribute for a coded value is something for deprecation / soft delete, so that it can be marked as no longer valid for future data but existing data can remain with that code, also date ranges its valid for etc, also parent child code relationships. Enums would be a good feature but they have a much more limited use case for static values you know ahead of time that will have no other attributes and values cannot be removed even if never used or old data migrated to new values. Common real world codes like US postal state can take advantage of there being agreed upon codes such as 'NY' and 'New York'.
- sgarland 7mo ago
  While I generally would prefer lookup tables, it's much easier to sell dev teams on "it looks and acts like a string - you don't have to change anything."
SigmundA 7mo ago
How do you store them? Also enums are not user configurable normally. It would be a good feature to have them, but they don't work well in many cases. Typical code tables with code, description and anything else needed for that value which the user can configure in the app. Sure you can use integers instead of codes, now all your results look like 1, 2, 3, 4 for all your coded columns when trying to debug or write ad-hoc stuff. Also ints are not variable length so your wasting space for short codes and you have to know ahead time if its only going to be 1,2,4 or 8 bytes.
- wvenable 7mo ago
  Enums are for non user-configurable values. For configurable values, obviously you use a table. But those should have an auto-integer primary key and if you need the description, join for it. Ints are by far more the efficient way to store and query these values -- the length of the string is stored as an int and variable length values really complicate storage and access. If you think strings save space or time that is not right.
  - SigmundA 7mo ago
    >Enums are for non user-configurable values In the systems I work with most coded values are user configurable. >But those should have an auto-integer primary key and if you need the description, join for it. Not ergonomic now when querying data or debugging things like postal state are 11 instead of 'NY' select * from addresses where state = 11, no thanks. Your whole results set becomes a bunch of ints that can be easily transposed causing silly errors. Of course I have seen systems that use guids to avoid collision, boy is that fun, just use varchar or char if your penny pinching and ok with fixed sizes. >the length of the string is stored as an int No it's stored as a smallint 2 bytes. So a single character code is 3 bytes rather than a 4 byte int. 2 chars is the same as an int. They do not complicate storage access in any meaningful way. You could use smallint or tinyint for your primary key and I could use char(2) and char(1) and get readable codes if I wanted to really save space.
    - simonask 7mo ago
      Please take literally one course. Do NOT use mnemonics as primary keys. It WILL bite you.
      - SigmundA 7mo ago
        https://en.wikipedia.org/wiki/Natural_key https://en.wikipedia.org/wiki/Natural_key you should have learned learn this in your courses. Clam down, I am not suggesting using this for actual domain entity keys, these are used in place of enums and have their advantages. I have doing this a long time and it has not bit me, I have also seen many other system designed this way as well working just fine. Using an incrementing surrogate key for say postal state code serves no purpose other than making things harder to use and debug. Most systems have many code values such as this and using surrogate key would lead to a bunch of overlapping hard to distinguish int data that leads to all sorts of issues.

andy81 7mo ago

The way to do enums in SQL (generally, not just MSSQL) is another table. It's better that they don't offer several ways to do the same thing.

SigmundA 7mo ago
Mostly agree separate tables can have multiple attributes besides a text description and can be exposed for modification to the application easily so users or administrators can add and modify codes. A common extra attribute for a coded value is something for deprecation / soft delete, so that it can be marked as no longer valid for future data but existing data can remain with that code, also date ranges its valid for etc, also parent child code relationships. Enums would be a good feature but they have a much more limited use case for static values you know ahead of time that will have no other attributes and values cannot be removed even if never used or old data migrated to new values. Common real world codes like US postal state can take advantage of there being agreed upon codes such as 'NY' and 'New York'.
sgarland 7mo ago
While I generally would prefer lookup tables, it's much easier to sell dev teams on "it looks and acts like a string - you don't have to change anything."

SigmundA 7mo ago

How do you store them? Also enums are not user configurable normally. It would be a good feature to have them, but they don't work well in many cases. Typical code tables with code, description and anything else needed for that value which the user can configure in the app. Sure you can use integers instead of codes, now all your results look like 1, 2, 3, 4 for all your coded columns when trying to debug or write ad-hoc stuff. Also ints are not variable length so your wasting space for short codes and you have to know ahead time if its only going to be 1,2,4 or 8 bytes.

wvenable 7mo ago
Enums are for non user-configurable values. For configurable values, obviously you use a table. But those should have an auto-integer primary key and if you need the description, join for it. Ints are by far more the efficient way to store and query these values -- the length of the string is stored as an int and variable length values really complicate storage and access. If you think strings save space or time that is not right.
- SigmundA 7mo ago
  >Enums are for non user-configurable values In the systems I work with most coded values are user configurable. >But those should have an auto-integer primary key and if you need the description, join for it. Not ergonomic now when querying data or debugging things like postal state are 11 instead of 'NY' select * from addresses where state = 11, no thanks. Your whole results set becomes a bunch of ints that can be easily transposed causing silly errors. Of course I have seen systems that use guids to avoid collision, boy is that fun, just use varchar or char if your penny pinching and ok with fixed sizes. >the length of the string is stored as an int No it's stored as a smallint 2 bytes. So a single character code is 3 bytes rather than a 4 byte int. 2 chars is the same as an int. They do not complicate storage access in any meaningful way. You could use smallint or tinyint for your primary key and I could use char(2) and char(1) and get readable codes if I wanted to really save space.
  - simonask 7mo ago
    Please take literally one course. Do NOT use mnemonics as primary keys. It WILL bite you.
    - SigmundA 7mo ago
      https://en.wikipedia.org/wiki/Natural_key https://en.wikipedia.org/wiki/Natural_key you should have learned learn this in your courses. Clam down, I am not suggesting using this for actual domain entity keys, these are used in place of enums and have their advantages. I have doing this a long time and it has not bit me, I have also seen many other system designed this way as well working just fine. Using an incrementing surrogate key for say postal state code serves no purpose other than making things harder to use and debug. Most systems have many code values such as this and using surrogate key would lead to a bunch of overlapping hard to distinguish int data that leads to all sorts of issues.