title: How to Implement Referential Integrity in Subtypes
body: <p>I have the following tables in a relational database:</p>
  
  <pre><code>[Sensor]
  LocationId [PK / FK -&gt; Location]
  SensorNo [PK]
  
  [AnalogSensor]
  LocationId [PK/FK -&gt; Sensor]
  SensorNo [PK/FK -&gt; Sensor]
  UpperLimit
  LowerLimit
  
  [SwitchSensor]
  LocationId [PK/FK -&gt; Sensor]
  SensorNo [PK/FK -&gt; Sensor]
  OnTimeLimit
  
  [Reading]
  LocationId [PK/FK -&gt; Sensor]
  SensorNo [PK/FK -&gt; Sensor]
  ReadingDtm [PK]
  
  [ReadingSwitch]
  LocationId [PK/FK -&gt; Reading]
  SensorNo [PK/FK -&gt; Reading]
  ReadingDtm [PK/FK -&gt; Reading]
  Switch
  
  [ReadingValue]
  LocationId [PK/FK -&gt; Reading]
  SensorNo [PK/FK -&gt; Reading]
  ReadingDtm [PK/FK -&gt; Reading]
  Value
  
  [Alert]
  LocationId [PK/FK -&gt; Reading]
  SensorNo [PK/FK -&gt; Reading]
  ReadingDtm [PK/FK -&gt; Reading]
  </code></pre>
  
  <p>Basically, ReadingSwitch and ReadingValue are subtypes of Reading and SwitchSensor and AnalogSensor are subtypes of Sensor.  A reading can either be a SwitchReading or ValueReading value - it cannot be both, and a Sensor can either be an AnalogSensor or a SwitchSensor.</p>
  
  <p>The only way I've come across to do this so far is <a href="http://web.archive.org/web/20100309034643/http://consultingblogs.emc.com/davidportas/archive/2007/01/08/Distributed-Keys-and-Disjoint-Subtypes.aspx" rel="noreferrer">here</a>.</p>
  
  <p>There surely must be a nicer way to do this sort of thing.</p>
  
  <p>The only other way I can think of is to not have sub types but completely expand everything:</p>
  
  <pre><code>[SwitchSensor]
  LocationId [PK/FK -&gt; Location]
  SensorNo [PK]
  
  [AnalogSensor]
  LocationId [PK/FK -&gt; Location]
  SensorNo [PK]
  
  [SwitchReading]
  LocationId [PK/FK -&gt; SwitchSensor]
  SensorNo [PK/FK -&gt; SwitchSensor]
  ReadingDtm
  Switch
  
  [AnalogReading]
  LocationId [PK/FK -&gt; AnalogSensor]
  SensorNo [PK/FK -&gt; AnalogSensor]
  ReadingDtm
  Value
  
  [AnalogReadingAlert]
  LocationId [PK/FK -&gt; AnalogReading]
  SensorNo [PK/FK -&gt; AnalogReading]
  ReadingDtm [PK/FK -&gt; AnalogReading]
  
  [SwitchReadingAlert]
  LocationId [PK/FK -&gt; SwitchReading]
  SensorNo [PK/FK -&gt; SwitchReading]
  ReadingDtm [PK/FK -&gt; SwitchReading]
  </code></pre>
  
  <p>Which might not be so bad but I also have tables that reference the Alert table, so they too would have to be duplicated:</p>
  
  <pre><code>[AnalogReadingAlertAcknowledgement]
  ...
  [AnalogReadingAlertAction]
  ...
  [SwitchReadingAlartAcknowledgement]
  ...
  [SwitchReadingAlartAction]
  </code></pre>
  
  <p>etc.</p>
  
  <p>Does this problem make any sense to anyone??</p>