Data Modeling and Enums

How should enums be modeled in your database? Static Lookup Table? Or should they be left out of the data model and contained only in the object model? Specifically, I'm thinking about an enum that is represented in the UI layer by something like a combo or list box.

Rather than just coming to a decision on my own - I thought I would open it up to my readers (both of you..heh) to give me your opinions on how enums should be modeled in the database. I'm guessing that there are a couple of different ways of thinking on this that both have convincing arguments.

10 Comments

  • Don't know if this is the best approach, but I create a table for semi static data. Enums that don't change, I code them.

  • Great question. Do I give up Intellicrack and put the list in the database? Do I give up runtime flexibility and put the list in the code? Do I force the user to submit a help ticket to add another item on the list?



    I think the practical question must fall in the functional zone. If the application benefits from adding to that enumeration at any time, without delay, by non-coders, that list had better be in the database. If you can't answer the preceding question, maybe you'd better put that list in the database.



    If you are enumerating the strings "True" and "False", your coding language of choice probably has that covered already. If you are enumerating the datatypes, likewise. If you are enumerating something that will allow your application to operate without a database connection, maybe that's a good thing. If you are a paranoid coder looking to enhance your job security, look for every opportunity to turn data into hard code and resource files!



    Now you know why I think that's a great question! (Haha)



    -- Walter Lounsbery

  • I think it really depends on if that data will change or not, but quite honestly, for consistency we create tables for each enum if it will fill a dropdownlist or whatever. That said, I think an exception to that rule is for enums that will change very often. I guess I would say those should not be in the DB. So I guess I'm back to the decision being dependent on what you want to do with it! :P

  • Thanks for the feedback so far. The type of stuff I've had a had time deciding on before has been stuff like a list of databases an application supports for a feature like setting up a connection string in an admin-type tool. Maybe at some point in the future you'll support another database or OLE DB data source and so you'll need to add that to the enum in your code library as well as add a row to your table for the list of supported databases/data sources.



    The other thing I've often found to be painful is ensuring that your enum values and the ID values for the primary key field in the DB table match up. If they somehow get messed up during setup or whatever, you have to reseed the value or reset the sequence (in Oracle) before inserting the rows into the table that map to your enum values.

  • If this "enum" that you're talking about changes, then its not an enum. Its a lookup, and should never be an enum. Use a simple list for



    This is my beef with enums, they should never have allowed you to easily convert to an int.



    And for anybody adding their enums to databases: you should ALWAYS explicitly set each enum's values. And dont let the database (via autoincrement identity or something) dictate that value... thats a great recipe for disaster.

  • No mention of localization yet, if that's even a requirement. If your enums will have user-friendly strings for presentation in the UI layer, then you have to also consider where/how you'll store the localized translations of said enums. That typically points to either some kind of resource file lookup, or possibly a database table in which you may have to keep adding columns to contain each language translation.

  • Enums definitely map to int, even more as using [Flags] attribute.

  • Putting enums in code mean that the code (and thus the enums) can be stored in a version control system.

    I had the situation once that a value went "missing" from a production system in an "enum table" (i.e. a small table which never changed which just stored possible values for some field). The row just got deleted, and no one knew why or who had done it. Thus the software stopped working, as the software's code assumed that this entry would exist in this table.

    With a version control system, this sort of thing is much less likely to happen. And if it does happen you can find out why and who did it. So I prefer to put configuration which the user or admin shouldn't be able to change on a whim in files under version control.

  • This post is really very nice as it is well written and has provided many useful particulars and information about the topic. I am waiting for more such posts made by you in future!

  • This blog is no doubt awesome and also factual. I have discovered many useful tips out of this blog. I'd love to come back every once in a while. Cheers!

    PoIuYt

Comments have been disabled for this content.