Database design - Clarification

Question

I have designed a table called USER_REQUESTS. It has a field called STATUS ,the purpose of which is to capture the current status of the request. It can be something like submitted,in progress,completed etc... Now my questions is,do I have to create a master table to hold the possible request status? If so,what would be the benefits?

Answer 1

You don't have to, but it is good practice to do so - such tables that only hold a set of values are commonly referred to as lookup tables.

This way, you can constrain the STATUS values in your USER_REQUESTS table to only correspond to the possible STATUS values.

Some databases allow you to define an ENUM type that will constrain values in this manner.

Answer 2

Status is a State object.

State objects have more than just a name ("submitted", "in progress", "completed")

Most of the time a State object has a set of legal next states. Some States (i.e., "completed") have no next state.

Most of the time a State object has a process which will advance the thing from one state to the next state. In some cases, you can actually name that process as part of the State.

You should have a table like this.

State Name | Process          | Success Next State | Failure Next State 
submitted  | start "some.sh"  | in progress        | submitted
in progress| check script     | completed          | failed

Now your application merely provides an implementation for each "process" step. The overall "workflow" is defined by your state table.

You can easily reconfigure your application by changing the definition of your states.

That's the benefit.