Handling Status Dilemma

Question

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.

Answer 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.