У меня очень простая таблица students
, структура, как показано ниже, где первичный ключ id
. Эта таблица заменяет около 20 таблиц с многомиллионными строками, которые часто объединяются.
+----+----------+------------+ | id | name | dob | +----+----------+------------+ | 1 | Alice | 01/12/1989 | | 2 | Bob | 04/06/1990 | | 3 | Cuthbert | 23/01/1988 | +----+----------+------------+
Если Боб хочет изменить дату своего рождения, у меня есть несколько вариантов:
Обновите
students
, указав новую дату рождения.Положительные моменты: 1 операция DML; к таблице всегда можно получить доступ с помощью единственного поиска по первичному ключу.
Минусы: я упускаю из виду тот факт, что Боб когда-либо думал, что родился 06.04.1990.
Добавьте в таблицу столбец
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
сканирование по диапазону, а не по уникальному сканированию, начнет оказывать влияние.Добавьте другой столбец,
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; Мне по-прежнему приходится использовать ранжированные запросы с дополнительной стоимостью или сканирование диапазона, а не сканирование уникального индекса в каждом запросе.
Создайте вторую таблицу, скажем
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
.Для полноты изобразите ужасно ненормализованную структуру данных:
id, name1, dob, name2, dob2...
и т. Д.
Если номер 1 не подходит, я никогда не хочу терять информацию и всегда делаю мягкое удаление. Номер 5 можно смело отбросить, поскольку он причиняет больше неприятностей, чем оно того стоит.
У меня остались варианты 2, 3 и 4 с сопутствующими им отрицательными аспектами. Обычно я использую вариант 2 и ужасающие 150 строк (с хорошим интервалом) с несколькими объединениями подвыбора, которые идут вместе с ним.
tl; dr Я понимаю, что катаюсь близко к финишу из-за "неконструктивного" голосования здесь, но:
Каков оптимальный (единственный!) Метод поддержания логической непротиворечивости, при этом никогда не удаляя никаких данных?
Есть ли более эффективный способ, чем те, которые я задокументировал? В этом контексте я определю эффективность как «меньше операций DML» и / или «возможность удалить подзапросы». Если вы можете придумать лучшее определение, когда (если) отвечаете, пожалуйста, не стесняйтесь.