Coffeehouse Thread

9 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Enums in Databases

Back to Forum: Coffeehouse
  • User profile image
    W3bbo

    Turrican's being recreating some famous Holy Wars in CS/SE lately, like Obfuscation; so I thought I'd raise one of my own:

     

    What's your solution for working with enumerations in databases?

     

    Back in ye olden days before enumerations it was simple enough to have an 'int' column that stores the values of SOME_FIELD_CONSTANT which worked well enough, but of course makes looking at the raw data painful unless you define the enumeration in its own table somewhere.

     

    So basically I see two main approaches:

     

    Give enumeration values constant values in the programming code then store the values (not the names) in the database column. For data integrity you could add an enumeration table, but the drawback is ensuring the enumeration table maps with the code enumeration.

     

    Alternatively, store the names in the database field; this has the advantage of not requiring the numeric values and lets you re-order enumerations at will, but then you can't easily rename or change enumerations, and this makes it easier for data corruption to occur.

     

    I've heard that some DBMS have built-in support for enums, and then of course you can just use validation triggers, but I'd like to hear what you've done for any large-scale systems you've built.

  • User profile image
    SlackmasterK

    One can get around the code/database matching constraint with bidirectional integration tests that employ Enum.Parse. 

     

    Another downside to storing in the db is that you have to know the int value of your enum.

  • User profile image
    blowdart

    SlackmasterK said:

    One can get around the code/database matching constraint with bidirectional integration tests that employ Enum.Parse. 

     

    Another downside to storing in the db is that you have to know the int value of your enum.

    Or of course you have a custom T4 template or MSBuild task that generates the enums from the data in the database, thus keeping everything in sync.

  • User profile image
    iBeer

    oops, double post.

  • User profile image
    iBeer

    http://weblog.dangertree.net/2007/09/23/mapping-java-5-enums-with-hibernate/

     

    "A Simple Example

    I have a Beer object that corresponds directly to a BEER table in a database. The Beer object can wither be an “Ale” or a “Lager”, and there may be more types in the future. So the database schema looks like this:"

     

    You may want to take a look at how Hibernate maps objects to JDBC and others.

    While Hibernate mapping may be the worst way to provide optimized interfaces to data, it does make things easier for some Java programmers. You could also use Spring and XML enums->object over Hibernate, to make things even more spicy.

  • User profile image
    Ion Todirel

    why do you feel the need to "strongly type" it? I think it's wrong, just store integers which maps directly to a C# enum, and do the mappings in the DAL

  • User profile image
    Maddus Mattus

    It all depends.

     

    It's a question of data integrety. Do you want it to be checked in the database aswell?

     

    Is your application the only gateway to the data?

     

    If so, then leave it out if you want. If not so, put it in!

  • User profile image
    Dr Herbie

    Maddus Mattus said:

    It all depends.

     

    It's a question of data integrety. Do you want it to be checked in the database aswell?

     

    Is your application the only gateway to the data?

     

    If so, then leave it out if you want. If not so, put it in!

    I prefer to explicitly list them in the DB, as the data may be used in other reporting systems (not uncommon with our customers).  

    The only problem I have with this is the use of 'magic numbers' in stored procedures when devs forget to comment what the magic numbers mean; "SELECT * FROM SALES WHERE SALE_TYPE_ENUM = 3"  Wha??

     

    The company I work for has a schema standard where enum tables end in '_enums', the primary key column is NOT an identity column but is set so that it matches the enum value and the primary key column name ends in '_enum'.

    I like this standard as it makes it very obvious which tables are read-only enums and which are user editable reference data.

     

    Herbie

     

  • User profile image
    vesuvius

    There are some niners that make me very suspicious, but you cannot come out and say look... are you may2018 (or whatever the fiends name was)?, at the risk of offending a genuine niner - not implying that that turrican is may2018, but there are people that worry me.

     

    If you look at the responses in that "Holy War" thread, there are some exceedingly suspicious niners, and I have to say that they are right.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.