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