32

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.

  1. The Post table has a text field that includes the status text.
  2. 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

5
  • 1
    Also see dba.stackexchange.com/q/11631/630
    – gbn
    Feb 14, 2012 at 14:19
  • What do you mean by "at any time" ? Does that mean as part of user activity, or as part of the software release cycle? Feb 14, 2012 at 20:22
  • Both, in which cases are any of the approaches mentioned here best used. So if users are able to add new statuses, or if new ones get added at a later point in the project
    – veganista
    Feb 15, 2012 at 9:27
  • Storing the text in the database may be a good denormalisation. I think it may depend on precise details eg How often does your organisation change its processes (leading to possible status changes)?
    – Jaydee
    Feb 15, 2012 at 12:14
  • If users are able to add new statuses, then it's another thing entirely. You will probably want to record the creating user etc. with the status and will definitely need another table. Feb 16, 2012 at 21:48

9 Answers 9

20

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.

2
  • 1
    +1, Barring a specific need to keep the list of statuses in the db, this is generally the simplest, least complicated way to do it. Feb 14, 2012 at 19:26
  • 2
    This is ok, unless you start changing status architecture or storing mutation dates Apr 6, 2018 at 18:36
14

Storing the status text is IMO not a good idea, since someone might decide that "complete" should be called "finished" instead and then you have to update your database, look through the program if someone hardcoded the text etc.

What I have seen in many programs is either a numerical code (1=new, 2=draft, 3=in validation, 4=complete, 99=cancelled) or a short alphanumerical code ("NEW", "DRA", "INV", "COM", "CAN"). The later makes the code (in the program or in the database) more human-readable, which is generally a good thing. On the other hand, numerical codes make it easy to do "greater than" or "smaller than" comparisons, for example

select * from myrecords where status < Status.Complete;
5
  • Some idiot can hardcore the ID too.
    – Morons
    Feb 14, 2012 at 14:31
  • Another advantage of IDs is you need to provide localisation. You can use your ID to lookup the resource string and display. With hard coded strings this is not possible
    – armitage
    Feb 14, 2012 at 16:55
  • 4
    I dont think doing statuses using "greater than" or "smaller than" comparisons like you have shown is a good idea. It may work for simpler applications such as this example but is not good for more complex apps (although im sure your aware of that)
    – veganista
    Feb 14, 2012 at 19:19
  • 1
    @armitage: it is perfectly possible to do a lookup using strings. Resource names are strings: status.draft=Draught Feb 14, 2012 at 19:29
  • veganista: Sure, there can be difficulties with greater than/smaller than comparisons, but I have seen large, complex systems that do that and live.
    – user281377
    Feb 14, 2012 at 21:41
4

The three rules of relational databases:

  1. Normalize
  2. Normalize
  3. Normalize

So your question answers itself. Keep the status inside of it's own table and use GUID/UUIDs as your id. Indexed GUIDS are very fast, and fix the problems intrinsic to incrementing numbers. With an id you can do cool things like ask the DB for all completed posts using the id, and because you are working within the relational db paradigm, it is very fast. If you have just a field, the DB has to loop over every single row and do a text comparison, maybe with munging, and that is very slow.

Post status names can change, more info about post status can go into the table, everything just works if you normalize.

For example, you can add status levels as additional info, which would allow for the comparison ammoQ mentions. But they don't depend upon the key for positioning, allowing rearranging of status level without harming DB integrity. You can also insert additional levels, which is quite a trick if you have the level associated to the autoincrementing key.

5
  • The reasons you have stated here are exactly the reasons i have being using another table to store my stauses. The main reason why i have asked this question is to see whether sometimes its good to use a simpler text field.
    – veganista
    Feb 14, 2012 at 16:27
  • @Liam Only if it normalizes down to a text field. That is, if your text field depends only on the primary key, and you are looking things up based upon the primary key, with the text field coming along. A relational DB is about relationships, you have one here, so it needs to be defined. One of the few exceptions is if you are handling dirty data from an outside source, and you do not have the time to model it completely. Avoid this if possible. Feb 14, 2012 at 16:49
  • hides eyes, mourning the GUIDs that will never return
    – sq33G
    Feb 15, 2012 at 8:29
  • You should have written "three theories of relational databases". Theory is not always practical. It is often more efficient to store a status code directly in the record to which it's related. If you don't need to look it up to use it, removing the join to another table saves a lot of wasted processing.
    – Suncat2000
    Oct 12, 2017 at 19:50
  • 1
    Downvoted because of the wrong information about column types vs. full table scans.
    – igorrs
    Aug 22, 2019 at 4:03
2

Yes, you should go with option 2, having a PostStatus table.

Apart from all the advantages mentioned in other answers.

Keeping in mind that the statuses needs to be added or removed, you can have an "enabled" column in the PostStatus table, so if the status is removed mark the "enabled" column as "N", that way you will be able to add or remove statuses and also the existing records will stay without issues.

1

Using text for status in the record table would probably not be a good idea as this can change and it would be difficult to perform any data integrity checks on insert/update. If you are utilizing a DBMS with an enum datatype, you can use this instead (performance will probably not be compromised ...depending).

If your status needs any metadata (description, created by, friendly name, ...) you will need to store statuses in a separate table and have a status key in your record table (make sure you use a foreign key). The id does not necessarily need to be a number, just the PK of the status table. Also, if the statuses are in their own table, you can share them across record types (tables) if applicable. I wouldn't worry about performance issues with a JOIN to the status table.

Whatever you do, make sure you avoid magic statuses (1 for active, 2 for deleted, ...). This relies on documentation and tradition which always have a tendency to get lost on a large enough timeline. If you are using numeric ids at all, make sure there is a textual association somewhere in your db.

1
  • If you aren't worrying about performance, you're likely sacrificing scalability. It's impossible for computers to avoid magical statuses: 0 and 1 are intrinsically magical.
    – Suncat2000
    Oct 12, 2017 at 19:52
1

I'd like to add to the otherwise insightful answers that for full normalization, a change in the status of an entity is actually modeled in a separate entity, e.g. named 'statusChange'.

You'd need an extra join with the statusChange entity, but you win the possibility of adding extra information, such as the actor performing the change, possible comments on why the change occurred and a date at which the statusChange is performed and possibly even when it becomes effective.

0

Depends on the purpose of the database design.

If you design the database simply to support the application (ie the objects (code) are master of all) then using an enumeration (or a psuedo enumeration for classes that don't support them) and storing off the name of the enum is a good idea because you still control the values allowed through the enum and you also make the table a bit easier to read when you are forced to view the raw data (which isn't that often if the code actually rules all). But if the enumeration is flagged. Then I usually store off the enum value (integer).

-1

The status is very important, any time you get post info you'll need to get it's status, or you'll want to filter posts by status. If you have status in another table, you'll need to make joins to get this info and so performance is compromised. Definitely you should have status in same table. And put an index on it! You can still use integers as status, or maybe enum field.

-2

The correct solution is to either use an Event Store/Source with CQRS or a blockchain. The problem with capturing events in a RDB is that RDB stores a snapshot of a single event in time, and things like "Statuses/States" are sequence of mutations that evolve over time

2
  • If you are going to down vote my post, then make a case. Else you are just a soft minded lemming who has very little scope outside the box Apr 12, 2018 at 13:55
  • Agree with this however the case against it is the added complexity
    – Sid
    Jan 9, 2023 at 10:08

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