关系数据库和关系表的规范化 [英] Relational Database and Normalization for Relational Tables

问题描述

我试图找出在这种情况下设置数据库的合适方法:

Im trying to figure out what the appropriate way to setup a database would be given this scenario:

我正在创建一个电影/电视数据库.一部电影可能有多种类型，而电视节目可能有多种类型.

I am creating a Movie / TV database. A movie may have multiple genres and a tv show may have multiple genres.

基本上我想知道的是您是否有电影桌、电视桌...您应该:

Essentially what I am wondering is if you have a Movie table, TV table... should you:

1. 有一个 MovieHasGenre 表，该表由 Movie 表的外键和流派值的常规字段组成

1. have a MovieHasGenre table consisting of a foreign key to the Movie table and a regular field for the genre value

或

有一个 MovieHasGenre 表和一个 Genre 表，其中 MovieHasGenre 有两个外键，一个指向 Movie 表中的 Movie，另一个指向 Genre 表中的 Genre

have a MovieHasGenre table AND a Genre table where the MovieHasGenre has two foreign keys, one pointing to the Movie in the Movie table the other pointing to the Genre in the Genre table

我真的不确定这是标准化的还是仅仅涉及偏好.我们是否担心速度问题，因为移除 Genre 表似乎少了一个连接.

Im really not sure if this is something standardized or just involves preference. Do we have concerns with speed as it seems removing the Genre table is one less join.

推荐答案

选择选项 2.

将每个流派存储一次并通过 MoveHasGenre 表对其进行引用很有用.这样，如果您有一个流派的其他属性列，您就不必在提到给定流派的每一行上冗余存储这些属性.\

It's useful to store each Genre once, and make reference to it via the MoveHasGenre table. That way, if you have other attribute columns for a genre, you don't have to store those attribute redundantly on each row where a given genre is mentioned.\

重新评论:

另一种情况是，如果您想更改流派的拼写，并将其应用于引用它的所有行，而您绝不会忘记一些.

Another case is if you want to change the spelling of a genre, and have it apply to all rows that reference it, with no chance you forget some.

这篇关于关系数据库和关系表的规范化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！