Databases
StackOverflow2013
Postsdbo
POSurrogate key as a foreign key over composite keys
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSurrogate key as a foreign key over composite keys
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USJ F
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSurrogate key as a foreign key over composite keys
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSurrogate key as a foreign key over composite keys
      1. USKLE
      1. VTFavorite
    3. VO
      singulars
      1. POSurrogate key as a foreign key over composite keys
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload