Общий первичный ключ по сравнению с внешним ключом

Question

У меня есть база данных лабораторного анализа, и я работаю над разметкой данных. Я видел некоторые предложения, основанные на схожих требованиях для использования "Shared Primary Key", но я не вижу преимуществ перед простыми внешними ключами. Я использую PostgreSQL: таблицы перечислены ниже

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)

Я мог бы использовать эту схему или перенести поля из таблицы анализа в таблицы gc и спектрофотометра и просто использовать внешние ключи между таблицами sample, gc и spectrophotometer. Единственное преимущество, которое я вижу в этом проекте, - это случаи, когда мне просто нужна информация о том, сколько и какие типы анализов были выполнены, без необходимости присоединяться к фактическим результатам. Однако дополнительные правила для обеспечения ссылочной целостности между общими первичными ключами и управления дополнительными объединениями и триггерами (при каскаде удаления и т. Д.), По-видимому, делают его скорее головной болью, чем незначительными преимуществами. Я не администратор, а ученый, поэтому, пожалуйста, дайте мне знать, что мне не хватает.

ОБНОВЛЕНИЕ: Общий первичный ключ (насколько я понимаю) подобен внешнему ключу "один к одному" с дополнительным ограничением, согласно которому каждое значение в родительских таблицах (анализ) должно появляться в одной из дочерних таблиц один раз, и не более один раз.

Answer 1

Я видел некоторые предложения, основанные на схожих требованиях для использования "Shared Primary Key", но я не вижу преимуществ перед простыми внешними ключами.

Если я понял ваши комментарии выше, то преимущество состоит в том, что только первый реализует требование, чтобы каждая строка в родительском элементе соответствовала строке в одном дочернем элементе и только в одном дочернем элементе. Вот один из способов сделать это.

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
);

Answer 2

Похоже, в моем случае эта модель супертипа / подтипа не лучший выбор. Вместо этого я должен переместить поля из таблицы анализа во все дочерние таблицы и создать серию простых отношений внешнего ключа. Преимущество модели супертипа / подтипа заключается в использовании первичного ключа супертипа в качестве внешнего ключа в другой таблице. Поскольку я этого не делаю, дополнительный уровень сложности ничего не добавит.