Hello,

I have a database with these tables:

COMPETITIONS
IdCompetition (PK)
Name
...

TEAMS
IdTeam (PK)
Name
...

TEAMS_COMPETITIONS
IdTeam (PK)
IdCompetition (PK)
(FK1 - IdTeam to table TEAMS)
(FK2 - IdCompetition to table COMPETITIONS)

TROPHIES
IdTrophy (PK)
IdCompetition
IdTeam

The question is what would be the foreign key on the TROPHIES table?
a)
Have 2 foreign keys, one IdCompetition to the table COMPETITIONS
The other one IdTeam to the table TEAMS

b) Have only one foreign key:
IdCompetition, IdTeam to the table TEAMS_COMPETITION

Any idea which one is the correct???

My assumption is that a team cannot win a trophy when it did not participate in the corresponding competition.
So I'd say a single FK needs to go to TEAMS_COMPETITIONS

Can you have more than one trophy per competition? If no, then the artificial PK on TROPHIES is not needed as well. But then the question comes up, what else do you want to store in the TROPHIES table that couldn't be stored the TEAMS_COMPETITIONS table (and maybe the competitions table)

A Team can't win a trophy unless is playing in a competition, you are right.

I create a Trophies table because one team can win several trophies in the same competition and also there could be several trophies per competiton.

Thanks

Ok, then the separate PK for the trophies table does make sense.

You mean the one I have just one FK?

Yes. Keep IdTrophy and make (IdCompetition, IdTeam) a FK to TEAMS_COMPETITIONS

Foreign key(FK) is completely based on the data you want to "Retrieve"......

When it comes to the case of "Trophies" then obviously you would want to know which team has won the Trophy....

So "Teams" idTeam(PK) would be the foreign key in "TROPHIES" table idTeam(FK)....

i think i know what you wanted to convey with this statement, but it did not succeed

more correct would be to say that the FK is completely based on the data you want to integrificate

in other words, FKs are about Referential integrity

Integrificate ???????

yes, integrificate (verb, transitive): to cause [a schema] to have relational integrity