status and error fields in the table?

I am trying to think of good table design for defining statuses and error if any.

For example in the queue table I am considering creating two fields called status and error.

By default error value would be null.

The value in status field could be one of the following: pending, in_queue, completed, error

If I define error value in the status field - it doesn't tell me what type of error it is. Should I set error value in the status and in the error to describe error type such as: ftp_login_failed

eg:

update queue set status='error' error='ftp_login_failed' where id=5;

1 answer

  • answered 2017-06-17 18:11 Mike Nakis

    I would go with:

    • Either only one field, status which can be pending, in_queue, completed, error_a, error_b, ... If you select your error names wisely, you can do SELECT ... WHERE status LIKE 'error%'

    • Or two fields, where status can be pending, in_queue and completed, and then if completed then error IS NULL means no error, while error IS NOT NULL means that there was an error.