MySQL模式设计：一个带类型列的表VS多个表

Question

我是一个关于电影的网站，希望能够建立一种电影和演职人员（导演、编剧和演员）之间的关系。我有两种可能的实现方法，第一种是创建每个演员的表格：

CREATE TABLE director(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));  
CREATE TABLE writer(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));  
CREATE TABLE actor(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50));

并在每个表与电影表之间建立多对多的关系。
第二种可能性是创建一个演职人员表，其中包含一个类型列，可以引用导演、编剧或演员，并在该表与电影表之间建立多对多的关系。

CREATE TABLE cast(id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), type varchar(10));

注意：对于演员，自动生成的表格将有一些额外的列：角色名称、角色... 那么，对于这种情况，哪种可能性更好呢？

- K. Ayoub

---

我会选择第二种方法，这样更容易管理、添加新类型，并且减少了搜索数据的表格数量。 - Clint

补充@Clint的评论，我还会创建一个类型表cast_type，并在演员表中使用cast_type_id列...当演员表很大时，搜索整数比搜索varchar要快得多..此外，整数数据类型使表数据和索引大小更小，也使搜索更快...此外，类型表不会变得那么大.. - Raymond Nijland

Answer 1

这两种方法都忽略了一个事实，演员、导演或编剧等通常是人。这才是实际的实体。在电影中担任的角色（演员、编剧等）实际上是一种关系属性。

这两种方法都存在冗余。如果某个人在电影中担任多个角色，比如导演和演员（想想希区柯克的客串），那么就会有两条记录。第一种方法将它们分布在两个表中，而第二种方法则在一个表中。人的属性（姓名等）会被存储两次，可能会相互矛盾，或者需要在两个地方进行更改等。

因此，我建议采用第三种方法：创建一个人员表，存储每个人可以拥有的所有属性（姓名等）。再创建一个链接人员和电影的表，同时指示人在电影中担任的角色（演员、导演等）。

最好还要为角色创建一个表。

例如：

CREATE TABLE people
             (id integer AUTO_INCREMENT,
              name varchar(50),
              PRIMARY KEY (id));

CREATE TABLE roles
             (id integer AUTO_INCREMENT,
              name varchar(10),
              PRIMARY KEY (id));

CREATE TABLE movies_people
             (movie integer,
              person integer,
              role integer,
              PRIMARY KEY (movie,
                           person,
                           role),
              FOREIGN KEY (movie)
                          REFERENCES movies
                                     (id)
                          ON DELETE CASCADE,
              FOREIGN KEY (person)
                          REFERENCES people
                                     (id),
              FOREIGN KEY (role)
                          REFERENCES roles
                                     (id));

- sticky bit

Answer 2

我会选择第二种方法。这甚至符合标准SQL规则。

如果您的类型转换（导演，编剧等）还具有其他属性（列），则可以进行一些修改。在这种情况下，建议将它们作为主表的子表。

在您的情况下，主表将是“cast”。它将具有ID和其他列。其他表将被创建，并对应于不同的演员，如“director”，“writter”等。然后在每个子表之间与主“cast”表进行1-1关系。关系在子表上是强制性的（意味着例如“director”在创建时必须具有与“cast”的关系）。在关系上，外键对于“director”也将是其主键，并且它将涉及到“cast”的主键。

推荐使用此方法，因为不仅可以向不同的演员添加其他列，还可以添加其他关系，以便在以后扩展数据库时使用。您还可以添加“cast”的其他子表，而不需要更改相对于“movies”表的结构。

- Fikret Basic

---

@RaymondNijland 我建议的方法是可扩展和灵活的，方便后续扩展。如果他只想保留演员类型，并且知道将来不需要为每种类型添加额外的唯一列，那么当然他不需要每个单独的表。 - Fikret Basic

我知道... 我是在强调“我会选择第二种方法。它甚至符合标准SQL规则。”.. “标准SQL”指的是ANSi SQL具有类型表和超级/子表或更好地说表继承（https://en.wikipedia.org/wiki/Structured_type），这也很有意义。在MySQL中，你不能使用ANSI SQL 1999，但可以模拟实现。 - Raymond Nijland

Answer 3

第二种方法似乎比第一种更干净。不需要维护3个不同的表，您只需在一个列中获取type即可。

此外，您可以使用bit（0=导演，1=作家，2=演员）而不是varchar（10）来存储type。

- Rishikesh Dhokare

---

我建议避免使用数据类型BIT...MySQL支持BIT数据类型，但仅作为TINYINT（1）的同义词。在MySQL 5.0.3版本中引入了原生的BIT数据类型。这种类型的行为与TINYINT非常不同...尽管文档将其列为“数值类型”，但数据似乎以二进制值的形式存储，因此在某些情况下查询可能会很棘手。 - Raymond Nijland