SQL: two tables with same primary key

Question

I have two tables:

• Person (personID, name, address, phone, email)
• Player (dateOfBirth, school)

What code would I use so I could use the personID in Person as a primary key in both the Person and Player tables?

I understand that playerID wold also have to be a foreign key in Player as well. Any ideas?

Solution 1

It is not clear that you need two tables for this information, unless there are people represented who are not players. Let's assume that is the case (other people can be coaches, parents, referees, etc). Further, even though coaches were indeed born, their date of birth is not material to the system (so there's no need to transfer the date of birth back to the Person table). Also, assume that you are dealing with people who only attend one school; if they were at a different school last year, the Player record will have been updated in between the seasons. (If you need history information about schools attended in different years, you will need a different table structure.) It is also plausible to suppose that in due course you'll add more fields to the Player table.

In that case, you need to link the Player data back to the right person:

CREATE TABLE Player
(
    PlayerID     INTEGER NOT NULL PRIMARY KEY REFERENCES Person(PersonID),
    DateOfBirth  DATE NOT NULL,
    School       VARCHAR(20) NOT NULL REFERENCES School(SchoolName)
);

I'm hypothesizing that the list of schools is finite. You might use a SchoolID integer instead of the school name for joining; that tends to be more compact.

Solution 2

First you should havepersonID in the Player table as well.

Once that is done, you can have the personID as the primary key of the Player table as well or can have a separate playerID column, and this can be the primary key.

Which one you choose depends on your application's requirements. If you think a player will be associated with only one person and definitely one person then personId can be the primary key.

But in many cases you would need a separate playerId (and I suggest having one). For example if are not modeling computer games, all players may not be persons - personId will be null. Also if you model a basketball player different from a soccer player, a person who plays both these games can have multiple entries in the player table (multiple records with same personId).

Comments

[batsta13]

Yes I shortened it for simplicity in the question. There is also a playerTeam, coach, coachTeam tables all of which I was to use the personID in. Could I do what you have suggested for these tables.

[Jonathan Leffler]

Yes, you could do 'the same' for the other tables too.