代理键作为复合键的外键
我意识到可能存在类似的问题，但我找不到足够接近的问题来提供指导。
根据此规范，
Site
---------------------------
SiteID int identity
Name varchar(50)
Series
---------------------
SiteID int
SeriesCode varchar(6)
...
--SeriesCode will be unique for every unique SiteID
Episode
----------------------
SiteID int
SeriesCode varchar(6)
EpisodeCode varchar(10)
...
我提出的设计/实现是
Site
----------------------------
SiteID int identity
Name varchar(50)
Series
-------------------------------------------
SeriesID int identity, surrogate key
SiteID int natural key
SeriesCode varchar(6) natural key
UNIQUE(SiteID, SeriesCode)
...
Episode
-------------------------------------------
EpisodeID int identity, surrogate key
SeriesID int foreign key
EpisodeCode varchar(6) natural key
...
有什么问题吗？可以在这里将 SeriesID 代理作为外键吗？我不确定我是否遗漏了任何可能出现的明显问题。或者使用复合自然键（SiteID+SeriesCode / SiteID+EpisodeCode）会更好吗？从本质上讲，这会将剧集表与系列表分离，但这不适合我。
值得补充的是，在填充这些表的原始输入数据中，SeriesCode 看起来像“ABCD-1”，EpisodeCode 看起来像“ABCD-1NMO9”，所以我想这是另一件事可以改变。
*：“虚拟”外键，因为之前是上级决定的，所以我们不应该使用实际外键
I realise there might be similar questions but I couldn't find one that was close enough for guidance.
Given this spec,
Site
---------------------------
SiteID int identity
Name varchar(50)
Series
---------------------
SiteID int
SeriesCode varchar(6)
...
--SeriesCode will be unique for every unique SiteID
Episode
----------------------
SiteID int
SeriesCode varchar(6)
EpisodeCode varchar(10)
...
my proposed design/implementation is
Site
----------------------------
SiteID int identity
Name varchar(50)
Series
-------------------------------------------
SeriesID int identity, surrogate key
SiteID int natural key
SeriesCode varchar(6) natural key
UNIQUE(SiteID, SeriesCode)
...
Episode
-------------------------------------------
EpisodeID int identity, surrogate key
SeriesID int foreign key
EpisodeCode varchar(6) natural key
...
Anything wrong with this? Is it okay to have the SeriesID surrogate as a foreign* key here? I'm not sure if I'm missing any obvious problems that can arise. Or would it be better to use composite natural keys (SiteID+SeriesCode / SiteID+EpisodeCode)? In essence that'd decouple the Episode table from the Series table and that doesn't sit right for me.
Worth adding is that SeriesCode looks like 'ABCD-1' and EpisodeCode like 'ABCD-1NMO9' in the raw input data that will populate these tables, so that's another thing that could be changed I suppose.
*: "virtual" foreign key, since it's been previously decided by the higher-ups we should not use actual foreign keys
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
是的，一切看起来都很好。我可能提出的唯一（次要）观点是，除非您有另一个第四个子表挂在 Episode 上，否则您可能不需要 EpisodeId，因为 Episode.EpisodeCode 是足以识别和定位 Episode 中的行的单个属性自然键。当然，把它留在那里并没有什么坏处，但作为一般规则，我添加代理键作为子表中 FK 的目标，并尝试向每个表添加一个自然键来识别和控制冗余数据行......因此，如果一个表没有其他表有 FK 引用它，（而且永远不会）我有时不会费心在其中包含代理键。
Yes, it all looks fine. The only (minor) point I might make is that unless you have another 4th child table hanging off of Episode, you probably don't need EpisodeId, as Episode.EpisodeCode is a single attribute natural key sufficient to identify and locate rows in Episode. It's no harm to leave it there, of course, but as a general rule I add surrogate keys to act as targets for FKs in child tables, and try to add a narural key to every table to indentify and control redundant data rows... So if a table has no other table with a FK referencing it, (and never will) I sometimes don't bother including a surrogate key in it.
我的建议：
尽可能使用自然/业务作为主键，但以下 3 种情况除外：
在这种情况下1 和 2 需要代理键。
在情况 3 中，强烈建议使用代理键。
My suggestion:
Use natural/business as primary key whenever possible except in the following 3 situations:
In situations 1 and 2 a surrogate key is requiered.
In situation 3 a surrogate key is strongly recommended.
什么是“虚拟”外键？是不是高层决定不使用外键约束？在这种情况下，您根本就没有使用外键。你只是假装而已。
Episode 是实体的最佳选择吗？它不是真的意味着“节目”或“播客”之类的东西，而且现在恰好总是系列的一部分吗？如果是这样，未来情况会改变吗？剧集最终会被滥用以涵盖系列之外的节目吗？在这种情况下，通过系列将剧集与站点绑定可能会再次困扰您。
考虑到这一切，并假设你作为一个咕噜声可能无法改变任何一个：如果我是你，我会觉得尽可能使用自然键更安全。在没有外键约束的情况下，它使得识别错误数据变得更容易，并且如果您稍后必须诉诸于一些 SeriesCode='EMPTY' 技巧，那么使用自然键也更容易。
What's a "virtual" foreign key? Did the higher-ups decide not to use foreign key constraints? In that case, you're not using foreign keys at all. You're just pretending to.
And is Episode the best choice for an entity? Doesn't it really mean Show or Podcast or so, and just happens to always be part of a series right now? If so, will that change in the future? Will Episode eventually be abused to encompass Show outside of a Series? In that case, tying Episode to Site via Series might come back to haunt you.
Given all that, and assuming that you as a grunt probably can't change any of it: if i was you i'd feel safer using natural keys wherever possible. In absence of foreign key constraints, it makes recognizing bad data easier, and if you have to resort to some SeriesCode='EMPTY' trickery later on that's easier with natural keys, too.