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?
我将使用什么代码,因此我可以将Person中的personID用作Person和Player表中的主键。我知道playerID wold也必须是Player中的外键。有任何想法吗?
4 个解决方案
#1
2
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.
我假设学校名单是有限的。您可以使用SchoolID整数而不是学校名称来加入;这往往更紧凑。
#2
1
First you should havepersonID in the Player table as well.
首先,你也应该在Player表中拥有personID。
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.
完成后,您可以将personID作为Player表的主键,也可以具有单独的playerID列,这可以是主键。
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.
您选择哪一个取决于您的应用程序的要求。如果您认为某个玩家只与一个人相关联,而且肯定只有一个人,则personId可以成为主键。
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).
但在许多情况下,你需要一个单独的playerId(我建议有一个)。例如,如果不是建模计算机游戏,则所有玩家可能不是人 - personId将为null。此外,如果您对与足球运动员不同的篮球运动员进行建模,则同时进行这两种运动的人可以在运动员表中具有多个条目(具有相同personId的多个记录)。
#3
0
Could you add a personID field and manage it the same way you manage the person table? Each time you insert a player, you would have to insert the corresponding person with the same id.
你能否像管理人员表一样添加personID字段并进行管理?每次插入播放器时,都必须插入具有相同ID的相应人员。
of course if your person concept is abstract then it should definetly be in the same table
当然,如果你的人的概念是抽象的,那么它应该完全在同一个表中
#4
0
I suggest that you move the date of birth field from the 'Player' table to the 'person' table (after all, a person doesn't have multiple dates of birth!), then add the 'personID' field to the 'player' table. If a person has multiple schools (primary, middle high, high), then the primary key of the 'player' table should be personID + school.
我建议你将出生日期字段从'玩家'表移动到'人'表(毕竟,一个人没有多个出生日期!),然后将'personID'字段添加到'玩家' '表。如果一个人有多个学校(小学,中高,高),那么'玩家'表的主键应该是personID + school。
There is definitely no problem in having one table's primary key serve as the primary key in a second table; such a second table could help save disk space and improve access time. Let's say that there is a large text field to be stored for some - but not all - people. Storing this field in a secondary table (with the same primary key) would mean that only people that need this field would have records in the secondary table.
将一个表的主键用作第二个表中的主键肯定没有问题;这样的第二个表可以帮助节省磁盘空间并缩短访问时间。假设有一些大型文本字段要存储给某些人 - 但不是所有人 - 。将此字段存储在辅助表中(使用相同的主键)将意味着只有需要此字段的人才会在辅助表中拥有记录。
#1
2
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.
我假设学校名单是有限的。您可以使用SchoolID整数而不是学校名称来加入;这往往更紧凑。
#2
1
First you should havepersonID in the Player table as well.
首先,你也应该在Player表中拥有personID。
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.
完成后,您可以将personID作为Player表的主键,也可以具有单独的playerID列,这可以是主键。
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.
您选择哪一个取决于您的应用程序的要求。如果您认为某个玩家只与一个人相关联,而且肯定只有一个人,则personId可以成为主键。
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).
但在许多情况下,你需要一个单独的playerId(我建议有一个)。例如,如果不是建模计算机游戏,则所有玩家可能不是人 - personId将为null。此外,如果您对与足球运动员不同的篮球运动员进行建模,则同时进行这两种运动的人可以在运动员表中具有多个条目(具有相同personId的多个记录)。
#3
0
Could you add a personID field and manage it the same way you manage the person table? Each time you insert a player, you would have to insert the corresponding person with the same id.
你能否像管理人员表一样添加personID字段并进行管理?每次插入播放器时,都必须插入具有相同ID的相应人员。
of course if your person concept is abstract then it should definetly be in the same table
当然,如果你的人的概念是抽象的,那么它应该完全在同一个表中
#4
0
I suggest that you move the date of birth field from the 'Player' table to the 'person' table (after all, a person doesn't have multiple dates of birth!), then add the 'personID' field to the 'player' table. If a person has multiple schools (primary, middle high, high), then the primary key of the 'player' table should be personID + school.
我建议你将出生日期字段从'玩家'表移动到'人'表(毕竟,一个人没有多个出生日期!),然后将'personID'字段添加到'玩家' '表。如果一个人有多个学校(小学,中高,高),那么'玩家'表的主键应该是personID + school。
There is definitely no problem in having one table's primary key serve as the primary key in a second table; such a second table could help save disk space and improve access time. Let's say that there is a large text field to be stored for some - but not all - people. Storing this field in a secondary table (with the same primary key) would mean that only people that need this field would have records in the secondary table.
将一个表的主键用作第二个表中的主键肯定没有问题;这样的第二个表可以帮助节省磁盘空间并缩短访问时间。假设有一些大型文本字段要存储给某些人 - 但不是所有人 - 。将此字段存储在辅助表中(使用相同的主键)将意味着只有需要此字段的人才会在辅助表中拥有记录。