How to handle "Type" field in DB and app?

Question

It is necessary to implement a logging, messages are planned to store in DB in a "log" table. Among other fields each message will have a "status" field: 0 - successful, 1 - wrong data, 2 - wrong user, 3 - whatever, 4 - etc...

The straightforward idea is to store the "Status" field as "int" column in the same table... To push data in table special enumeration will be created, something like this (let's use C#.NET for example, but any language will work too):

enum LogStatusEnum
{
    Successful=0,
    WrongData=1,
    WrongUser=2,
}
void main()
{
    LogStatusEnum status = LogStatusEnum.Successful;
    int statusValue = (int)status;
    string query = "INSERT INTO log (Status, ...) VALUES ("+statusValue+",...)";
}

There is also another approach: to create additional table, something like "log_status" with fields "StatusId" (int, autoincrement), "StatusCode" (varchar), "StatusDescription" (varchar) that will contain a separate record for each status field (with a foreign key applied to both tables). In this case before adding data into "log" table ID for the required "code" should be fetched in advance with query:

query = "SELECT Id FROM LogStatus WHERE StatusCode='"+GetStatusCode(status)+"'";

and this (received) ID will be used to populate "log" table.

In both cases you need to keep in sync both DB side and application side. But from my perspective, the 2nd approach is a little bit better:

1. more safe: you need to be sure that your "status" is really present in DB before adding data, you will have a constrain (wrong status won't be added).
2. more data-driven (it is hard to say for me why it is better, but I fill that).

In order to get these benefits you need to pay: perform a request to DB to get status ID by status code.

Do you think it is reasonable to implement the 2nd approach? Or the 1st will fit too? Do you see any other pros of the 2nd approach or cons of the 1st one?

Any thoughts are welcome.

Thank you in advance.

Answer 1

The second approach is usually better. If you need a new status type, you only need to update the database and not data structures in your application code.

If you are doing a lot of inserts, you shouldn't query for the status id every time, but cache it.

Answer 2

Assuming that the log statuses in the database will be fairly static I'd set up the application to load them all out in a local cache at startup, so that you don't have to keep loading them all the time.

I like the second approach myself when possible. Unless you work in a place where it's very difficult to get approval for changing data in tables manually to add more statuses etc.

Answer 3

I'd define the status codes in the database, and I'd although an int field is ok a Char(4) would be better as the codes will be human readable - and - you should also specify a description;

• "OK__", "The operation occured as planned"
• "WUSR", "Wrong user"
• "WDAT", "Wrong data"

The problem with using numbers and not having descriptions in the DB is that you can't report off it without knowing for certain that the code is in sync - or what the codes were in the first place. Having it all together will make life easier in the long run - keep the Common Reuse Principle in mind.

Make the descriptions as useful as possible. From memory, in MS SQL a Char(4) uses the same amount of space as an int - so you're not using extra space by using the char.

I definately wouldn't have the ForeignKey used to externally identfy the codes as an auto incrementing number; if the values get upset you'll loose integrity with the code.

If you're going to hardcode status codes into the app,, I'd suggest including a way for people to get access to the codes without having to view the source code; documentation might cut it but it's easy to get out of sync - what you want is some sort of callable interface (UI, webservice, etc) that anyone (like an admin / DBA) can call easily. Attributes are a good way to do this.

For general reference (or a pre-built implementation) I would very strongly suggest looking at the MS Enterprise Libraries, this includes a Logging block, and includes a database respository as well.