Search code examples
sqldatabase-designdata-modelingcomposite-keysurrogate-key

Surrogate key as a foreign key over composite keys

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

Solution

  • 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.