Добавление системы версий / истории в базу данных table

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

Мой менеджер проекта предполагает, что это должно быть сделано следующим образом. чтобы добавить несколько новых столбцов в каждую таблицу. Главная особенность - это колонка с названием «версия». Каждый раз, когда выполняется обновление, на самом деле ничего не обновляется, старая строка остается, но в таблицу добавляется новая строка с увеличенным значением для «версии».

Чтобы показать текущие данные, мы просто используем представление, которое показывает только строки с наивысшим номером версии каждого типа.

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

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

Я знаю, что есть и другие способы сохранения истории обновлений базы данных (например, используя отдельную таблицу истории для каждого table), но я придерживаюсь этого подхода в этом проекте. но это бесполезно в качестве идентификатора, поскольку несколько строк в основном представляют собой разные версии одного и того же. Мы могли бы вручную отслеживать последнюю версию каждого типа записи и обновлять соответствующие отношения внешнего ключа каждый раз, когда что-то меняется, но это выглядит как большая работа, и я не уверен, что это всегда будет работать (например, возврат к предыдущей версии записи может привести к тому, что внешний ключ будет ссылаться на старую и непригодную для использования версию другой записи в какой-либо другой таблице.)

Я знаю, что есть и другие способы сохранения истории обновлений базы данных (например, используя отдельную таблицу истории для каждого table), но я придерживаюсь этого подхода в этом проекте. но это бесполезно в качестве идентификатора, поскольку несколько строк в основном представляют собой разные версии одного и того же. Мы могли бы вручную отслеживать последнюю версию каждого типа записей и обновлять соответствующие отношения внешних ключей каждый раз, когда что-то меняется, но это выглядит как большая работа, и я не уверен, что это всегда будет работать (например, возврат к предыдущей версии записи может привести к тому, что внешний ключ будет ссылаться на старую и непригодную для использования версию другой записи в какой-либо другой таблице.)

Я знаю, что есть и другие способы сохранения истории обновлений базы данных (например, используя отдельную таблицу истории для каждого table), но я придерживаюсь этого подхода в этом проекте. Есть ли более очевидный способ обработки взаимосвязей между подобными таблицами, который мне не хватает?

Примечание: я использую MS SQL Server 2008 R2.