Quite a lot of applications require records in their tables to have a status, such as 'complete', 'draft', 'cancelled'. What's the best way of storing these statuses? To illustrate what i'm getting at here is a *very short) example.
I have a simple Blog application and each post has a status one of: published, draft or pending.
The way i see it there are 2 ways to model this in the database.
- The Post table has a text field that includes the status text.
- The Post table has a status field that contains the ID of a record in the PostStatus table
The Blog example here is a very simple example. Where an enum (if supported) might suffice. However i'd like responses to the question to take into account that the list of statuses could change at any time, so more could be added or removed.
Can anyone explain the advantages/disadvantages of each?
Cheers!
My initial optnion on this is that its better to use another table and look up the status as its better for normalisation and i've always been taught that normalisation is good for databases
Best Answer
Storing the status as an index into another table is an unnecessary complication. Store the status directly in the table in a readable way. In the application code use constants or an enumeration type. This will result in simpler application code and ease debugging of the data layer.
This does not denormalize the data, it merely changes the representation. If the database supports enumerations directly, then use that. Otherwise use a constraint to restrict the column values. You are going to have a constraint either way: either a direct constraint on the column values, or a foreign key constraint.
Yes, you may have to present the status differently to different users. That is a presentation problem, to be solved in the presentation layer, not the persistence layer.