Поддержание логической согласованности с мягким удалением при сохранении исходной информации

У меня очень простая таблица students, структура, как показано ниже, где первичный ключ id. Эта таблица заменяет около 20 таблиц с многомиллионными строками, которые часто объединяются.

+----+----------+------------+
| id |   name   |    dob     |
+----+----------+------------+
|  1 | Alice    | 01/12/1989 |
|  2 | Bob      | 04/06/1990 |
|  3 | Cuthbert | 23/01/1988 |
+----+----------+------------+

Если Боб хочет изменить дату своего рождения, у меня есть несколько вариантов:

1. Обновите students, указав новую дату рождения.

   Положительные моменты: 1 операция DML; к таблице всегда можно получить доступ с помощью единственного поиска по первичному ключу.

   Минусы: я упускаю из виду тот факт, что Боб когда-либо думал, что родился 06.04.1990.

2. Добавьте в таблицу столбец created date default sysdate и измените первичный ключ на id, created. Каждый update становится:

   insert into students(id, name, dob) values (:id, :name, :new_dob)
   

   Затем, когда мне нужна самая свежая информация, сделайте следующее (Oracle, но вопрос стоит для каждой СУБД):

   select id, name, dob
     from ( select a.*, rank() over ( partition by id 
                                          order by created desc ) as "rank"
              from students a )
    where "rank" = 1
   

   Положительные моменты: я никогда не теряю информацию.

   Минус-слова: все запросы по всей базе данных занимают немного больше времени. Если размер таблицы был указан, это не имеет значения, но как только вы перейдете на 5-е left outer join сканирование по диапазону, а не по уникальному сканированию, начнет оказывать влияние.

3. Добавьте другой столбец, deleted date default to_date('2100/01/01','yyyy/mm/dd'), или что-нибудь еще, слишком раннее или футуристическое, дата мне понравится. Измените первичный ключ на id, deleted, тогда каждый update станет:

   update students x
      set deleted = sysdate 
    where id = :id
      and deleted = ( select max(deleted) from students where id = x.id );
   insert into students(id, name, dob) values ( :id, :name, :new_dob );
   

   и запрос на получение текущей информации принимает следующий вид:

   select id, name, dob
     from ( select a.*, rank() over ( partition by id 
                                          order by deleted desc ) as "rank"
              from students a )
    where "rank" = 1
   

   Положительные моменты: я никогда не теряю информацию.

   Минусы: две операции DML; Мне по-прежнему приходится использовать ранжированные запросы с дополнительной стоимостью или сканирование диапазона, а не сканирование уникального индекса в каждом запросе.

4. Создайте вторую таблицу, скажем student_archive, и измените каждое обновление на:

   insert into student_archive select * from students where id = :id;
   update students set dob = :newdob where id = :id;
   

   Положительные стороны: Никогда не теряйте никакой информации.

   Минусы: 2 операции DML; если вы когда-нибудь захотите получить всю необходимую информацию, используйте union или дополнительный left outer join.

5. Для полноты изобразите ужасно ненормализованную структуру данных: id, name1, dob, name2, dob2... и т. Д.

Если номер 1 не подходит, я никогда не хочу терять информацию и всегда делаю мягкое удаление. Номер 5 можно смело отбросить, поскольку он причиняет больше неприятностей, чем оно того стоит.

У меня остались варианты 2, 3 и 4 с сопутствующими им отрицательными аспектами. Обычно я использую вариант 2 и ужасающие 150 строк (с хорошим интервалом) с несколькими объединениями подвыбора, которые идут вместе с ним.

tl; dr Я понимаю, что катаюсь близко к финишу из-за "неконструктивного" голосования здесь, но:

Каков оптимальный (единственный!) Метод поддержания логической непротиворечивости, при этом никогда не удаляя никаких данных?

Есть ли более эффективный способ, чем те, которые я задокументировал? В этом контексте я определю эффективность как «меньше операций DML» и / или «возможность удалить подзапросы». Если вы можете придумать лучшее определение, когда (если) отвечаете, пожалуйста, не стесняйтесь.