Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: DB Design question - Enums vs Struct?

    I have a table in my database with several "states"

    -Offline
    -Online
    -StageA
    -StageB
    -Processing
    -Completing

    I have a 3-tier architecture, where the application talks to the middle-layer DLL, and the DLL talks to the DB.

    Which design should I approach the above situation with?

    1) Create a method GetStates() that returns an array of classes such as
    Code:
    class ItemState {
        int Id;
        int Name;
    }
    So the GetStates() method, above, will return an array of 6 elements -- one for each state.

    2) Create a enum of the above states such as
    Code:
    enum ItemStates {
        Offline,
        Online,
        StageA,
        StageB,
        Processing,
        Completing
    }
    The benefit of approach 2 is that it's very clean for the application user, and they don't have to worry about primary key ID's etc. On the downside, there will have to be some code to map the enum's with the primary key's in the database. This could get pretty messy:
    Code:
    // Some random value for the item state enum
    ItemState itemStateEnum = ItemState.StageA;
    
    // Get the states from the DB, and cycle through each state
    foreach( ItemStateStruct curStruct in GetStates() ) {
    
    // Find which state matches the value of the enum we just set
    if( itemStateEnum.ToString().Equals( curStruct.Name ) ) {
        // now we know the id
        int id = curStruct.Id;
        break;
    }
    
    }
    Again... I like how #2, is so clean, but it's tedious to map the two

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am no C# expert but I suspect that the enum might be more light weight than instantiating objects.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    On the other hand, with #2 you have tightly coupled your app and database. But of a pooper if you add a new state....

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah, poots is right for a change, pull the states from the datbase. that crap changes all of the time.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jan 2008
    Posts
    186
    Do you guys think that it's too "transparent" though? That structure?
    Code:
    class ItemState {
        int Id;
        string Name;
    }
    I suppose there's no other way around it though... I'm fairly new to all of this.

    How is something like this normally handled? Do a lot of applications out there use the #1 approach?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Enums have no bizness in a database. Think about it...you are hard-coding surrogate keys into the application layer. That can't be good...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    Enums have no bizness in a database. Think about it...you are hard-coding surrogate keys into the application layer. That can't be good...
    Yup, that's data coupling, but...How else you let the app be aware of what screen to show based on the status (if it has to make a decision of what screen to show based on it)? It's a dilema, that I could not resolve back in the days...
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by blindman
    Enums have no bizness in a database. Think about it...you are hard-coding surrogate keys into the application layer. That can't be good...
    Yeah I know...

    But to solve that problem, I was going to have a mapping from the Enum to the Id's within my middle-layer DLL.

    So I could have a function:
    Code:
    SetState( ItemStateEnum eState ) {
        int stateId = GetIDFromEnum( eState );
        executeSQL("CALL setState(" + stateId + ")");
    }
    Is that too tedious? I don't like the idea of having to make database hits with each call to "GetIDFromEnum"

    I guess I keep thinking about the application-layer guys coding, and how much easier it would be with Enum's instead of "Id/Name" classes

  9. #9
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by rdjabarov
    Yup, that's data coupling, but...How else you let the app be aware of what screen to show based on the status (if it has to make a decision of what screen to show based on it)? It's a dilema, that I could not resolve back in the days...
    Yeah, it's a lot more tedious to work with "Id/Name" classes rather than enums... Like for Enum's you can easily do:
    Code:
    if( myValue = ItemState.StageA ) {
        // do stuff
    }
    On the other hand, "Id/Name" classes would do something like:
    Code:
    ItemStateStruct items[] = myLib.GetItemStates();
    foreach( ItemStateStruct curItem in items ) {
        if( myValue == curItem.Id ) {
            // do something
        }
    }
    I mean, it's doable... But it just feels so messy and transparent to the application coders.

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I did come up with an excuse for myself to use enums. The reason I chose for it was that if another "state" would need to be added, my app still needs to be modified to include yet another condition. And I would use this opportunity to add yet another "hard-coded" enum into my front-end code. At the same time I would insert into my static table the new value for enum id and its name and/or description. Keeping them synchronized was needed to meet the reporting requirements, so...Yes, I am guilty of data coupling, but on a very small scale
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  11. #11
    Join Date
    Jan 2008
    Posts
    186
    Hey, rdjabarov, that's exactly the scenario I'm in.

    Even if I dump enum's and use pure structs/classes, I'll STILL have to modify my middle-tier or application layer to include a condition for that state. So if I have to modify it anyway, I might as well use Enums... That was my logic.

    How did that work out for you? Did you map your Enum values to the primary key values in the database table?

    My issue is that I don't want to have to map the enum values manually to what's in the database table
    Code:
    public enum ItemState {
        StageA = 2,
        StageB = 4,
        etc.
    }
    Instead, I want the values to be whatever they want, but instead I will map them based on their name. i.e. The database table will could have StageA with ID 8, but I will have a mapping function that looks for "StageA" and returns the value 8, as in my previous post:
    Code:
    SetState( ItemStateEnum eState ) {
        int stateId = GetIDFromEnum( eState );
        executeSQL("CALL setState(" + stateId + ")");
    }
    What do you think of that approach?
    Last edited by dbguyfh; 06-04-08 at 14:11.

  12. #12
    Join Date
    Jan 2008
    Posts
    186
    [Edit, combined both of my messages into one]

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I was using the state to color the record in the grid based on the id of the enum, as well as show the corresponging description of the state. In mine it was account status, so if delinquent, - it'll show it in a shade of red depending on the age of delinquency, and show the description of it - "Delinquent for the past " + <days_since_it_became_delinquent> + " day(s)". So in this case the values of enums in your declaration section of front-end have to match the table contents. For consistency the names of the enum values there can also be the same as the ones stored in that static table.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by rdjabarov
    ...So in this case the values of enums in your declaration section of front-end have to match the table contents. For consistency the names of the enum values there can also be the same as the ones stored in that static table.
    Ahhh yes, that was my delimma.

    My intent was to make the ID's in the database table independent of what the enum values were, to avoid coupling.

    But, now I'm realizing that it really makes no difference. If I don't couple on IDs & enum values, I'll have to couple on state_name and enum names.

    Either way, there will need to be some link between the enum and the database table, whether that be [ID & enum value], or [state_name & enum name].

    Was there any specific reason you chose to couple on ID<->enum value instead of state_name<-->enum name?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to throw out another idea....
    You can write triggers to output XML on insert\ modify\ delete. Have the application pick these files up and pop them in an array. Interrogate these as you have above (I don't really buy the "lots of inelegant code" bit - just write a function to encapsulate the loop through the array and call that).

    Benefits -
    no unnecessary round trips to database for largely involatile data.
    Application and database decoupled.

    I am always nervous about having to maintain & synchronise two+ sets of values manually. I value this even more than keeping the round trips down.

    Anyhoo - just me thunkles - take 'em or leave 'em.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •