问题描述
我试图让我的头脑圆满的这个令人难以置信的东西，他们称之为数据库设计，没有太多的成功，所以我会尝试用一个例子说明我的问题。 p>我正在使用MySQL，这里是我的问题：
说我想创建一个数据库来保存我的DVD收藏。我有以下信息，我想包括：
- 电影标题
- 演员
- 运行时间
- 类型
- 描述
- 年份
- 导演
我想创建这些之间的关系，使其更有效率，但不知道如何
这是我正在想的数据库设计：
Films Table =>
年份表=>年
类型表=>类型
导演表=>导演
演员表=> actor_name
但是，如何创建这些表之间的关系？ >
此外，我已经为Films Table创建了一个唯一的ID，主键自动增量，我需要为每个表创建一个唯一的ID吗？
最后，如果我要通过一个PHP表单将新的电影更新到数据库中，那么我将如何将所有这些数据插入（与关系和所有？）
感谢您提供的任何帮助，
Keith
你必须区分属性和实体。一个实体是一个东西 - 通常是一个名词。属性更像是一条描述信息。在数据库术语中，entity = table，attribute = field / column。
对于某些事物，有一个单独的表，让我们使用director，例如称为normalizing。虽然在某些情况下可能会很好，但在其他情况下可能是不必要的（通常会使查询更复杂 - 您必须加入所有内容，而且速度较慢）。在这种情况下，不需要一张年表，因为一年以外没有其他属性，除了年份本身，您将存储。最好把这个规范化，把年份存放在电影表里。另一方面，导演是不同的，
也许你想要存储导演的名字，姓氏，出生日期，死亡日期（如果适用）等等。你显然不想在每次进入电影时输入导演的出生日期指导，所以有一个单独的实体为董事是有道理的。
即使您不想存储有关导演的所有信息（您只是想要他们的名字），有一个单独的表（并使用代理键 - 我会在一秒钟内得到）是有用的，因为它可以防止排版错误和重复 - 如果你有某人的名字拼写错误或输入不同（第一，最后一个vs最后，第一），那么如果你试图找到其他电影，他们你已经指示了，你会失败。
使用表格的代理键（主键）通常是一个好主意。匹配整数比匹配字符串要快得多。它还允许您自由更改名称，而不必担心存储在其他表中的外键（ID保持不变，因此您不必执行任何操作）。
你真的可以把这个设计搞得很远，这一切都是为了弄清楚你想要的存在里面
例如，每部电影不是有一位导演，有些电影有多个导演，所以电影和导演之间会有多对多的关系，所以你需要一张表格，例如：
films_directors => **电影，导演**
进一步说，有时候导演也是演员， -versa因此，您可以拥有一个单独的表，而不是使用导演和演员表，并使用角色表加入该表。角色表可以持有不同的职位 - 例如导演，制作人，明星，额外，掌握，编辑器。它的外观更像：
films => **电影**，标题，其他...
人=> ** personid **，name，....
roles => ** roleid **，角色名称，...
film_people => ** filmid，personid，roleid **
genre => ** genreid **，name，...
film_genre => ** genreid，filmid **
您还可以在film_people表中添加一个role_details字段，包含额外的信息取决于角色（例如，演员所在部分的名称）。
我还将类型显示为许多关系，因为可能有一种电影是多种类型的。如果你不想要这个，那么电影只会包含一个genreid，而不是film_genre表。
一旦这样设置，查询和查找给定人员完成的任何事情，或任何人作为导演完成的任何事情，或所有曾经指导一部电影，或所有参与一个特定电影的人..可以继续。
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:
Say I want to create a database to hold my DVD collection. I have the following information that I want to include:
- Film Title
- Actors
- Running Time
- Genre
- Description
- Year
- Director
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
Year Table => year
Genre Table => genre
Director Table => director
Actors Table => 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?
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?)
thanks for any help you can give,Keith
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.
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).
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.
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).
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:
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).
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.
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.
这篇关于如何设计电影数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！