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

Asked

Viewed 1,157 times

0

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?

  • 1

    It depends a lot... if all your status is just names, this solution might be a good alternative. If Voce has or will in the future have different fields for each status it is suggested to use separate tables. But there is still a third alternative, creating a status table only with Description and the other (specialized) tables pointing to this first with these dedicated fields.

  • 1

    Welcome back, Anderson boy. I edited your question to be a little less open, because questions that ask for answers based on opinions tend not to work here. They generate many answers, and none more right or wrong than others. Here it should be possible to identify an answer as being the correct one.

1 answer

2


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;
  • Hello Fabio, thank you very much for the answer, very enlightening. I liked mainly the date field that shows when occurred each status. I only had one question. Let’s say I want to return the status in written form, in a view for example. I would have to do a CASE for each Status?

  • Dear Andy. Yes, use the CASE is an option. I have updated the answer and sqlfiddle.

Browser other questions tagged

You are not signed in. Login or sign up in order to post.