Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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

share|improve this question
    
Also see dba.stackexchange.com/q/11631/630 –  gbn Feb 14 '12 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? –  kevin cline Feb 14 '12 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 '12 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 '12 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. –  kevin cline Feb 16 '12 at 21:48

8 Answers 8

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.

share|improve this answer
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. –  GrandmasterB Feb 14 '12 at 19:26

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;
share|improve this answer
    
Some idiot can hardcore the ID too. –  Morons Feb 14 '12 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 '12 at 16:55
2  
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 '12 at 19:19
1  
@armitage: it is perfectly possible to do a lookup using strings. Resource names are strings: status.draft=Draught –  kevin cline Feb 14 '12 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 '12 at 21:41

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.

share|improve this answer
    
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 '12 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. –  Spencer Rathbun Feb 14 '12 at 16:49
    
hides eyes, mourning the GUIDs that will never return –  sq33G Feb 15 '12 at 8:29

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

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).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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