Dependencies in the same table

Question

A funeral home asked me to do a simple customer registration scheme for them.

A client may have several dependents who benefit from their contracted plan if they die. I am in doubt about the data model. I could create two tables, one for customers and one for dependents, making a 1:N ratio, but I don’t think it would be wise because the structure of the tables is the same.

Could also create a single table with a field boolean, to check whether a client is dependent or not. But what if he is dependent? How do I relate him to another present in the same table? In Mongodb I think this would be simple, but here we are talking about Mysql.

If both approaches are indecent, could someone suggest me a better way to do it?

Answer 1

Self relationship

When I need to do this kind of Self-regulation I create a field, as in your case, Titularid which, if null, is the holder, otherwise it is the dependent of the referenced holder

ClienteId | Nome     | CPF         | TitularId
  1       | Pedro    | XXXXXXXXXX  | NULL
  2       | João     | YYYYYYYYYY  | 1

This structure is great for creating records on trees, where the child record meets its father, and the father can have many children and their children be parents of other records such as:

+ Home
- Assuntos
  - Desenvolvimento
    - C#
      - MVC
      - WCF
      - WebApi
    - Delphi
    - Java
      - J2EE
      - J2SE
- Outros

Linking table

Another form is to have a binding table, with both key fields referencing a client table, perhaps even with a field to identify the contract, since a dependent can also make a plan for themselves

TitularId  |  DependenteId
-----------------------------
  1        |    2
  1        |    3
  4        |    5

Which is great for relationship cases N for N as:

Projetos
-----------------
|Id  |  Nome     |
|-----------------
| 1  |  FrontEnd |
| 2  |  BackEnd  |

Usuarios
-----------------
|Id  | Nome      |
|-----------------
| 1  |  AAAAAA   |
| 2  |  BBBBBB   |
| 3  |  CCCCCC   |

|UsuariosProjetos
|------------------------
|IdProjeto |  IdUsuario |
|------------------------
|1         |     1      |
|1         |     2      |
|2         |     1      |
|2         |     3      |

Contract Table (Binding the Contract to the Dependent instead of Client/Dependent)

You can make the customer table not have to worry about this rule. Both the Holder and the dependent are Customers

so you could have a table of contracts:

ContratoId  |  ClienteId | DataInicio | DataFim
  1         |     1      | 01/01/2001 | NULL

and a table of dependents

ContratoId | DependenteId
  1        |    2
  1        |    3

Concluding

In your case, I recommend using the Binding or Contract model, since it may be that a dependent is dependent on more than one customer, and that a customer in a contract may be dependent on others

Example

Lord B and Lady A Sons C, D, E

Lady A makes a plan and places her husband and children as dependents A -> B,C,D,E
Lord B makes another plan and places his wife and children as dependents B -> A,C,D,E

CREATE TABLE clientes (
  ClienteId INT NOT NULL,
  Nome VARCHAR(100),
  PRIMARY KEY (ClienteId)
);

CREATE TABLE contratos (
  ContratoID Integer NOT NULL,
  ClienteId Integer,
  PRIMARY KEY (ContratoID),
  FOREIGN KEY (ClienteId) REFERENCES clientes(ClienteId)
);

CREATE TABLE dependentes (
  ContratoId INT,
  DependenteId INT,
  PRIMARY KEY (ContratoId, DependenteId),
  FOREIGN KEY (ContratoId) REFERENCES contratos(ContratoId),
  FOREIGN KEY (DependenteId) REFERENCES clientes(ClienteId)
); 

INSERT INTO clientes (ClienteId, nome) VALUES (1, 'A');
INSERT INTO clientes (ClienteId, nome) VALUES (2, 'B');
INSERT INTO clientes (ClienteId, nome) VALUES (3, 'C');
INSERT INTO clientes (ClienteId, nome) VALUES (4, 'D');
INSERT INTO clientes (ClienteId, nome) VALUES (5, 'E');

INSERT INTO Contratos (ContratoId, ClienteId) VALUES (1, 1);
INSERT INTO dependentes (ContratoId, DependenteId) VALUES (1,2);
INSERT INTO dependentes (ContratoId, DependenteId) VALUES (1,3);
INSERT INTO dependentes (ContratoId, DependenteId) VALUES (1,4);
INSERT INTO dependentes (ContratoId, DependenteId) VALUES (1,5);

INSERT INTO Contratos (ContratoId, ClienteId) VALUES (2, 2);
INSERT INTO dependentes (ContratoId, DependenteId) VALUES (2,1);
INSERT INTO dependentes (ContratoId, DependenteId) VALUES (2,3);
INSERT INTO dependentes (ContratoId, DependenteId) VALUES (2,4);
INSERT INTO dependentes (ContratoId, DependenteId) VALUES (2,5);

Answer 2

You can create a relationship in your table, see in the example below:

In this case, for holders you leave the field id_titular as null.

To select holders and dependents:

SELECT 
a.nome as titular,
b.nome as dependente
FROM cliente a
LEFT JOIN cliente b
ON a.id = b.id_titular

WHERE a.id_titular IS NULL

In this case the clause WHERE is to select all customers and dependents.

If you want to select a specific customer and their dependents, change the clause to

WHERE a.id = 1 //id do cliente titular

SQL Fiddle