6

So I'm doing a bit of practise in database design, and a question arose that I couldn't figure out how to correct.

I'm pretty new at this, so I'm not the best but here it goes.

So I want to create two different tables, one called Team, and one called Player. These tables are pretty different from one another.

I then want another table called Challenge, which I want to reference either Team or Player.

Basically there are competitions, and they are either competed individually, or as a team. I want a foreign key to reference Team, if its a team challenge, or Player if its individual.

I can't combine the two tables, as they contain very different elements.

Im just a bit confused as to how to do it. Can I have just one foreign key, that will reference one table or another. Or shall I have two, with one null.

Or can I add another ID key in the Team and Player. And then in Challenge if a new Type key indicated its a group, it'll reference Team, and if its individual, it references Player.

Again, pretty new at this, so hope I made sense.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user3088511
  • 61
  • 1
  • 1
  • 2
  • You cannot foreign key one column to two tables, no. – John Gibb Dec 10 '13 at 21:20
  • 1
    One workaround would be have a Competitor table that has a row for both Team and Player. So now you have a CompetitorId to foreign key to for Challenge, and both Team and Player foreign key back to Competitor. Probably not worth the hassle though. – John Gibb Dec 10 '13 at 21:21

1 Answers1

2

You could add two columns in challenge: PlayerID & TeamID, which both can be NULL.

PlayerID references Player.id
TeamID references Team.id

example, a challenge has a player:

  PlayerID  = 14
  TeamID = NULL

Note that the referenced columns (Player.id & Team.id) must be defined as NOT NULL, since they are referenced from table challenge

manx
  • 21
  • 3