Surrogate key as foreign key over composite keys
I realize there might be similar questions, but I couldn't find one that was close enough for guidance.
Given this specification,
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 suggested design / implementation
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
...
Is there something wrong with that? Is it possible to have a SeriesID surrogate as a foreign * key here? I'm not sure if I'm missing any obvious problems that might arise. Or would it be better to use composite natural keys (SiteID + SeriesCode / SiteID + EpisodeCode)? In essence, this would split the Episode table from the Series table, and does not work for me.
It is worth adding that the SeriesCode looks like "ABCD-1" and the EpisodeCode looks like "ABCD-1NMO9" in the original input that these tables will populate, so there might be another thing that could be changed.
*: "virtual" foreign key, since previously solutions were above, we should not use foreign actual foreign keys
source to share
Yes, everything looks great. The only (minor) point I can make is that if you don't have another 4th child table dangling from Episode you probably won't need EpisodeId as Episode.EpisodeCode is the natural key of one attribute sufficient to define and search for strings in the Episode. There is no harm in leaving it there, of course, but generally I add surrogate keys to act as targets for FKs in child tables, and try to add a descriptive key to each table to identify and manage redundant rows of data ... So if there is no other table in the table with an FK referencing it (and never will be), I sometimes don't bother including a surrogate key in it.
source to share
What is the "virtual" foreign key? Did the upstream authorities decide not to use foreign key constraints? In this case, you are not using foreign keys at all. You're just pretending.
And Episode is the best choice for the entity? Doesn't that mean Show or Podcast or so, and it just happens to always be part of the series right now? If so, will this change in the future? Will the Episode end up being abused to cover the show outside of the series? In this case, linking the episode to the site through the episode may come back to haunt you.
With all this in mind, and assuming that you, as a grunt, probably can't change it: if I were you, I would feel safer using natural keys where possible. In the absence of foreign key constraints, it makes it easier to recognize bad data, and if you have to resort to incorrect traversal of SeriesCode = 'EMPTY', it is easier with natural keys.
source to share
My suggestion:
Use natural / business as the primary key whenever possible , except in the following three situations:
- Natural / business key unknown at the time of insertion
- The natural / business key is not good (it is not unique, it can change frequently)
- Natural / Business Key is a collection of more than 3 columns and the table will have child tables
In situations 1 and 2, a surrogate key is required .
In situation 3, a surrogate key is highly recommended .
source to share