MySQL первичные ключи с двумя полями ID

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

Возьмите следующие данные о сотрудниках:

EmpNo FirstName LastName Birthdate HireDate Payrate DeptNo

Теперь перед вами стоит задача поддерживать версии данных по мере их изменения. Затем вы можете добавить поле даты, которое показывает, когда изменились данные:

EmpNo EffDate FirstName LastName Birthdate HireDate Payrate DeptNo

Поле «Дата вступления в силу» показывает дату вступления в силу каждой конкретной строки.

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

Одно очевидное решение состоит в том, чтобы сделать комбинацию EmpNo и нового поля EffDate первичным ключом.

Хорошо, это решает проблему PK, но теперь как насчет внешних ключей в других таблицах, которые относятся к конкретным сотрудникам? Можем ли мы добавить поле EffDate в эти таблицы?

Ну, конечно, мы можем. Но это означает, что внешние ключи вместо ссылки на одного конкретного сотрудника теперь ссылаются на одну конкретную версию одного конкретного сотрудника. Не, как говорится, номинальный.

Многие схемы были реализованы для решения этой проблемы (см. Запись в «Википедии»Медленно изменяющийся размер«для списка нескольких наиболее популярных).

Вот простое решение, которое позволяет вам создавать версии ваших данных и оставлять ссылки на внешние ключи в покое.

Во-первых, мы понимаем, что не все данные когда-либо изменятся и поэтому никогда не будут обновляться. В нашем примере кортежа это статические данные: EmpNo, FirstName, Birthdate, HireDate. Данные, которые могут измениться, — это LastName, Payrate, DeptNo.

Но это означает, что статические данные, такие как FirstName, зависят от EmpNo — исходного PK. Изменяемые или динамические данные, такие как LastName (которые могут измениться из-за брака или усыновления), зависят от EmpNo и EffDate. Наш кортеж уже не во второй нормальной форме!

Итак, мы нормализуем. Мы знаем, как это сделать, верно? С закрытыми глазами. Дело в том, что когда мы закончим, у нас будет основная таблица сущностей с одной и только одной строкой для каждого определения сущности. Все внешние ключи могут ссылаться на эту таблицу для одного конкретного сотрудника — так же, как и в случае нормализации по любой другой причине. Но теперь у нас также есть таблица версий со всеми данными, которые могут время от времени изменяться.

Теперь у нас есть два кортежа (по крайней мере, два — возможно, были выполнены другие процессы нормализации), чтобы представлять нашу сущность сотрудника.

EmpNo(PK) FirstName Birthdate  HireDate
=====     ========= ========== ==========
1001      Fred      1990-01-01 2010-01-01

EmpNo(PK) EffDate(PK)    LastName Payrate DeptNo
=====     ========       ======== ======= ======
1001      2010-01-01     Smith    15.00   Shipping
1001      2010-07-01     Smith    16.00   IT

Запрос на восстановление исходного кортежа со всеми версионными данными прост:

select  e.EmpNo, e.FirstName, v.LastName, e.Birthdate, e.Hiredate, v.Payrate, v.DeptNo
from    Employees e
join    Emp_Versions v
on  v.EmpNo = e.EmpNo;

Запрос на восстановление исходного кортежа только с самыми последними данными не очень сложен:

select  e.EmpNo, e.FirstName, v.LastName, e.Birthdate, e.Hiredate, v.Payrate, v.DeptNo
from    Employees e
join    Emp_Versions v
on  v.EmpNo = e.EmpNo
and v.EffDate =(
select  Max( EffDate )
from    Emp_Versions
where   EmpNo = v.EmpNo );

Не позволяйте подзапросу пугать вас. Тщательное изучение показывает, что он находит нужную строку версии с поиском по индексу вместо сканирования, которое генерирует большинство других методов. Попробуйте — это быстро (хотя, конечно, пробег может варьироваться в зависимости от разных СУБД).

Но вот где это действительно хорошо. Предположим, вы хотели посмотреть, как выглядят данные на определенную дату. Как будет выглядеть этот запрос? Просто возьмите запрос выше и сделайте небольшое дополнение:

select  e.EmpNo, e.FirstName, v.LastName, e.Birthdate, e.Hiredate, v.Payrate, v.DeptNo
from    Employees e
join    Emp_Versions v
on  v.EmpNo = e.EmpNo
and v.EffDate =(
select  Max( EffDate )
from    Emp_Versions
where   EmpNo = v.EmpNo
and EffDate <= :DateOfInterest ); --> Just this difference

Эта последняя строка позволяет «вернуться во времени», чтобы увидеть, как выглядели данные в любое конкретное время в прошлом. И, если DateOfInterest — текущее системное время, он возвращает текущие данные. Это означает, что запрос для просмотра текущих данных и запрос для просмотра прошлых данных фактически являются одним и тем же запросом.