我正在编写家谱应用程序,我正在尝试弄清数据库模型的方面。
我有一个人的桌子:
create table person (
id int unsigned not null primary key
)
我有一个名称的表:
create table name (
id int unsigned not null primary key,
person_id int unsigned not null,
first_name varchar(191),
last_name varchar(191),
foreign key (person_id) references person (id)
)
一个人可以有多个名称,因为有时他们在不同的人口普查和其他记录中具有不同的名称。
但是一个人也有一个特定的名称,我们默认情况下应该显示。
我的第一个想法是将primary
列添加到表name
:
create table person (
id int unsigned not null primary key
)
create table name (
id int unsigned not null primary key,
person_id int unsigned not null,
first_name varchar(191),
last_name varchar(191),
`primary` tinyint(1) not null default 0,
foreign key (person_id) references person (id)
)
这有一个数据整合问题。属于一个人的多个名称可以具有primary
标志集。
我想到的另一种方式是在person
表中添加primary_name_id
列。这可以链接回name
表中的主要名称:
create table person (
id int unsigned not null primary key,
primary_name_id int unsigned default null,
foreign key (primary_name_id) references name (id)
)
create table name (
id int unsigned not null primary key,
person_id int unsigned not null,
first_name varchar(191),
last_name varchar(191),
foreign key (person_id) references person (id)
)
这也存在完整性问题。primary_name_id
可能指向属于另一个人的名称行。另外,它要求primary_name_id
列是可替代的,因为当一个人首次创建时,他还没有名字。
我考虑这样做的第三种方式是在人录音中包括"名称"字段的重复项:
create table person (
id int unsigned not null primary key,
first_name varchar(191),
last_name varchar(191),
)
create table additional_name (
id int unsigned not null primary key,
person_id int unsigned not null,
first_name varchar(191),
last_name varchar(191),
foreign key (person_id) references person (id)
)
这似乎也不是理想的解决方案。类似的数据被存储在两个地方。同样,更改一个人的主要名称也需要一些工作。我必须插入一个新的附加_name,更新该人并删除旧名称。
有更好的方法吗?
您的第二种方法基本上是正确的方法。但是,您想确保名称是给该人的。所以:
create table person (
id int unsigned not null primary key,
primary_name_id int unsigned default null,
foreign key (id, primary_name_id) references name (person_id, id)
);
create table name (
id int unsigned not null primary key,
person_id int unsigned not null,
first_name varchar(191),
last_name varchar(191),
foreign key (person_id) references person (id),
unique (person_id, id);
);
unique
约束有点冗余,但是它可以确保您的值匹配两个表 - 适当。