如何设计引用多个（至少 4 个）表的媒体表？
我正在为我的食谱设计一个数据库。我在设计中创建了多个表格：书籍、作者、食谱、成分，对于所有这些项目，我想将媒体（图像或视频）链接到所有这些表格中的项目。
我正在考虑这样的设计：
media_id,
rid (primary key of foreign table),
rtype (1=book, 2=author, 3=recipe, 4=ingredient),
media_type(1=image,2=video),
media_url
但是我如何确保关系完整性？
谢谢
I am designing a database for my cookbooks. I have created multiple tables in my design: books, authors, recipes, ingredients and for all these items I want to link media (images or video) to items in all these tables.
I was thinking of a design like:
media_id,
rid (primary key of foreign table),
rtype (1=book, 2=author, 3=recipe, 4=ingredient),
media_type(1=image,2=video),
media_url
But how will I ensure relational integrity?
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您提出的设计似乎暗示每个实体（书籍、作者等）可以拥有多个媒体文件，因此为了保持关系完整性，我将为每个关系拥有单独的联结表。
Your proposed design seems to imply that each entity (book, author, etc.) can have multiple media files, so to maintain relational integrity, I'd have separate junction tables for each relationship.
如果每个表只有 1 个媒体项，则 media_id 应该位于表中，而不是相反。
如果可能有多个媒体项，您应该将它们链接到一个额外的表中。每个项目应该有一个额外的表（例如 bookid_mediaid）。
如果您认为它应该在一个表中链接，那么您实际上是在声明这些项目至少有一些共同点。否则，根据类型的不同，rid 在整个记录中将具有不同的含义，而这在关系理论中是不可能的。
结论：
你的设计不好。您应该为每个实体建立一个关系，或者找到所有实体的共同点并使用它来链接到您的媒体类型。
If there's only 1 media-item for each table, the media_id should be in the tables in stead of the other way around.
If several media-items are possible you shoud link them within an extra table. There should be an extra table per item (bookid_mediaid for example).
If you think it should be linkable within one table, you are actually stating that those items have at least something in common. Otherwise the rid would have different meaning throughout the records, depending on type, and that's not possible in relational theory.
Concluding :
Your design is not good. Either you should have a relationship per entity or find what's common for all entities and use that to link to your mediatypes.
Matthijs，
对于关系设计，您必须了解这些对象如何相互关联，然后决定数据模型。这是初稿和我假设的条件。您可能对您的案例有不同的规则。发布它们，有人应该能够发布准确的模型。
如果媒体与一本且仅一本书相关，您将有一个不同的表，例如带有以下列的媒体。 BOOK_ID 是与该媒体关联的父列。
如果给定的图像/视频可以与多本书相关联，并且每本书可以有不同的图像，那么您将拥有不同的关联实体..类似于..
Matthijs,
For a relational design, you have to look at how these objects are related to each other and then decide on the data model. Here is an initial draft and the conditions that I am assuming. You might have different rules for your case.. post them and someone should be able to post an accurate model.
If the media is related to one and only one book, you'll have a different table , say, media with the following columns. BOOK_ID is the parent column to which this media is associated.
And if a given image/video can be associated to multiple books and each book can have different images, then you'll have a different entity for the association.. something like..