单独的链接/关联表用于不同的数据？ [英] Separate link/association tables for different data?

问题描述

哪种方法更好：在数据库中为每种数据类型使用单独的链接/关联表，或将通用标识合并到公共链接/关联表中？

Which is a better design approach: Having separate link/association tables for each type of data within a database or incorporating a general identity into a common link/association table?

因为没有一个例子，这个问题真的没有道理...

Since this question really doesn't make sense without an example...

假设我有一个数据库，其中包含作者和书籍的数据（以一个例子为例）可以很容易地掌握并认同）。为了简单起见，每个表的外观如下：

Suppose I have a database which contains data for authors and books (to use an example people can readily grasp and identify with). For the sake of simplicity, the tables for each look like:

Id

名称

Id
Name

Id

AuthorId

标题

ISBN

Id
AuthorId
Title
ISBN

我决定将这两组数据的链接包含到其他系统中（亚马逊，巴恩斯& Noble，一百万本书等）。我已经为LinkType，LinkSources和LinkBases创建了表：

I've decided I want to include links for these two sets of data into other systems (Amazon, Barnes & Noble, Books-A-Million, etc.). I've created tables for the LinkTypes, LinkSources, and LinkBases:

Id

名称（例如，书，作者）

Id
Name (e.g., Book, Author)

Id

名称（例如，亚马逊，B& N等）

Id
Name (e.g., Amazon, B&N, etc.)

Id

LinkTypeId

LinkSourceId

UrlBase（例如， http://www.amazon.com/book.html?id= {0}）

实际链接：

Id

LinkTypeId

LinkSourceId

ReferenceValue（将此值替换为关联的UrlBase以创建实际的链接）

Id
LinkTypeId
LinkSourceId
ReferenceValue (this value is substituted into the associated UrlBase to create the actual link)

到目前为止，我一直在考虑为与作者和书籍相关的链接创建单独的表。这些表如下所示：

Up to now, I've been thinking of creating separate tables for the links related to Authors and Books. The tables would look something like:

AuthorId

LinkId

AuthorId
LinkId

这对两种不同类型的数据可能很好，但是当我决定扩展数据库以包括发布者，讨论组，类型以及许多其他类型的数据时，会发生什么情况我可以提供链接的数据？

This is probably fine for two different types of data, but what happens when I decide to expand the database to include Publishers, Discussion Groups, Genres, and any number of other types of data which I could have links available? Is this still good design (or was it good design to begin with)?

我正在考虑的一个选项是修改Links表以包括AssociationId的列，这是一个好设计吗？与特定类型的数据无关，而是用于建立必要的链接。这种方法消除了单独的关联表，但从长远来看会给设计带来一定程度的混乱。

An option I'm considering is modifying the Links table to include a column for AssociationId which is not tied to a specific type of data, but would be used to establish the necessary link. This approach eliminates the separate association tables, but it could introduce a level of confusion to the design long-term.

任何人都认为哪个设计更好？如果需要提供更多详细信息，请告诉我。

Any thoughts on which is the better design? If I need to provide further detail, please let me know.

推荐答案

我问了类似的问题，并得到了一些有趣的答复：<一个href = https://stackoverflow.com/questions/2862918/common-one-to-many-table-for-multiple-entities>多个实体的通用一对多表

I asked a similar question and got some intersting responses: Common one-to-many table for multiple entities

我认为最好的解决方案是使用继承。创建一个抽象实体（我称其为Item，但我确定有一个更好的名称），该实体代表Author，Book或任何其他可能具有链接的实体。您最终会遇到类似这样的事情：

I think the best solution is to use inheritance. Create an abstract entity (I called it Item but I'm sure there's a better name) that represents an Author, Book, or anything else that could have a link. You'd end up with something like this:

Item      Author    Book       LinkBase        Link
=======   =======   =========  ==========      ========
ItemID    ItemID    ItemID     LinkBaseID      LinkBaseID
          Name      AuthorID   LinkTypeId      ItemID
                    Title      LinkSourceId    ReferenceValue
                               ReferenceValue

我认为您不需要< Link 表中的code> LinkSourceID ，因为您已经具有指向 LinkBase 包含 LinkSourceID 。

I don't think you need LinkSourceID on the Link table because you already have a link back to the LinkBase which contains the LinkSourceID.

我还有一个想法是，如果引用完整性很重要，那么您可能想避免第二种选择，因为您无法使用通用的 AssociationID 在 Links 表上维护外键

One additional thought I had is that if referential integrity is important then you might want to avoid your second option because you can't maintain a foreign key on your Links table with a generic AssociationID.

这篇关于单独的链接/关联表用于不同的数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！