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;
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.
– Roger Barretto
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.
– bfavaretto