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?
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.
3
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.
7