Handling Status Dilemma

There is a recurring problem regarding status fields and similar predefined set of values.

Let's take an example of an ordering system with an order entity which has a status that could be New, In Progress, Paid, etc.

The problem:

The Status of an order need to be

• stored (in database)
• processed (in backend)
• communicated (to frontend in web service API)

How to do these three activities while keeping:

• Preserve the meaning of the status.
• efficient storage.

Here are some example implementations with their pros and cons:

1- Status Table

• The database will contain a status table with id, name

• Order table references the id of the status.

  CREATE TABLE `status` (   `id` INT NOT NULL,   `name` VARCHAR(45) NOT NULL,   PRIMARY KEY (`id`));  CREATE TABLE IF NOT EXISTS `order` (   `id` INT NOT NULL AUTOINCREMENT,   `status_id` INT NOT NULL,   PRIMARY KEY (`id`),   INDEX `order_status_idx` (`status` ASC),   CONSTRAINT `order_status_id`     FOREIGN KEY (`status_id`)     REFERENCES `status` (`id`)     ON DELETE NO ACTION     ON UPDATE NO ACTION); 

• The backend code has an enum that gives these predefined integers a meaning in the code

  enum Status {     PAID = 7; };  // While processing as action ... order.status = Status::PAID; 

• The web service API will return the status number

  order: { id: 1, status_id: 7 } 

• The frontend code has a similar enum that gives these predefined integers a meaning in the code. (like the backend code)

• Pros:

  • The database is well defined and normalized
• Cons:
  • The mapping between the status number and meaning is done in three places which gives space for human errors and inconsistency in defining the meaning of a specific status number.
  • The returned data from the API is not descriptive because status_id: 7 does not deliver a concrete meaning because it does not include the meaning of the status_id: 7

2- Status ENUM

• In database, the order table will contain a status columns with type ENUM containing the predefined statuses.

  CREATE TABLE IF NOT EXISTS `order` (   `id` INT NOT NULL AUTOINCREMENT,   `status` ENUM('PAID') NULL,   PRIMARY KEY (`id`)); 

• The backend code has constant values as code artifacts for the predefined status

  enum Status {     PAID = 'PAID' }; 

  OR

  class Status { public:     static const string PAID = PAID; }; 

  To Be used as follwoing

  // While processing as action ... order.status = Status::PAID; 

• The web service API will return the status constant

  order: { id: 1, status: 'PAID' } 

• The frontend code will have a similar construct for predefined status constants. (like the backend code)

• Pros:

  • The database is well defined and normalized
  • The returned data from the API is descriptive and deliver the required meaning.
  • The status constants used already contain their meaning which reduces the chances of errors.
• Cons:
  • Using an ENUM type for a column in database has its limitations. Adding a new status constant to that enum later using an ALTER command is expensive specially for huge tables like order table.

3- My proposed solution:

• The database will contain a status table with one field called key with type string which is the primary key of this table.

  CREATE TABLE `status` (   `key` VARCHAR(45) NOT NULL,   PRIMARY KEY (`key`)); 

• The order table will contain a field called status with type string which references the key field of the status table.

  CREATE TABLE IF NOT EXISTS `order` (   `id` INT NOT NULL AUTOINCREMENT,   `status` VARCHAR(45) NOT NULL,   PRIMARY KEY (`id`),   INDEX `order_status_idx` (`status` ASC),   CONSTRAINT `order_status`     FOREIGN KEY (`status`)     REFERENCES `status` (`key`)     ON DELETE NO ACTION     ON UPDATE NO ACTION); 

• The backend code has constant values as code artifacts for the predefined status

  enum Status {     PAID = 'PAID' }; 

  OR

  class Status { public:     static const string PAID = PAID; }; 

  To Be used as follwoing

  // While processing as action ... order.status = Status::PAID; 

• The web service API will return the status constant

  order: { id: 1, status: 'PAID' } 

• The frontend code will have a similar construct for predefined status constants. (like the backend code)

• Pros:

  • The database is well defined and normalized
  • The returned data from the API is descriptive and deliver the required meaning.
  • The status constants used already contain their meaning which reduces the chances of errors.
  • Adding a new status constant is simple with INSERT command in the status table.
• Cons:
  • ???

I'd like to know if this is a feasible solution or there is a better solution for this recurring problem.

Please include reasons why the proposed solution is bad and why your better solution is better

Thank you.

1 Answers

Answers 1

This my approach for this problem:

1. I add a column status with type string in the orders table.
2. Define the constant of all your statuses in your class so you can reference them easily.
3. Make a validation rule on creation of order that the status value is in the only allowed ones you defines earlier.

This makes adding a new status very easily by just editing your code base, and the retrieved value for the status is still a string (descriptive).

I hope this answer your question.

If You Enjoyed This, Take 5 Seconds To Share It

• Facebook
• Twitter
• Google+
• StumbleUpon
• Digg
• Delicious
• LinkedIn
• Reddit
• Technorati