Dependencies on the same table

Question

Dependencies on the same table

Navigation

#1 by (6 votes)
#2 by (2 votes)

6

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

A customer can have several dependents who benefit from his or her contracted plan if he or she is killed. I am in doubt as to the data model. I could create two tables, one for clients and one for dependents, making a 1: N relationship, but I do not think it would be wise to have the structure of the tables be the same.

You could also create a single table with a boolean field to check whether a client is dependent or not. But what if he is dependent? How do I relate it to another gift on the same table? In MongoDB I think this would be simple, but here we are talking about MySQL.

If both approaches are indecent, could anyone suggest a better way to do this?

mysql sql

asked by anonymous 13.06.2014 / 15:02

2 answers

2

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

Inthiscase,forholdersyouleavethefieldid_titularasnull.

Toselectincumbentsanddependents:

SELECTa.nomeastitular,b.nomeasdependenteFROMclienteaLEFTJOINclientebONa.id=b.id_titularWHEREa.id_titularISNULL

InthiscasetheWHEREclauseistoselectallclientsanddependents.

Ifyouwanttoselectaspecificclientanditsdependents,changetheclauseto

WHEREa.id=1//iddoclientetitular

SQL Fiddle

13.06.2014 / 15:25

Use a security framework or finger-nail? Mathematical Combinator

score 6 · Accepted Answer

Self-relationship

When I need to do this type of AutoRelation I create a field, as in your case, TitularId that if it is null, is the holder, otherwise this is the dependent of the holder referenced

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

This structure is great for creating records in trees, where the child record knows its parent, and the parent can have many children and their children become parents of other records as:

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

Binding table

Another form and having a binding table, with both key fields referencing a customer table, maybe even with a field to identify the contract, since a dependent can also make a plan for himself

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

That's great for N-to-N relationship cases like:

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      |

Contracts Table (Linking the Contract to the Dependent instead of the 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

Completing

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

Example

Mr. B and Mrs. A Children C, D, E

Mrs A makes a plan and puts her husband and children as dependents A - > B, C, D, E
Lord B makes another plan and puts 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);