current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.
up vote 4 down vote favorite

Consider a database table of Items that have a status flag represented by an integer. A few of the status might be:

0 - Past Storage;
1 - Current Inventory;
5 - Scrap;
6 - Rework;
15 - Processing;

Now, I would like to avoid passing and querying for 'magic numbers' in my code, and in the past I have used a Dictionary to accomplish this, but this approach seems less elegant than what I hope to accomplish.

How are these types of status flags retrieved from a database handled in object oriented code? Is it with enums, and if so, how? Or is it better to create a separate table with the status flag as the primary key?

share|improve this question

2 Answers 2

up vote 4 down vote accepted

Where I work this is a common situation. What we do here is use enums AND a separate table with the status flag as the primary key. In our experience, things have been a lot easier when the primary key was not an identity field. The good thing about doing it this way is that the c# compiler has a list of valid values (the enum) and the DBAs and whoever else has to work with the data (report writers) also has a list of valid values (the table). The downside, of course, is that any additions or modifications have to be done in both places.

share|improve this answer
    
OK, so let's assume then I add an ItemStatus table as you described. This allows me to query the status name directly with EF as so: var status = context.Items.First(i=>i.ItemStatus=="Scrap"). But now I'm working with magic strings. How do I implement the enums you described? –  field_b Jan 3 '14 at 20:09
2  
@field_b context.Items.First(i=>ItemStatus == (int)StatusFlag.Scrap) as opposed to context.Items.First(i=>ItemStatus == 5) –  Jon Raynor Jan 3 '14 at 20:16
    
+1. Typed, (complete) list of values, and Visual Studio intellisense. DB values can be validated as a member of the enum. Having enum + DB table is, IMHO, a crosscheck to ensure a new one is fully implemented. I always have Undefined = 0 as this is the default value. –  radarbob Jan 4 '14 at 2:34
up vote 3 down vote

Usually integers can be represented by enums in code, as that will make it more readable for code blocks that use the integer.

Example:

        public enum StatusFlag
        {
            PastStorage = 0,
            CurrentInventory = 1,
            Scrap = 5,
            Rework = 6,
            Processing = 15,
            Unknown = 99 //Optional
        }

I included an optional unknown, but you may not need it. Also, this is not really necessary, you could use integers as is with something like this:

if (statusflag == 15) //processing

But this introduces some magic number like scenarios.

Although not mandatory, there should be a table in the database that has the integer as the PK and probably a description field.

Any reference tables should have FK to the status flag lookup table. That way, one cannot insert a status flag that is not in the table.

If this is not there, there is a potential for any integer to be inserted, so there is less referential integrity to the data. The downside of this is that every new integer will have to be added to the DB and code. If you leave it as an INT with no PK/FK relationships, you can add integers ad hoc. Code changes may need to be done if you wish to incorprate the new INT with newer logic.

I tend to advocate the enum/PK-FK style as making additions should be relatively straight forward. This also ensure data integrity which is a good thing.

share|improve this answer
    
We use foreign keys to the status flag lookup table too. I forgot to mention that in my answer. –  Larry Coleman Jan 3 '14 at 20:23
    
Yuck. Why use enums for something you never enumerate? What's wrong with static consts? –  pdr Jan 3 '14 at 21:18
2  
@pdr - Enums are typed. If you have a method such as DoSomethingWithStatus(int status), any integer value of status can be passed, where in this case there are only 1/2 dozen valid values. If we have DoSomethingWithStatus(StatusFlag status) we can gaurantee only valid status are passed to that method. Maybe better intellisense as all values are grouped as well. –  Jon Raynor Jan 3 '14 at 21:39
    
@JonRaynor: Eh. I guess, in theory. But then why don't you go as far as wrapping every primitive in a class (as suggested in Code Callisthenics)? –  pdr Jan 3 '14 at 22:11
    
If possible, I always have Undefined = 0. And I mean "undefined", not "unknown". Anyway, zero is the C# default value. In practice this mean the enum variable must be explicitly set to a valid value, it cannot default. In the long run I have found this to be most satisfactory for ensuring correct state. –  radarbob Jan 4 '14 at 2:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.