如何设计电影资料库?

I'm trying to get my head round this mind boggling stuff they call Database Design without much success, so I'll try to illustrate my problem with an example.

我正试图让我的头脑明白他们称之为数据库设计的那些令人费解的东西，但没有取得多大的成功，所以我将尝试用一个例子来说明我的问题。

I am using MySQL and here is my question:

我用的是MySQL，我的问题是:

Say I want to create a database to hold my DVD collection. I have the following information that I want to include:

假设我想创建一个数据库来保存我的DVD收藏。我有以下信息，我想包括:

1. Film Title
2. 电影标题
3. Actors
4. 演员
5. Running Time
6. 运行时间
7. Genre
8. 类型
9. Description
10. 描述
11. Year
12. 一年
13. Director
14. 导演

I would like to create relationships between these to make it more efficient but don't know how.

我想在这些之间建立关系，使它更有效，但不知道如何做。

Here is what I'm thinking for the database design:

下面是我对数据库设计的想法:

Films Table => filmid, filmtitle, runningtime, description

电影表=> filmid, filmtitle, runningtime, description。

Year Table => year

年表= >

Genre Table => genre

类型表= >类型

Director Table => director

董事表= >导演

Actors Table => actor_name

= > actor_name演员表

But, how would I go about creating relationships between these tables?

但是，我该如何在这些表之间创建关系呢?

Also, I have created a unique ID for the Films Table with a primary key that automatically increments, do I need to create a unique ID for each table?

另外，我为电影表创建了一个惟一的ID，它的主键自动递增，我需要为每张表创建唯一的ID吗?

And finally if I were to update a new film into the database through a PHP form, how would I insert all of this data in (with the relationships and all?)

最后，如果我要通过PHP表单向数据库更新一个新影片，我该如何插入所有这些数据(以及关系和所有这些)?

thanks for any help you can give, Keith

基思，谢谢你的帮助

10 个解决方案

#1

You have to make a distinction between attributes and entities. An entity is a thing - usually a noun. An attribute is more like a piece of describing information. In database jargon, entity = table, attribute = field/column.

必须区分属性和实体。实体是一种东西——通常是名词。属性更像是一段描述信息。在数据库行话中，entity = table, attribute = field/column。

Having a separate table for certain things, let's use director, as an example, is called normalizing. While it can be good in some circumstances, it can be unnecessary in others (as generally it makes queries more complicated - you have to join everything - and it is slower).

对于某些东西有一个单独的表，让我们使用director作为一个例子，叫做normalizing。虽然在某些情况下它是好的，但在其他情况下它可能是不必要的(通常它使查询变得更复杂——您必须连接所有内容——而且它更慢)。

In this case, having a year table is unnecessary, since there are no other attributes about a year, besides the year itself, that you would store. It is better to denormalize this and store the year in the film table itself.

在这种情况下，没有必要使用年表，因为除了年本身之外，一年之外没有其他属性可以存储。最好去规范化它，并将年份存储在电影表中。

Director, on the other hand, is different. Perhaps you'll want to store the director's first name, last name, date of birth, date of death (if applicable), etc. You obviously don't want to enter the director's birth date every time you enter a film that this person directs, so it makes sense to have a separate entity for a director.

另一方面，导演则不同。也许你想要存储导演的名字,姓名,出生日期,死亡日期(如果适用),等等。你显然不想进入导演的出生日期每次你输入一个电影这个人指导,因此为董事有一个单独的实体。

Even if you didn't want to store all this information about the director (you just want their name), having a separate table for it (and using a surrogate key - I'll get to that in a second) is useful because it prevents typographic errors and duplicates - if you have someone's name spelled wrong or entered differently (first,last vs last,first), then if you try to find other movies they've directed, you'll fail.

即使你不想存储关于董事的所有这些信息(你只是希望自己的名字),在一个单独的表(使用代理键-我会在第二个)是非常有用的,因为它阻止排版错误和重复——如果你有某人的名字拼写错误或者进入不同(第一,去年vs去年第一),然后如果你试图找到其他电影导演,你会失败。

