title: Surrogate key as a foreign key over composite keys
body: <p><em>I realise there might be similar questions but I couldn't find one that was close enough for guidance.</em></p>
  
  <p>Given this spec,</p>
  
  <pre><code>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)
  ...
  </code></pre>
  
  <p>my proposed design/implementation is</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p><em>*: "virtual" foreign key, since it's been previously decided by the higher-ups we should not use <strong>actual</strong> foreign keys</em></p>