mysql - 将一条且仅一条记录标记为 "Primary"

Question

我正在编写一个谱系应用程序，我正在尝试弄清楚我的数据库模型的各个方面。

我有一张供人们使用的 table :

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)
)

一个人可以有多个名字，因为有时他们在不同的人口普查和其他记录中有不同的名字。

但是一个人也有一个特定的名字，我们应该默认显示它。

<小时/>

我的第一个想法是向表name添加一个primary列:

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)
)

这似乎也不是一个理想的解决方案。类似类型的数据存储在两个地方。更改一个人的主要姓名也需要一些工作。我必须插入一个新的additional_name，更新此人，并删除旧的附加名称。

<小时/>

有更好的方法吗？

Answer 1

你的第二种方法基本上是正确的方法。但是，您要确保该名字是针对该人的。所以:

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 约束有点冗余，但它可以让您确保两个表中的值正确匹配。