Скажем, что у меня есть запись в базе данных и что как администраторы, так и обычные пользователи могут делать обновления.
Может ли кто-нибудь предложить хороший подход/архитектуру, как управлять версиями каждого изменения в этой таблице, чтобы можно было отбросить запись на предыдущую ревизию.
Скажем, у вас есть таблица FOO, которую могут обновлять администраторы и пользователи. Большую часть времени вы можете писать запросы по таблице FOO. Счастливые дни.
Тогда я бы создал таблицу FOO_HISTORY. У этого есть все столбцы таблицы FOO. Первичный ключ такой же, как FOO плюс столбец RevisionNumber. Существует внешний ключ от FOO_HISTORY до FOO. Вы также можете добавить столбцы, относящиеся к ревизии, такие как UserId и RevisionDate. Заполняйте RevisionNumbers все возрастающим образом во всех таблицах *_HISTORY (т.е. Из последовательности Oracle или ее эквивалента). Не полагайтесь на то, чтобы в течение одной секунды было одно изменение. то есть. не помещайте RevisionDate в первичный ключ.
Теперь, каждый раз, когда вы обновляете FOO, перед обновлением вы вставляете старые значения в FOO_HISTORY. Вы делаете это на некотором фундаментальном уровне в своем дизайне, чтобы программисты не могли случайно пропустить этот шаг.
Если вы хотите удалить строку из FOO, у вас есть выбор. Либо каскадируйте, либо удаляйте всю историю, либо выполняйте логическое удаление, помеченные FOO как удаленные.
Это решение хорошо, когда вы в значительной степени заинтересованы в текущих значениях и только изредка в истории. Если вам всегда нужна история, вы можете ввести эффективные даты начала и окончания и сохранить все записи в FOO. Затем каждый запрос должен проверять эти даты.
Я думаю, что вы ищете версию содержимого записей базы данных (как это делает StackOverflow, когда кто-то редактирует вопрос/ответ). Хорошей отправной точкой может быть поиск некоторой модели базы данных, которая использует отслеживание версий.
Лучшим примером, который приходит на ум, является MediaWiki, движок Википедии. Сравните диаграмму базы данных здесь, особенно таблица изменений.
В зависимости от того, какие технологии вы используете, вам нужно найти хорошие алгоритмы diff/merge.
Отметьте этот вопрос, если он для .NET.
В мире BI вы можете выполнить это, добавив startDate и endDate в таблицу, которую хотите изменить. Когда вы вставляете первую запись в таблицу, startDate заполняется, но endDate имеет значение NULL. Когда вы вставляете вторую запись, вы также обновляете endDate первой записи с помощью startDate второй записи.
Если вы хотите просмотреть текущую запись, вы выбираете ту, где endDate имеет значение null.
Это иногда называют типом 2 Медленным изменением размера. См. Также TupleVersioning
Перейти на SQL 2008.
Попробуйте использовать SQL Change Tracking в SQL 2008. Вместо того, чтобы использовать временную метку и взломать столбцы, вы можете использовать эту новую функцию для отслеживания изменений данных в вашей базе данных.
Просто хотел добавить, что одним из хороших решений этой проблемы является использование временную таблицу с PostgreSQL, но у других тоже есть. Всякий раз, когда вы обновляете запись в базе данных, база данных также хранится в предыдущей версии этой записи.
Вы не говорите, какая база данных, и я не вижу ее в тегах сообщений. Если это для Oracle, я могу порекомендовать подход, встроенный в конструктор: используйте таблицы журналов. Если это для любой другой базы данных, ну, я в принципе рекомендую то же самое, тоже...
Как это работает, если вы хотите реплицировать его в другой БД, или, может быть, если вы просто хотите это понять, это то, что для таблицы также создается теневая таблица, только обычная таблица базы данных, с такие же полевые спецификации, а также некоторые дополнительные поля: например, какое действие было выполнено в последний раз (строка, типичные значения "INS" для вставки, "UPD" для обновления и "DEL" для удаления), datetime для того, когда действие было выполнено, и идентификатор пользователя для тех, кто это сделал.
Через триггеры каждое действие в любую строку таблицы вставляет новую строку в таблицу журнала с новыми значениями, какое действие было предпринято, когда и каким пользователем. Вы никогда не удаляете никаких строк (по крайней мере, не последние несколько месяцев). Да, он будет расти, просто миллионы строк, но вы можете легко отслеживать значение для любой записи в любой момент времени с момента начала ведения журнала, или старые строки журнала были очищены последним, и кто сделал последнее изменение.
В Oracle все, что вам нужно, генерируется автоматически как код SQL, все, что вам нужно сделать, это скомпилировать/запустить его; и он поставляется с базовым CRUD-приложением (фактически только "R" ) для его проверки.
Два варианта:
Вы можете выполнять аудит таблицы SQL через триггеры SQL. С триггера вы можете получить доступ к 2 специальным таблицам (вставлен и удален). Эти таблицы содержат точные строки, которые были вставлены или удалены каждый раз, когда таблица обновляется. В триггерном SQL вы можете взять эти измененные строки и вставить их в таблицу аудита. Этот подход означает, что ваш аудит прозрачен для программиста; не требуя от них никаких усилий или каких-либо знаний о внедрении.
Дополнительным преимуществом такого подхода является то, что аудит будет происходить независимо от того, выполнялась ли операция sql через DLL файлы с доступом к данным или с помощью ручного SQL-запроса; (поскольку аудит выполняется на самом сервере).
Я тоже делаю то же самое. Я делаю базу данных для планов уроков. Эти планы нуждаются в гибкости управления версиями Atom. Другими словами, каждое изменение, независимо от того, насколько мало, должно быть разрешено для планов уроков, но старая версия также должна быть сохранена. Таким образом, создатели уроков могут редактировать планы уроков, пока учащиеся их используют.
То, как это будет работать, заключается в том, что после того, как студент сделал урок, их результаты привязаны к завершенной версии. Если изменение сделано, их результат всегда будет указывать на их версию.
Таким образом, если критерии урока будут удалены или перемещены, их результаты не изменятся.
То, как я сейчас это делаю, - это обработка всех данных в одной таблице. Обычно у меня просто одно поле id, но с этой системой я использую id и sub_id. Sub_id всегда остается в строке с помощью обновлений и удалений. Идентификатор автоматически увеличивается. Программное обеспечение плана уроков будет ссылаться на новейший sub_id. Результаты ученика свяжутся с идентификатором. Я также включил отметку времени для отслеживания, когда произошли изменения, но нет необходимости обрабатывать версии.
Одна вещь, которую я могу изменить, как только я ее протестирую, могу использовать ранее упомянутую идею endDate null. В моей системе, чтобы найти новейшую версию, мне нужно было бы найти max (id). Другая система просто ищет endDate = null. Не уверен, что преимущества имеют другое поле даты.
Мои два цента.
Пока @WW. Ответ - хороший ответ, иначе можно создать столбец версии и сохранить все ваши версии в одной таблице.
Для одного табличного подхода вы также:
outer join.Пример SQL метода outer join с использованием номеров версий:
SELECT tc.*
FROM text_content tc
LEFT OUTER JOIN text_content mc ON tc.path = mc.path
AND mc.revision > tc.revision
WHERE mc.revision is NULL
AND tc.path = '/stuff' -- path in this case is our natural id.
Плохая новость заключается в том, что для этого требуется outer join, а внешние соединения могут быть медленными. Хорошей новостью является то, что создание новых записей теоретически дешевле, потому что вы можете сделать это в одной операции записи без транзакций (при условии, что ваша база данных атомарна).
Пример создания новой версии для '/stuff' может быть:
INSERT INTO text_content (id, path, data, revision, revision_comment, enabled, create_time, update_time)
(
SELECT
(md5(random()::text)) -- {id}
, tc.path
, 'NEW' -- {data}
, (tc.revision + 1)
, 'UPDATE' -- {comment}
, 't' -- {enabled}
, tc.create_time
, now()
FROM text_content tc
LEFT OUTER JOIN text_content mc ON tc.path = mc.path
AND mc.revision > tc.revision
WHERE mc.revision is NULL
AND tc.path = '/stuff' -- {path}
)
Вставляем старые данные. Это особенно полезно, если вы хотите только обновить один столбец и избежать оптимистической блокировки и транзакций.
Подход флага и таблицы таблиц истории требуют, чтобы строки два были вставлены/обновлены.
Другим преимуществом подхода с номером версии outer join является то, что вы всегда можете реорганизовать метод множественной таблицы позже с помощью триггеров, потому что ваш триггер должен по существу сделать что-то вроде выше.