数据库设计
我正在建立一个音乐流媒体网站，用户可以在其中购买和流媒体播放 mp3。我有一个子集实体图，可以描述如下：
我想将数据标准化为 3NF。我需要多少张桌子？显然我想避免包括部分依赖性，这将需要更多的表而不仅仅是专辑、艺术家、歌曲 - 但我不确定还需要添加什么？根据经验有什么想法吗？
I am building a music streaming site, where users will be able to purchase and stream mp3's. I have a subset entity diagram which can be described as follows:
I want to normalise the data to 3NF. How many tables would I need? obviously I want to avoid including partial dependancies, which would require more tables than just album, artist, songs - but I'm not sure what else to add? Any thoughts from experience?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
好吧，你已经完成了 ER 级别。您需要先确定键和属性，然后才能计算出功能依赖性。在达到 3NF 之前，还有大量工作要做。例如。歌曲标题重复。
另外，还有一些问题：
无论如何，这是一个已解决的▶实体关系图◀< /strong>，至少对于所提供的信息而言。它比 3NF 更接近 5NF，但我不能这么声明，因为它不完整。
不熟悉关系数据库建模标准的读者可能会发现▶IDEF1X 符号◀ 很有用。
它使用简单的超类型-子类型结构，即正交设计原理。出售的物品，即专辑或歌曲。
请随意提出澄清问题。
Well, you've done the ER level. You need to identify Keys and Attributes before you can work out Functional Dependencies. There is a fair amount of work to do before you get to 3NF. Eg. Song Titles are duplicated.
Also, there are questions:
Anyway, here is a resolved ▶Entity Relation Diagram◀, at least for the info provided. It is closer to 5NF than 3NF, but I cannot declare it as such, because it is not complete.
Readers who are unfamiliar with the Standard for Modelling Relational Databases may find ▶IDEF1X Notational◀ useful.
It uses a simple Supertype-Subtype structure, the Principle of Orthogonal Design. The Item that is sold ie either an Album xor a Song.
Feel free to ask clarifying questions.
您将需要 4 个表：艺术家、歌曲、专辑和专辑歌曲。
最后一首是必需的，因为完全相同的歌曲（=相同的编辑/版本...）可能包含在多个专辑中，因此您有一个点对点的关系。
You will need 4 tables: Artists, Songs, Albums, and AlbumSongs.
The last one is required since the exact same song (=same edit/version...) could be included in several albums, so you have there a m-to-m relationship.
我同意 iDevelop，但需要 1 个额外的表。这是我的建模方法。
表：Artist、Song、Album、AlbumSongMap、SingleInfo
如果歌曲在不同日期作为单曲发行，您可以从 SingleInfo 获取该信息。该单曲发行时可能带有一些与专辑艺术不同的封面艺术。您可以将单曲艺术存储在 SingleInfo 中。也许一首歌可以作为单曲多次发行，并带有新的封面艺术或其他东西，因此它可能是一对多的关系。否则就是1-1。
如果您可以将歌曲与 SingleInfo 结合在一起，则意味着它已作为单曲发布。如果您可以将歌曲与专辑连接起来（使用地图），那么您将找到该专辑下发布的所有专辑。
对老歌进行数字增强就是一首新歌。 （或者至少是不同的二进制文件）。您可能希望进一步规范化歌曲以允许存储数字增强功能而无需复制歌曲名称等。
I agree with iDevelop but with 1 extra table. Here is how I would model it.
Tables: Artist, Song, Album, AlbumSongMap, SingleInfo
If the song was a released as a single on a different date, you can get that from SingleInfo. The single may have been released with some cover art that is different from the album art. You would store the singles art in SingleInfo. MAYBE a song can be released as a single multiple times, with new cover art or something so it could possibly be a 1-many relation. Otherwise it is 1-1.
If you can join Song with SingleInfo that means it was released as a single. If you can join Song with Album (using the map) then you will find all the album's it was released under.
A digital enhancement to an old song is a new song. (or at least a different binary). You may want to further normalize Song to allow storage of digital enhancements without duplicating songName, etc.
当您从 ER 建模切换到关系建模（表）时，每个实体都需要一个表。您还需要一个用于某些关系的表。
在您给我们的图表中，这两种关系都是多对一的。多对一关系不需要表。您可以通过向实体表添加外键来避免这种情况。因此，你的问题的答案是 3 个表：艺术家、专辑和歌曲。
但是，我质疑你的 ER 图。在我看来，“包含”的关系确实是多对多。一张专辑显然包含很多歌曲。但一首特定的歌曲可以出现在不止一张专辑中。因此，连接“包含”和“专辑”的线上应该有一个箭头。
如果您接受对 ER 模型的此修订，则表的数量将增加到 4 个：艺术家、专辑、歌曲和包含。
对于艺术家和歌曲也可以提出类似的论点。如果两位艺术家合作创作一首歌曲（例如多莉·帕顿和肯尼·罗杰斯一起演唱“溪流中的岛屿”，
那么您可能想要将“生产”建模为多对多关系。现在您需要 5 个表：Artists、Albums、Songs、Contains 和 Produces。
艺术家、专辑和歌曲将需要一个标识相应实体的 PK。实体完整性要求实体实例和表行之间是一一对应的。
Contains 和 Produces 表可以在没有单独的 Id 属性的情况下构建。每个表中都需要一对 FK，并且您可以为包含这两个 FK 的每个表声明一个复合 PK。
引用完整性要求您在程序中或通过在数据库中声明引用约束来强制 FK 引用的有效性。我强烈喜欢在数据库中声明约束。
When you switch over from ER modeling to relational modeling (tables), you need one table for each entity. You also need a table for some relationships.
In the diagram you've given us, both relationships are many to one. Many to one relationships do not require a table. You can get away with adding foreign keys to entity tables. Therefore the answer to your question is 3 tables: Artists, Albums and Songs.
However, I question your ER diagram. It seems to me that the "contains" relationship is really many to many. An album clearly contains many songs. But a given song can appear on more than one album. So there should be an arrowhead on the line that connects "contains" to "album".
If you accept this revision to your ER model, then the number of tables increases to 4: Artists, Albums, Songs, and Contains.
A similar argument might be made for Artist and Song. If two artists collaborate on a single song, (e.g. Dolly Parton and Kenny Rogers singing "Islands in the Stream" together,
then you might want to model "produces" as a many to many relationship. Now you need 5 tables: Artists, Albums, Songs, Contains and Produces.
Artists, Albums, and Songs are going to require a PK that identifies the corresponding entity. Entity integrity demands that the correspondence bewteen entity instances and table rows be one-to-one.
The Contains and Produces tables can be built without a separate Id attibute. You will need a pair of FKs in each of these tables, and you can declare a compound PK for each table consisting of the two FKs.
Referential integrity demands that you enforce the validity of FK references, either in your programs or by declaring a references constraint in the DB. I strongly prefer declaring the constraint in the DB.