Should I control the status of a system with a single table or several?

Question

My question is somewhat conceptual and allows several interpretations.

I am responsible for a system that uses status in several modules. Cargo status, order status, payment status, etc. For status group there is a different table, for example: Status, Status, Status.

However, doing maintenance on a system done by another company, I saw that they have a single status table, and all possible status options are within this table. Therefore, my load, order and payment tables would all look at the same Status table.

Between these two forms, is there a better and a worse? Is there another way to do it that I haven’t mentioned here?

Answer 1

Due to the data domain of each of the entities mentioned in the question, the approach of a status table for each entity is much more organized and better meets the maintenance and extensibility requirements normally present in large projects.

If there is only one table to control status of multiple entities, everything can get very confusing.

Suppose the following status for each entity:

• Load: Separating (0), loading (1), carrying(2) and delivered(3);
• Request: in-progress (0) and completed (1);
• Payment: pending (0), paid (1) and unauthorised (2).

The numbers in parentheses are the codes of each status.

Note that for each entity there is a unique set of status and taking into account that each entity exists independently of each other, one Pagamento cannot hold the status carregando, for example.

So a possible modeling would be:

Carga
  - id

StatusCarga
  - carga_id
  - codigo
  - data
  - primary_key(carga_id, codigo)

Pedido
  - id

StatusPedido
  - pedido_id
  - codigo
  - data
  - primary_key(pedido_id, codigo)

Pagamento
  - id

StatusPagamento
  - pagamento_id
  - codigo
  - data
  - primary_key(pagamento_id, codigo)

Note that in addition to the foreign key for the entity table, there is the status code and the date. Thus there is an accurate history of when each of them occurred. Also note that the primary key of these status tables is composed of the foreign key plus the code, so an entity will never repeat it if this is a requirement.

See on sqlfiddle the physical model of this solution.

Now a data selection for Cargo could be like this:

#Selecionar todas as cargas
SELECT id, cidade, data,
   CASE WHEN StatusCarga.codigo = 0 THEN 'Separando'
        WHEN StatusCarga.codigo = 1 THEN 'Carregando'
        WHEN StatusCarga.codigo = 2 THEN 'Transportando'
        WHEN StatusCarga.codigo = 3 THEN 'Entregue'
        ELSE '<desconhecido>'
   END AS status
FROM Carga, StatusCarga
WHERE StatusCarga.carga_id = Carga.id;