Relationship Tables

1

I have a Endereco table that serves both Funcionario and Vendor:

1 Funcionario possui 1 Endereco
1 Fornecedor possui 1 Endereco

How do I create this relationship without having inconsistencies?

    
asked by anonymous 03.03.2015 / 22:30

1 answer

2

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)
}

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

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

How do I know what else is needed? Having real, complete, well-founded requirements helps a great deal. Coding solution is the easy part, finding the requirements is that it is difficult.

It may actually be that the modeling is all wrong. For example, I would probably not separate what vendor and employee is, at least not for the context I'm imagining. If everyone will only have a 1 to 1 directly related address, I would probably not split it into another table. But it would separate by if the relationship is different from 1 to 1.

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

    
03.03.2015 / 22:41