多对多关系设计 [英] Multiple one to many relationship design

问题描述

目前，我们拥有存储多个图像/视频网址的对象的设计：

At the moment, we have this design for storing objects with multiple image/video URLs:

tblCompany:
pkCompanyId

tblPerson:
pkPersonId

tblImage:
pkImageId
ImageUrl
fkCompanyId
fkPersonId

此设计处理：

1. 一个拥有多个图像的公司


2. 有多个图像的人

t帮助感觉这个设计有问题，因为tblImage中的行将为外键列提供大量NULL值。

I can't help feeling that there is a problem with this design as rows in tblImage will have tons of NULL values for the foreign key columns.

有更好的设计吗？更多的对象（一些与公司或个人无关，一些与公司或个人相关的）在设计中将具有图像，因此目前的设计tblImage可能拥有越来越多的外键。

Is there a better design? More objects (some unrelated to company or person, some related to company or person) in the design will have images so with the current design tblImage could have more and more foreign keys.

推荐答案

这实际上是一个非常好的设计，只有2个可以拥有图像的实体。是的，你会有很多NULL，但替代方案（如单独的图表，或者特制的1：N链接表）也会有问题。

This is actually a pretty good design for just 2 entities that can have images. Yes, you'll have plenty of NULLs, but alternatives (such as separate image tables, or specially crafted 1:N link tables) will have their problems too.

这是一个1：N的关系，我们不需要任何额外的M：N连接/链接表。

Since this is a 1:N relationship, we don't need any additional M:N junction/link tables.

您需要添加更多可以拥有图像的实体，您可以考虑继承，如下所示：

In case you need to add more kinds of entities that can have images, you could consider inheritance, like this:

这样一来，图像将自动连接到任何继承自 tblCommon ，无论有多少种实体。不幸的是，关系数据库管理系统并不直接支持继承，因此您必须在 3种方式，每种都有自己的一套妥协。

This way, an image will automatically be able to connect to any entity that inherits from the tblCommon, no matter how many kinds of entities there are. Unfortunately, inheritance is not directly supported in relational DBMSes, so you'll have to emulate it in one of 3 ways, each with its own set of compromises.

这篇关于多对多关系设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！