Specialization multiple foreign key

Question

Specialization multiple foreign key

Navigation

#1 by (0 votes)

0

Hello, I am modeling a database and I came across the following problem: I have a table called time, which is a generalization for 4 specialized tables, where each one will contain a different id, to know which team belongs each player. Next image:

ThenwhenIgeneratethelogicalmodelIgetthefollowingresult:

Threetablesplayer,times...andsponsor,andtheproblemisinthesponsortablethatreceivesastheforeignkeytheidoftheteam,Idonotwantthesponsortosponsorateam'n'andyesthe'teams+time1+time2..."all time then I do not know how to do because at the time of inserting in the physical model there is no single identifier that identifies all teams, just the id of each team: time1 id = 1 time2 id = 2 time3 id = 3 time4 id = 4

Physical model:

CREATE TABLE times+time1+time2+time3+time4 (
nome Text,
id int PRIMARY KEY,
f1 Text,
f2 Text,
f3 Text,
f4 Text
)

CREATE TABLE patrocinador (
nome Text,
valor Text,
cnpj int PRIMARY KEY,
id Text,
id Text,
FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

CREATE TABLE jogador (
cpf int PRIMARY KEY,
sobrenome Text,
nome Text,
lider Text,
time Text,
id int,
FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

If someone knows how to solve? Thanks in advance for your attention!

mysql mysql-workbench

asked by anonymous 13.02.2018 / 06:08

1 answer

Error Page Dashboard PHP - Cache limiter session start [duplicate] Query sql SELECT

score 0 · Answer 1

If you create the tables in this way:

Adding the FOREIGN KEY of sponsor to the TIMES table, you start to have a UM-PARA-UM sponsor relationship, meaning each team will have a sponsor.

CREATE TABLE times+time1+time2+time3+time4 (
nome Text,
id int PRIMARY KEY,
f1 Text,
f2 Text,
f3 Text,
f4 Text,
ID_PATROCINADOR int NOT NULL,

CONSTRAINT PK_TIMES FOREIGN KEY (ID_PATROCINADOR) REFERENCES patrocinador (id)
)

If you need to know, which TIME PATROCINADOR is sponsoring, it will be required by a FOREIGN KEY in sponsor too, which will be a UM-PARA-MUITOS relationship, ie a sponsor can sponsor many teams. p>

CREATE TABLE patrocinador (
nome Text,
valor Text,
cnpj int PRIMARY KEY,
id Text,
id Text,

FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

CREATE TABLE jogador (
cpf int PRIMARY KEY,
sobrenome Text,
nome Text,
lider Text,
time Text,
id int,
FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

I would like to give you some tips on how to create TABELAS :

When creating table names, try to name the entity in the plural, for example: TIMES, PATROCINADORES, JOGADORES, etc.

When you create the CONSTRAINTS try to name them to make it easy for you to find when there is a problem, for example:

CREATE TABLE patrocinador (
nome Text,
valor Text,
cnpj int ,
id Text,
id Text,

CONSTRAINT PK_JOGADORES FOREIGN KEY (cnpj),
CONSTRAINT FK_PATROCINADOR_TIMES FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

CREATE TABLE jogador (
cpf int,
sobrenome Text,
nome Text,
lider Text,
time Text,
id int,
insira o código aqui
CONSTRAINT PK_JOGADORES PRIMARY KEY (ID),
CONSTRAINT FK_JOGADORES_TIMES FOREIGN KEY( id) REFERENCES times+ time1+time2+time3+time4 (id)
)

Creating CONSTRAINT's giving their names makes it easier to solve future errors.