CODEWITHSUNDEEP
database-design
Graf
Graf

Reputation: 1507

How to store statuses in DB

My application is processing user requests. Each request has the following statuses: "some parts of request received from user", "all parts received", "started processing", "result is ready", "result is sent to user". I want to store request status in a database as a single column. Right now I have two ideas how to implement it:

  1. Use a string column. Each request status is stored as text. For instance, a request which was sent to the user, will have a status value of "sent".

    • (+) Even a technician not familiar with my code can understand what's happened with request after looking in DB
    • (-) For storing a request status I use multiple bytes, thougn actually this information can be stored in one byte (see p. 2)

  2. Use an integer column. Each request status is stored as int. For instance, "some parts of request received from user" = 1, "all parts received" = 2, "started processing" = 3, "result is ready" = 4, "result is sent to user" = 5.

    • (+) When you look in the database you can't understand what's going on. Need to write a code which converts integers to textual representation. Also when you suddenly decide, that between "result is ready" and "result is sent to user" should be a status "user agreed to get result", you must accept that status "user agreed to get result" will be 6, not between "result is ready" (4) and "result is sent to user" (5), which seems a little bit unlogical to me.
    • (-) Use as little space as possible

I see there is no point in using more than one byte when one byte is enough. I even agree to write converting code (text status -> integer status and vice versa). But what should I do with sudden intermediate statuses? Is there a different approach?

Upvotes: 1

Views: 1870

Answers (1)

BillRobertson42
BillRobertson42

Reputation: 12883

Storing the data as an integer does not have to hide your information, you can create a lookup table that relates the integer constants to their textual values, and then join them if you want to look at a textual representation of the data.

On the other hand, unless you plan on storing many millions of rows, or have to process many thousands of transactions per second, the extra few bytes in your state table will probably not make that much of a difference.

I'm not sure what you mean by the latter part of your question when you say, "sudden intermediate statuses." Are you worried about having to add more states later, or are you talking about modeling transitions from one state to the next as states in your finite state machine?

Upvotes: 1

Related Questions