Should I track the status of a system with a single or multiple tables? [closed]

Question

Should I track the status of a system with a single or multiple tables? [closed]

Navigation

#1 by (2 votes)

0

My question is somewhat conceptual and allows for a variety of interpretations.

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

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

Between these two forms, is there a better and a worse? Is there any other way to do this that I have not quoted here?

sql

asked by anonymous 04.04.2014 / 19:26

1 answer

Form submission via AJAX, method PUT Leave td with transparent background

score 2 · Accepted Answer

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

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

Assume the following statuses for each entity:

Load: separating (0), loading (1), carrying (2) and delivered (3);
Request: in-progress (0) and done (1);
Payment: pending (0), carried out (1) and unauthorized (2).

The numbers in parentheses are the codes for each status.

Note that for each entity there is a unique set of status and taking into account that each exists independently of each other, a Pagamento can not have 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 date. In this way there is a precise history of when each of them occurred. Also note that the primary key of these status tables is made up of the foreign key plus the code, so an entity will never repeat it if this is a requirement.

See sqlfiddle the physical template for this solution.

Now a selection of data for Load might look 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;