Using a surrogate key (primary key) for tables is generally a good idea. Matching an integer is much faster than matching a string. It also allows you to freely change the name, without worrying about the foreign keys stored in other tables (the ID stays the same, so you don't have to do anything).

对表使用代理键(主键)通常是个好主意。匹配一个整数比匹配一个字符串要快得多。它还允许您*地更改名称，而不必担心存储在其他表中的外键(ID保持不变，因此您不必做任何事情)。

---

You can really take this design quite far, and it's all a matter of figuring out what you want to be able to store in it.

你真的可以把这个设计做得相当深入，而这一切都取决于你想要在其中存储什么。

For example, rather than have a single director per film, some films have multiple directors.. so there would be a many-to-many relationship between films and directors, so you'd need a table with eg:

例如，有些电影不是每部都有一个导演，而是有多个导演。所以在电影和导演之间会有多对多的关系，所以你需要有一张桌子。

films_directors => **filmid, directorid**

Taking it a step further, sometimes directors are also actors, and vice-versa. So rather than even have director and actor tables, you could have a single person table, and join that table in using a role table. The role table would hold various positions - eg, director, producer, star, extra, grip, editor.. and it would look more like:

更进一步说，有时导演也是演员，反之亦然。因此，即使没有director和actor表，也可以使用单个person表，并使用role表连接该表。角色表将包含不同的位置——例如，导演、制片人、明星、外援、控制、编辑……它看起来更像:

films => **filmid**, title, otherstuff...
people => **personid**, name, ....
roles => **roleid**, role name, ....
film_people => **filmid, personid, roleid**
genre => **genreid**, name, ...
film_genre => **genreid, filmid**

You might also have a role_details field in the film_people table, which could contain extra information depending on the role (eg, the name of the part the actor is playing).

您还可以在film_people表中有一个role_details字段，该字段可以根据角色(例如，演员所扮演的角色的名称)包含额外的信息。

I'm also showing genre as a many<>many relationship, because possible a film is in multiple genres. If you didn't want this, then instead of the film_genre table, films would just contain a genreid.

我还展示了许多不同类型的电影，因为一部电影可能有多种类型。如果你不想要这个，那么电影就会包含一个genreid，而不是film_genre表。

Once this is set up, it is easy to query and find everything a given person has done, or everything a person has done as a director, or everyone who has ever directed a movie, or all the people involved with one specific movie.. It can go on and on.

一旦设置好了，就可以很容易地查询和找到一个给定的人所做的一切，或者一个人作为导演所做的一切，或者任何一个曾经导演过一部电影的人，或者所有参与过一部电影的人。它可以一直持续下去。

#2

What follows is not actual MySQL code. It seems like what you need is more of a conceptual start here. So here's a model of what your database should look like.

下面不是实际的MySQL代码。看起来你需要的是一个概念性的开始。这是一个数据库模型。

Actor table

• id (primary key)
• id(主键)
• first name
• 第一个名字
• last name
• 姓
• etc. (any additional columns you want to store on an actor)
• 等(您想要存储在actor上的任何其他列)

Director table

• id
• id
• first name
• 第一个名字
• last name
• 姓
• etc.
• 等。

Genre table

• id
• id
• name
• 的名字
• etc.
• 等。

Film table

• id
• id
• title
• 标题
• description
• 描述
• running time
• 运行时间
• release date
• 发布日期
• director id -- this is a foreign key that refers to the id (the primary key) of the director who directed the film
• 导演id——这是一个外键，指的是导演的id(主键)
• genre id -- like the director id, this refers to the id of the genre the film belongs to
• 类型id——就像导演id一样，它指的是电影所属类型的id

Actor-film index table

• film id -- this is a foreign key that refers to the id of the film
• 影片id——这是一个外键，指的是影片的id
• actor id -- this is a foreign key that refers to the id of one actor in the film.
• id——这是一个外键，指的是影片中一个演员的id。

For each actor in the film, you would add a row to the Actor-Film Index. So, if actors 5 and 13 (the primary keys for those actors) starred in film 4 (again, the primary key for that film), you'd have two rows reflecting that fact in your index: One with film id = 4, and actor id = 5, and another with film id = 4, and actor id = 13.

对于电影中的每个演员，你都要在演员-电影索引中添加一行。所以,如果演员5和13(这些演员的主键)出演电影4(对那部电影的主键),你会有两个索引行反映了这一事实:一个与电影id = 4,和演员id = 5,和另一个电影id = 4,和演员id = 13。

Hope that helps.

希望有帮助。

Also, this assumes that each film has exactly one director. If any film in your library has two directors (such as Slumdog Millionaire), you'd want to separate out the director id from the film table, and create a Director-Film index like the Actor-Film Index as above.

此外，这还假设每一部电影都只有一位导演。如果你的库中有任何一部电影有两位导演(比如《贫民窟的百万富翁》)，你会想要将导演id从电影表中分离出来，并创建一个导演-电影索引，如上面所示。

#3

These are the tables I'd use:

这些是我要用的表格:

films (_id_, title, runningtime, description)
genres (_id_, name)
people (_id_, name, birthdate, etc...)
roles (_roleid_, rolename)
filmgenres (_filmid_, _genreid_)
castandcrew (_filmid_, _roleid_, _personid_)

Instead of having a directors and actors table, just have one table of people. This can also include crew members (in case you want to track who the 2nd Junior Assistant Dolly Grip was). Each movie can be any number of genres (comedy and horror, for example). Plus, the people can take any number of roles on each film - there are quite a number of actor/directors out there.

与其有导演和演员的桌子，不如有一个人的桌子。这也可以包括船员(如果你想要追踪第二个小助理多莉是谁)。每部电影可以是任何类型的(比如喜剧和恐怖片)。此外，人们可以在每部电影中扮演任意数量的角色——有相当多的演员/导演。

The Roles table doesn't necessarily mean the character the actor is playing, but it could. It could be "Director", "Producer", "Actor"... or even "Luke Skywalker" if you wanted to get that finely-grained... I believe IMDB does that.

角色表并不一定意味着演员正在扮演的角色，但它可以。它可以是“导演”、“制作人”、“演员”……或者“天行者卢克”，如果你想要细粒度的话……我相信IMDB会这么做。

Hopefully the names of the fields above should hint at the foreign keys, and i've put _underscores_ around the primary keys I'd use.

希望上面字段的名称应该提示外键，并且我已经在我要使用的主键周围加上_underscores_。

#4

Your Films table also needs links to the genre, director, and actors tables. Since the actors, at least will be many to many (one film will list more than one actor, one actor will be in more than one film), you'll need a table to link them.

您的电影表还需要到类型、导演和演员表的链接。因为演员，至少会有很多(一个电影会列出不止一个演员，一个演员会出现在多个电影中)，你需要一个表格来链接他们。

Films Table => filmid, filmtitle, runningtime, description, genreid, directorid
Genre Table => genreid, genre
Director Table => directorid, director
Actors Table => actorid,actor_name
FilmActor link table => actorid, filmid (with a record linking each actor to each film)

Any table that might be many to many needs a linking table.

任何可能对许多人来说都需要一个链接表的表。

#5

I have created a unique ID for the Films Table with a primary key that automatically increments, do I need to create a unique ID for each table?

我为电影表创建了一个惟一的ID，它的主键自动递增，我需要为每张表创建唯一的ID吗?

Yes, each table must have a unique id. But, that's not necessarily the primary auto incrementing key - it's whatever makes that particular instance unique. For instance, for movies, I think it's common to be title + year of release - though you'd want to check with a movie buff (a domain expert) to be sure of that. The auto increment is a fallback - basically, when you really don't have anything else to uniqueify on.

是的，每个表都必须有一个唯一的id，但这并不一定是自动递增键，而是使这个特定的实例唯一的关键字。例如，对于电影来说，我认为标题+发行年份是很常见的——尽管你想要和电影爱好者(领域专家)确认一下。自动增值是一种退步——基本上，当你真的没有任何其他东西可以独享的时候。

You may use an auto increment key for ease of use in joins and such, but you should have a unique constraint on the uniqueness fields anyway.

您可以使用自动递增键来方便地在连接中使用，但是无论如何，您应该对惟一字段有一个惟一的约束。

As for the actual design, I'd suggest something like:

至于实际的设计，我建议如下:

Films => Primary Key(filmid), Unique Constraint(filmtitle, year), 
         runningtime, description, 
         Foreign Key(Genre), Foreign Key(DirectorId)

Genre Table => Primary Key(Genre)

Director Table => Primary Key(DirectorId), DirectorName

Actors Table => Primary Key(ActorId), ActorName

Films_Actors => Primary Key(Foreign Key(ActorId), Foreign Key(FilmId))

For the insert, well - frankly, it's a PITA. You need to insert in reverse order (and this is where auto increment keys can be an even bigger PITA - if you can add date of birth or something into the Actors and Directors table, then a unique constraint can make it easier).

对于插入，坦白地说，这是皮塔。您需要以相反的顺序插入(这是自动递增键可以是更大的PITA的地方——如果您可以将出生日期或其他内容添加到角色和导演表中，那么唯一的约束可以使它变得更容易)。

So, you'd insert Actor(s), Director, Film, and then Films_Actors. Ideally, all in a single transaction. Also, I assume Genre is already filled in, and is a select list - so it doesn't need to be inserted.

你需要插入演员，导演，电影，然后是Films_Actors。理想的情况是，所有操作都在一个事务中。另外，我假设流派已经被填满了，并且是一个选择列表——所以不需要插入它。

#6

You can download Imdb schema here.

您可以在这里下载Imdb模式。

#7

I realize your question has already been answered, however I wanted to point you to:
http://www.imdb.com/interfaces

我知道您的问题已经得到了回答，但是我想指出的是:http://www.imdb.com/interface

IMDB provides flat-text files of their database (minus primary keys). You might find this useful to populate your database once you get going, or you could use it in your program/website to allow you to simply search for a movies title to add to your "DVD Collection", and have the rest of the information pulled from these.

IMDB提供数据库的平面文本文件(减去主键)。一旦开始，您可能会发现这对填充数据库非常有用，或者您可以在程序/网站中使用它，以便您可以简单地搜索电影标题，添加到“DVD集合”中，并从中提取其余信息。

#8

Sometimes actors are directors and vice versa, maybe you want a "people" table?

有时演员是导演，反之亦然，也许你想要一张“人物”表?

#9

You don't really need a YearTable, and all you need is a genre_id, director_id, and actor_id columns in your films table.

您并不需要真正的YearTable，而您所需要的只是films表中的genre_id、director_id和actor_id列。

Also, your genre, director, and actor tables need their own unique IDs.

此外，您的类型、导演和演员表需要自己独特的id。

Edit: This is, of course, assuming that you're only going to have 1 genre, director, and actor for each movie. Which probably isn't the case.

编辑:当然，这是假设你每一部电影只有一个类型，导演和演员。但事实可能并非如此。

To have many actors belonging to many movies, you will need a seperate relations table. You could call it "moviesActors" (or actorsMovies) and each row will have an actor_id and a movie_id to say this actor was in this movie.

要让许多演员属于许多电影，你需要一个独立的关系表。你可以称它为“moviesActors”(或actorsMovies)，每一行都有一个actor_id和一个movie_id来表示这个演员在这部电影中。

#10

Every table should have a primary key which is unique.

每个表都应该有一个惟一的主键。

You should read up on database normalization.

您应该仔细阅读数据库规范化。

A year table is probably unnecessary.

年表可能是不必要的。

If it's year of release, say, then the year can be stored in the film.

如果是上映的年份，那么这一年就可以被储存在电影里。

If there are multiple directors on a film, then you would have a separate table which would hold the primary key of the film table and the director table. Similarly for any of the foreign key constraints which are many-to-one or many-to-many. In particular, I believe this would apply to the Actor.

如果电影中有多个导演，那么你将有一个单独的表，它将持有电影表和导演表的主键。类似地，对于任何对多对一或多对多的外键约束。特别是，我认为这将适用于演员。