locked
Sql table design question RRS feed

  • Question

  • User1100692814 posted

    Hi all

    Just trying to figure out what is the best use here: I have the following and would like to know whether you would store these as SQL table records or have them as simple enums?

    ======================
    Relationship Types
    ======================

    Grandparent = 1
    Father = 2
    Mother = 3
    Son = 4
    Daughter = 5
    Brother = 6
    Sister = 7
    Cousin = 8

    ======================
    Address Types
    ======================

    Home = 1
    Work = 2
    Other = 3

    ======================
    Marital Statuses
    ======================

    Single = 1
    Engaged = 2
    Married = 3
    Divorced = 4
    CoHabiting = 5
    Separated = 6
    Widowed = 7

    Thanks in advance.

    Dave

    Friday, January 15, 2010 11:11 AM

Answers

  • User187056398 posted

    I usually do both (enums and tables) and have never found a way to automatically keep them in sync. I add comments to the enums referencing the database tables they must match.

    The reason for having them in the tables is so reports look correct ie 'Daughter' instead of 5.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 15, 2010 11:19 AM

All replies

  • User187056398 posted

    I usually do both (enums and tables) and have never found a way to automatically keep them in sync. I add comments to the enums referencing the database tables they must match.

    The reason for having them in the tables is so reports look correct ie 'Daughter' instead of 5.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, January 15, 2010 11:19 AM
  • User437720957 posted

    I'd say that enums are enough. The number of relationship types etc. are mostly static, so having them stored in a dynamic store such as a table is not really needed. You could use tables if you want to attach more information to the types, such as translations, sort values etc.

    Friday, January 15, 2010 4:35 PM
  • User-821857111 posted

    I tend towards keeping this kind of thing in the database. I've been frustrated in the past when I've wanted to query a database externally to the application that primarily uses it, and have to dig around in the source code of the app to find constants declarations to understand what the flipping numbers mean, or which numbers I need to use as filters.

    Generally, if the magic numbers refer to data in the database, that's where I would store it. If they mean more to the code, then they become part of the code.


    Friday, January 15, 2010 6:02 PM
  • User187056398 posted

    One other advantage to having them in the database (in addition to being able to provide meaningful reports and query results) is that it's easy to fill listboxes direct from the database.  And to update the selections in the database without touching code. 

    Friday, January 15, 2010 6:22 PM
  • User437720957 posted

    I don't disagree, but using enums in the database doesn't necessarily mean that they're stored numerically.



    Friday, January 15, 2010 7:24 PM
  • User-319574463 posted

    I would have them both as Enums and as tables in the database. In the database, the enum numerical value would be a Primary key, to allow full referential integrity to be enforced at the database level. It should be noted that generally an index is not recommended for a table with less that 10 records as a sequential scan can re faster than an index seek, however the advantage of easy of query by say SSRS, outweighs this.

    Monday, January 18, 2010 2:33 PM
  • User-952121411 posted

    I have to say I usually use both.  The reason for this is because they may not always be used for the same reason.

    The records in the database are used for basic SQL, foreign key relationships, and query joins to give a few examples.  Having an Enum in an application provides me no use for any of the aforementioned tasks.

    Having the Enums provide a much better way of referencing mostly static values represented by typically lookup SQL tables, without having to hardcode them all over the place.  The enums are a terrific way to pass values to methods, or in any reference to that type.  Especially since the enum name or value can easily be extracted at runtime. 

    As mentioned before, I use these enums for mostly static values (i.e. Relationship Types as you listed) because if a new value is added to the database, the enum must be updated as well.  They work best for values that don't need to change too often, so that you don't constantly have to redeploy the application.  However that guideline is not a strict bound by any means; they can be used for anything.

    Lastly, as a side benefit - the values in an Enum bind nicely to UI controls like a DropDown.  If you even need to do that, take a look at the following for an explanation:

    How To: Bind an Enum to an ASP.NET DropDownList:

    http://allen-conway-dotnet.blogspot.com/2009/12/how-to-bind-enum-to-aspnet-dropdownlist.html

    Hope this information helps! Smile

    Wednesday, January 20, 2010 2:15 PM
  • User187056398 posted

    Lastly, as a side benefit - the values in an Enum bind nicely to UI controls like a DropDown.  If you even need to do that, take a look at the following for an explanation:

    How To: Bind an Enum to an ASP.NET DropDownList:

    http://allen-conway-dotnet.blogspot.com/2009/12/how-to-bind-enum-to-aspnet-dropdownlist.html

     

    I also blogged that, in C#: http://weblogs.asp.net/stevewellens/archive/2009/08/19/how-to-fill-a-listbox-dropdownlist-from-an-enum.aspx

    Thursday, January 21, 2010 2:05 PM