title: Shared Primary key versus Foreign Key
body: <p>I have a laboratory analysis database and I'm working on the bast data layout. I've seen some suggestions based on similar requirements for using a "Shared Primary Key", but I don't see the advantages over just foreign keys. I'm using PostgreSQL:tables listed below</p>
  
  <pre><code>Sample
  ___________
  sample_id (PK)
  sample_type (where in the process the sample came from)
  sample_timestamp (when was the sample taken)
  
  
  Analysis
  ___________
  analysis_id (PK)
  sample_id (FK references sample)
  method (what analytical method was performed)
  analysis_timestamp (when did the analysis take place)
  analysis_notes
  
  gc
  ____________
  analysis_id (shared Primary key)
  gc_concentration_meoh (methanol concentration)
  gc_concentration_benzene (benzene concentration)
  
  spectrophotometer
  _____________
  analysis_id
  spectro_nm (wavelength used)
  spectro_abs (absorbance measured)
  </code></pre>
  
  <p>I could use this design, or I could move the fields from the analysis table into both the gc and spectrophotometer tables, and just use foreign keys between sample, gc, and spectrophotometer tables. The only advantage I see of this design is in cases where I would just want information on how many or what types of analyses were performed, without having to join in the actual results.  However, the additional rules to ensure referential integrity between the shared primary keys, and managing extra joins and triggers (on delete cascade, etc) appears to make it more of a headache than the minor advantages. I'm not a DBA, but a scientist, so please let me know what I'm missing.</p>
  
  <p>UPDATE:
  A shared primary key (as I understand it) is like a one-to-one foreign key with the additional constraint that each value in the parent tables(analysis) must appear in one of the child tables once, and no more than once.</p>