Shared Primary key versus Foreign Key

Question

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

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)

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.

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.

Answer 1

It looks like in my case this supertype/subtype model in not the best choice. Instead, I should move the fields from the analysis table into all the child tables, and make a series of simple foreign key relationships. The advantage of the supertype/subtype model is when using the primary key of the supertype as a foreign key in another table. Since I am not doing this, the extra layer of complexity will not add anything.

Answer 2

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.

If I've understood your comments above, the advantage is that only the first implements the requirement that each row in the parent match a row in one child, and only in one child. Here's one way to do that.

create table analytical_methods (
  method_id integer primary key,
  method_name varchar(25) not null unique
);
insert into analytical_methods values
(1, 'gc'),(2, 'spec'), (3, 'Atomic Absorption'), (4, 'pH probe');

create table analysis (
  analysis_id integer primary key,
  sample_id integer not null, --references samples, not shown
  method_id integer not null references analytical_methods (method_id),
  analysis_timestamp timestamp not null,
  analysis_notes varchar(255),
  -- This unique constraint lets the pair of columns be the target of
  -- foreign key constraints from other tables.
  unique (analysis_id, method_id)
);

-- The combination of a) the default value and the check() constraint on 
-- method_id, and b) the foreign key constraint on the paired columns 
-- analysis_id and method_id guarantee that rows in this table match a 
-- gc row in the analysis table. 
--
-- If all the child tables have similar constraints, a row in analysis 
-- can match a row in one and only one child table.
create table gc (
  analysis_id integer primary key,
  method_id integer not null 
    default 1 
    check (method_id = 1),
  foreign key (analysis_id, method_id) 
    references analysis (analysis_id, method_id),
  gc_concentration_meoh integer not null,
  gc_concentration_benzene integer not null
);