英文:
How to reference these tables in an ER Diagram?
问题
一个大学作业要求我为一个音乐平台数据库设计ER图。作业要求用户能够在平台上评论歌曲和专辑。我已经设计了以下模型，将用户，评论，歌曲和专辑作为独立的实体。然而，我不确定如何引用被评论的标题，因为它既可以是一首歌曲，也可以是一张专辑，并且它们的ID在不同的表中。同时提及两者会导致其中一个成为NULL属性。
此外，每首歌都可以是在一张专辑中或不在。不确定如何引用这两者，因为这是一个条件语句。
如果您能提供任何帮助，将不胜感激！
我考虑过将歌曲和专辑都变成录音实体的子类型，但由于它们必须相互关联，这似乎是一种不好的实践方式。
英文:
A college assignment requires me to design an ER Diagram for a music platform database. The assignment states that users are able to review songs and albums on the platform. I've designed the following model stating USERS, REVIEWS, SONGS and ALBUMS as separate entities. However, I’m not sure how to reference the title that is being reviewed, given that it’s either a song or an album, and that their IDs are in separate tables. Mentioning both would result that one of them ends up a NULL attribute.
Additionally, each song can either be on an album or not. Not sure how to reference those two either, since it's a conditional statement.
Any help you can offer is appreciated!
I’ve thought about making both SONGS and ALBUMS into subtypes of a RECORDINGS entity but since they have to be related to each other it feels like a bad practice to specialize them.
答案1
得分: 1
这种情况没有"完美"的建模方式，如何建模取决于你需要根据这些实体在你的模型中的使用情况来做出的判断 - 尝试研究超类型和子类型以获取更多信息。
基本上有3种建模方式：
- 如你所建模的那样，但是评论（Reviews）有外键（FK）同时指向歌曲（Song）和专辑（Album）。
- 创建一个包含歌曲和专辑的共同属性的Recording实体。你的评论（Review）将有一个指向Recording的外键（FK），而Recording将有单独的外键（FK）分别指向歌曲和专辑。
- 创建一个包含歌曲和专辑所有属性的Recording实体。你的评论（Review）将有一个指向Recording的外键（FK），而Recording将有一个自引用外键（FK），用于将歌曲链接到专辑。
确保为任何给定的评论填充正确的属性将需要应用程序逻辑；我认为你不能纯粹依靠数据模型设计来实现它。
英文:
There is no "perfect" way to model this scenario and how you model it is a judgement call you need to make based on how these entities are going to be used in your model - try researching supertypes and subtypes for more information.
There are basically 3 ways to model this:
- As you've modelled it but Reviews has FKs to both Song and Album.
- Create a Recording entity that contains the common attributes for both Song and Album. Your Review would have an FK to Recording and Recording would have separate FKs to Song and Album
- Create a Recording entity that includes all attributes for both Songs and Albums. Your Review would have an FK to Recording and Recording would have a self-referential FK to link songs to albums
Ensuring the the correct attributes are populated for any given review would require application logic; I don't believe you can implement it purely in a data model design
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论