即使您可以将标识序列放在多个表中,您的注释表也无法在单个外键中引用两个列。
根据关系数据库设计理论,最好的方法是创建两个评论表。但显然,您希望避免这种情况,可能是出于代码重用的原因。
最直接的实用方法是在评论表上放置两个外键列,并为每个评论设置一个为空,另一个不为空。
另一种方法(可能是最好的折衷方案)是这样的。您在问题中提到“实体 ID”。所以制作一个实体表!那么作者以及书籍和评论都可以参考that table.
编辑添加:
Philip Kelley、Ray 和(我认为)Artic 都建议修改评论表,添加一个entity_id
,它可以指的是book_id
or the author_id
,以及某种标志(char(1)
, tinyint
, and boolean
分别)表明正在引用其中的哪一个。
出于多种原因,无论是实用性(包括数据完整性、报告、效率)还是理论上,这都不是一个好的解决方案。
第一个也是最明显的问题是数据完整性问题。关系数据库系统应该始终负责维护其自身数据的完整性,并且数据库设计有自然且首选的方式来做到这一点。这些机制中最重要的之一是外键系统。如果comment.entity_id
列是引用两者book.book_id
and author.author_id
,则无法为此列创建外键。
当然,您可以在 DML(插入、更新、删除)存储过程中进行检查来验证引用,但这很快就会变得一团糟,因为所有三个表上的所有 DML 操作都会涉及到。
这给我们带来了效率问题。每当查询运行时comment
表,它将需要连接到author
or book
表或两者。查询计划生成系统将没有可用于优化的外键,因此其性能很可能会下降。
那么这个方案在报道中就存在问题。任何报告生成系统都会遇到此类系统的问题。当然,这对于专业程序员来说不是问题,但是任何用户临时报告都必须模拟背后的逻辑,当event_id
意味着这个或那个,这可能是一笔非常糟糕的交易。也许您永远不会在该数据库上使用报告生成工具。但话又说回来,没有人知道数据库最终将用在哪里。为什么不与系统合作以允许任何事情发生呢?
这就引出了理论问题。
在关系数据库理论中,每个表(“关系变量”)中的每一行(也称为“元组”)代表关于现实世界的一个命题。设计表格就是决定该命题的形式。让我们看几个例子来说明它是如何工作的。
comment (comment_id int, comment_type char(1), entity_id int,
user_id int, comment_text nvarchar(max), comment_date datetime)
/* comment_id identifies a comment (comment_text) that a user (user_id)
has made about a book (entity_id if comment_type = 'B') or author
(entity_id if comment_type = 'A') at a particular date and
time (comment_date).*/
这里很明显,该列(或“属性”)称为entity_id
正在履行双重职责。除了引用另一列之外,它实际上并不代表任何内容。这是可行的,但并不令人满意。
comment (comment_id int, book_id int, author_id int, user_id int,
comment_text nvarchar(max), comment_date datetime)
/* comment_id identifies a comment (comment_text) that a user (user_id)
has made about a book (book_id if not null) or author (author_id if
not null) at a particular date and time (comment_date). */
这为我们购买了外键,这是第一个版本中最大的遗漏。但这仍然不是很令人满意,除非一条评论可以同时指一本书和一个作者(这可能是合理的)。可空列是一个警告信号,表明设计存在问题,这里也可能出现这种情况。检查约束可能是必要的,以避免评论根本没有提及任何内容,或者如果不允许的话,则可以同时提及一本书和作者。
从理论角度(以及我的角度:))有一个明显的最佳选择:
book_comment (book_comment_id int, book_id int, user_id int,
comment_text nvarchar(max), comment_date datetime)
/* book_comment_id identifies a comment (comment_text) that a
user (user_id) has made about a book (book_id) at a particular
date and time (comment_date). */
author_comment (author_comment_id int, author_id int, user_id int,
comment_text nvarchar(max), comment_date datetime)
/* author_comment_id identifies a comment (comment_text) that a
user (user_id) has made about an author (author_id) at a particular
date and time (comment_date). */
最后一个选项将提供最佳的效率、数据完整性和报告的简易性。唯一的代价是 DML 存储过程需要将注释放入正确的表中,这不是什么大问题,因为它们必须知道注释所指的内容。
如果您的计划是立即检索一本书或作者的所有评论,那么您可以轻松地在这些表之上创建一个视图来重现其他设计(如果您想要这样做)。
create view comments as
select
book_comment_id as comment_id,
book_id as entity_id,
comment_text,
'B' as comment_type
from book_comment
union
select
author_comment_id as comment_id,
author_id as entity_id,
comment_text,
'A' as comment_type
from author_comment