I'm working on a system that stores orders - there are fields for recording the time an order was created, completed, processed, and shipped.

Therefore it is possible to determine the status of an order by querying these fields

However, i thought it would make queries much simpler if i had a status field enum(created, completed, processed, shipped) that is updated at the same time as the time fields

Instinctively it felt a bit wrong to store data that could be calculated from other fields but i think it would make like much easier...

any thoughts would be gratefully recieved..

ta
j

yes, go ahead and add the status column -- but don't use ENUM, which is the spawn of the devil, use TINYINT or CHAR(1), and link it via a foreign key to a status table

many thanks, rudy, is enum really so bad? using a foreign key to an external table means harder work glueing everything back together. with the enums i can see whats what from the raw table without having to remeber 0 = this, 1 = that, etc.

yes, ENUM is evil

so okay, instead of using a TINYINT or CHAR(1) as the foreign key, use VARCHAR(12) as the foreign key, and store the status name

ok, so even if i dont store anything in the related status table other than the primary key ie. the status_name, it is still a valid struture because the status table is used to validate status entries in the orders table???

yes, perfectly valid

many thanks!

I'd prefer a CHECK constraint as long as the status codes need not be translated through SQL (e.g. through a JOIN)

one of the problems with a CHECK constraint, as well as with ENUM, is that it requires the DBA to make a change to the production schema when you want to add a new status value

if you've ever worked in a large organization, where the bureaucracy requires you to fill out a project plan and get three levels of management signature just to ask the DBA to pick his nose, you'll agree that this is a non-starter

The question looks is already answered.
I just want to add a very general rule for those who thinks very academic.
Sometimes you need to add a redundant field to your tables because you want to code easier and to maintain your code easier.
Remember that your code writing is 10-15% of the life cycle but maintenance is 85-90% (and even more). So, the easier the code structure is, the easier would be the maintenance!
Good luck!