Relationship Tables

Question

I have a table Endereco which serves as much to Funcionario how much for Supplier:

1 Funcionario possui 1 Endereco
1 Fornecedor possui 1 Endereco

How I create this relationship without having inconsistencies?

Answer 1

I think this is what you want:

CREATE TABLE Fornecedor {
  id INT(4) NOT NULL UNSIGNED AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
  endereco INT(4) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (endereco) REFERENCES Endereco(idEndereco)
}

CREATE TABLE Funcionario {
  id INT(4) NOT NULL UNSIGNED AUTO_INCREMENT,
  nome VARCHAR(100) NOT NULL,
  endereco INT(4) NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (endereco) REFERENCES Endereco(idEndereco)
}

CREATE TABLE Endereco {
  idEndereco INT(4) NOT NULL UNSIGNED AUTO_INCREMENT,
  logradouro VARCHAR(100) NOT NULL,
  cidade VARCHAR(100) NOT NULL,
  estado CHAR(02) NOT NULL,
  cep VARCHAR(8) NOT NULL.
  PRIMARY KEY(idEndereco)
}

I put in the Github for future reference.

Of course there are several other ways to do this and better organize a database altogether. This is just an example, without real requirements it is difficult to do something right. Evidently I only put example fields and do not normalize the other data as cities, for example.

Of course this alone is not enough, it is possible that restrictions and triggers are needed to "ensure" consistency. And even this may not be enough because if you restrict too much in the database may prevent certain tasks of the application.

How to know what else is needed? Having real, complete, well-founded requirements helps a lot. Code the solution is the easy part, finding the requirements is what is difficult.

Actually, the modeling might be all wrong. For example, I probably wouldn’t separate what is supplier and employee, at least not for the context I’m imagining. If everyone will only have one directly related address 1 to 1, I probably wouldn’t separate it into another table. But I would break up because the relationship is different from 1 to 1.

Finally, I can improve the information of the answer if the information of the question is improved.