1
Hello, I am modeling a database and I came across the following problem: I have a table called team, which is a generalization to 4 specialized tables, where each will contain a different id, to know to which team each player belongs. Follow the image:
So when I test the logical model I get the following result:
Three player tables, teams... and sponsor, and the problem is in the sponsor table that receives as a foreign key the id of the team, I do not want the sponsor to sponsor an 'n' team but the "times+time1+time2..." every team so I don’t know how to do it because when entering the physical model there is not a unique identifier that identifies all teams, only 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)
)
Does anyone know how to fix it? Thanks in advance for your attention!
only you remove the foreign key from the Sponsors table, and add a foreign one in times+time2+... with sponsor ID, so you have a sponsor for all teams.
– Paulo Ricardo
Your model is almost correct. The table
times+time1+time2+time3+time4
has a primary key for each set of 4 times. When you declaretimes+time1+time2+time3+time4.id
as a foreign key topatrocinador
, you unite one sponsor to one set of teams and at the same time, one set of teams to several sponsors. The only mistake I see is that in the tablepatrocinador
, has two fields namedid
.– Marcelo Shiniti Uchimura