Историческая/аудитная база данных

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

Однако - когда элемент отредактирован или удален, мне нужно сохранить старые данные; Мне нужно уметь видеть, какие данные были перед изменением.

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

Если я создаю отчет о показаниях, я должен уметь видеть, какие атрибуты для местоположения или датчика были во время чтения.

В принципе, я должен иметь возможность восстанавливать данные в любой момент времени.

Теперь, я сделал это раньше и хорошо работал, добавив следующие столбцы в каждую редактируемую таблицу:

valid_from
valid_to
edited_by

Если valid_to = 9999-12-31 23:59:59, то это текущая запись. Если valid_to равно valid_from, то запись удаляется.

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

Я могу избежать триггеров, используя расширение для базы данных PostgreSQL. Это обеспечивает тип столбца, называемый "период", который позволяет хранить период времени между двумя датами, а затем позволяет выполнять ограничения CHECK для предотвращения периодов перекрытия. Это может быть ответ.

Мне интересно, если есть другой способ.

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

Возможно, я мог бы сократить мою первоначальную реализацию, чтобы не потрудиться, проверяя согласованность записей, которые не являются "текущими", т.е. только для проверки ограничений на записи, где valid_to - 9999-12-31 23:59:59. В конце концов, люди, которые используют исторические таблицы, похоже, не имеют ограничений на эти таблицы (по той же причине вам понадобятся триггеры).

Есть ли у кого-нибудь мысли об этом?

PS - название также упоминает проверяемую базу данных. В предыдущей системе, о которой я упоминал, всегда есть поле edit_by. Это позволило отслеживать все изменения, чтобы мы всегда могли видеть, кто изменил запись. Не знаете, какая разница.

Спасибо.

Ответ 1

Пересмотрено 01 января 11

Итак, есть пробел между тем, где я сижу (доставляйте полностью проверенные базы данных, а ваше особое требование) и где вы сидите: на основе ваших вопросов и комментариев. Которое мы, вероятно, рассмотрим в комментарии. Здесь можно начинать позицию.

Чтобы обеспечить это требование, нет необходимости вообще: триггеры; массовое дублирование; нарушенная целостность; и др.
Это не классическое временное требование, так что нет необходимости в возможности "периода", но вы можете.
ValidFrom и ValidTo - ошибка нормализации: ValidTo - это данные, которые легко получить; ValidTo в любой строке дублируется в ValidFrom следующей строки; у вас есть аномалия обновления (при обновлении одного столбца в одной строке вам дополнительно нужно обновить другой столбец в следующей строке); вы должны использовать фиктивное значение для "current".
- Все ненужное, используйте только ValidFrom, и держите db в чистом и чистом 5NF.
- Предостережение заключается в том, что если PostgreSQL не может выполнять подзапросы, не попадая в кучу (ala Oracle), то отлично, kep ValidTo.

Все эти вещи доступны для редактирования в системе и удаляются.

Ну, нет. Это база данных, содержащая важную информацию; с ссылочной целостностью, а не с блокнотом, поэтому пользователь не может просто подойти к нему и "удалить" что-то. Это будет противоречить тем же требованиям пользователей для хранения исторических данных (в Рединг, Alert; Ack; Action; Download).

Каскадные удаления запрещены. Эти функции являются флажками для не-баз данных, типов доступа MS. Для реальных баз данных ограничения RI останавливают удаление родителей с дочерними элементами.
Первичные ключи не могут (не должны) быть изменены. Например. Идентификатор пользователя; LocationId; NetworkSlaveCode никогда не изменяется; помните, что их тщательно рассматривают Идентификаторы. Одна характеристика ПК заключается в том, что они стабильны.
Вы можете добавить новых пользователей; вы можете изменить текущее имя пользователя; но вы не можете удалить пользователя, у которого есть записи в Download, Acknowledgement, Action.

В принципе, если он редактируется, он должен быть историческим (поэтому исключает показания и предупреждения).

Также исключает: Загрузки; Выражение признательности; Действия.

И справочные таблицы: SensorType; AlertType; ActionType.

И новые таблицы истории: они вставлены, но они не могут быть обновлены или удалены.

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

Хорошо, теперь вы понимаете, что LocationId (FK) в Sensor не изменится; нет массового дублирования и т.д.? Нет проблем в первую очередь (и есть в этой глупой книге!), Которая во втором месте экспоненциально ухудшается.
~~IsObsolete не соответствует вашим требованиям.~~ (См. ниже)
UpdatedDtm в любой реальной строке (Reading и т.д.) идентифицирует строку истории родителя (FK to Sensor) (ее AuditedDtm), которая действовала в это время.
Полная реляционная способность; Декларативная неопровержимая целостность и т.д.
Поддержка IDEF1X, Реляционная концепция сильных идентификаторов... Существует только одна текущая родительская строка (например, местоположение)
Строки в истории - это изображения текущей строки, прежде чем она была изменена, в объявленном AuditedDtm. Текущая строка (не-история) показывает последний UpdateDtm, когда строка была изменена.
AuditedDtm показывает всю серию UpdatedDtms для любого заданного ключа; и, таким образом, я использовал его для "разделения" реального ключа во временном смысле.

Все, что требуется, это таблица "История" для каждой изменяемой таблицы. Я предоставил таблицы Hiistory для четырех таблиц идентификации: Location; Датчик; NetworkSlave; и пользователя.

Прочитайте это для понимания Аудитируемый в смысле бухгалтерского учета.

Модель данных

Ссылка на Модель данных датчика с историей (Страница 2 содержит таблицы и контекст истории).

Читатели, которые не знакомы с стандартом реляционного моделирования, могут найти IDEF1X Notation.

Ответ на комментарии

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

(Вы подняли аналогичную проблему в другом вопросе.) Возможно, что dbs, с которыми вы столкнулись, на самом деле не имеет ссылочной целостности; что линии связи были там только для документации; что RI был "реализован в коде приложения" (что означает отсутствие RI).

Это стандартная база данных SQL/IEC/ANSI Standard SQL. Это позволяет декларативную ссылочную целостность. Каждая линия отношений реализована как ссылка PK:: FK, фактическое ограничение, объявленное. Например:

CREATE TABLE Location
    ...
    CONSTRAINT UC_PK
        PRIMARY KEY (LocationId)
    ...
CREATE TABLE Sensor
    ...
    CONSTRAINT UC_PK
        PRIMARY KEY (LocationId, SensorNo)
    CONSTRAINT Location_Sensor_fk
        FOREIGN KEY (LocationId)
        REEFERENCES Location(LocationId)
    ...
CREATE TABLE SensorHistory
    ...
    CONSTRAINT UC_PK
        PRIMARY KEY (LocationId, SensorNo, UpdatedDtm))
    CONSTRAINT Sensor_SensorHistory_fk
        FOREIGN KEY (LocationId, SensorNo)
        REEFERENCES Sensor (LocationId, SensorNo)
    ...

Those Declared Constraints are enforced by the server; not via triggers; not in app code. That means:

A Sensor с LocationId, который не существует в Location, не может быть вставлен
A LocationId в Location, который имеет строки в Sensor, не может быть удален
A SensorHistory с LocationId+SensorNo, который не существует в Sensor, не может быть вставлен
A LocationId+SensorNo в Sensor, который имеет строки в SensorHistory, не может быть удален.

(1.1) Все столбцы должны иметь ПРАВИЛА и ПРОВЕРИТЬ Ограничения, чтобы ограничить их диапазон значений. Это в дополнение к тому, что все INSERT/UPDATE/DELETE являются программными, в рамках хранимых процедур, поэтому несчастных случаев не происходит, и люди не подходят к базе данных и не запускают команды против нее (кроме SELECTS).

Вообще я держусь подальше от триггеров. Если вы используете хранимые procs и обычные разрешения, то это:

в SensoryHistory можно было бы добавить запись с параметром UpdateDtm, указывающим дату до того, как существовало бы само местоположение, если вы понимаете, что я имею в виду

. Так что вставка SensorHistory с UpdateDtm раньше самого датчика. Но procs не являются декларативными правилами. Однако, если вы хотите быть вдвойне уверенным (и я имею в виду вдвойне, потому что INSERTS - все через команду proc, прямую команду пользователей), то обязательно, вы должны использовать триггер. Для меня это сверху.

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

Не уверен. Например. Вы согласны с тем, что при удалении Sensor он окончательный... (да, история сохранена)... а затем, когда в Location добавлен новый Sensor, он будет иметь новый SensorNo... нет Sensor, логически заменяемого новым, с или без пробела во времени?

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

И "удалить" Locations, NetworkSlaves и Users.

Ok. Тогда новый Sensor с теми же параметрами, действительно новый, имеет новый SensorNo и не зависит от любого предыдущего логического Sensor. Мы можем добавить IsObsolete BOOLEAN в четыре идентифицирующие таблицы; теперь он считается адекватным. Удалить теперь является мягким удалением.

(2.1) Для NetworkSensor и LoggerSensor, которые фактически зависят от двух родителей: они устарели, если любой из их родителей устарел. Таким образом, нет смысла давать им столбец IsObsolete, который имеет двоякое значение, которое может быть получено из применимого родителя.

(2.2) Чтобы быть понятным, пользователи не могут удалять любые строки из любых таблиц транзакций и истории, правильно?

(3) При обновлении таблицы какой метод лучше всего вставить новую строку в таблицу истории и обновить основную таблицу? Просто нормальные операторы SQL внутри транзакции могут быть?

Да. Это классическое использование транзакции, в соответствии с свойствами ACID, это Atomic; он либо преуспевает, либо полностью отказывается (может быть повторен позже, когда проблема исправлена).

(4) Справочная книга

Окончательный и семантический текст - это временные данные и реляционная модель C J Date, H Darwen, N A Lorentzos. Как и в, те из нас, кто обнимают RM, знакомы с расширениями и что требуется в преемнике RM; а не какой-либо другой метод.

Указанная книга ужасна и бесплатна. PDF не является PDF (без поиска, без индексирования). Открытие моей MS и Oracle говорит; несколько хороших бит, проложенных в большом количестве пуха. Много искажений. Не стоит отвечать на детали (если вы хотите получить правильный обзор, откройте новый вопрос).

(4.1) ValidTo в дополнение к ValidFrom. Серьезная ошибка (как указано в верхней части моего ответа), которую делает книга; затем кропотливо решает. Не делайте ошибку в первую очередь, и вам нечего решать во-вторых. Насколько я понимаю, это устранит ваши триггеры.

(4.2) Простые правила, учитывающие как нормализационные, так и временные требования. Прежде всего вам необходимо глубоко понять (а) временное требование и (б) DataTypes, правильное использование и ограничения. Всегда сохраняйте:

Мгновенно, как DATETIME, например. UpdatedDtm
Интервал как INTEGER, четко идентифицирующий Unit в имени столбца, например. IntervalSec
Период. Зависит от конъюнкции или дизъюнкции.
- Для конъюнкции, к которой это требование, применяется (4.1): используйте один DATETIME; конец периода может быть выведен из начала периода следующей строки.
- Для периодов дизъюнкции да, вам нужно 2 x DATETIME, например, RentedFrom и a RentedTo с промежутками между ними.

(4.3) Они вовлекаются в "Временный первичный ключ", что усложняет код (в дополнение к необходимости запуска триггеров для контроля аномалии обновления). Я уже поставил чистый (проверенный и проверенный) временный первичный ключ.

(4.4) Они испорчены с фиктивными значениями, нереальными значениями и Nulls для "Now". Я не допускаю таких вещей в базу данных. Поскольку я не сохраняю дублированный ValidTo, у меня нет этой проблемы, нечего решать.

(4.5) Нужно задаться вопросом, почему 528-й "учебник" доступен бесплатно в Интернете, в плохой форме PDF.

(5) Я [Пользователь] мог спокойно удалять все строки LocationHistory, например, (оставив только текущую версию в таблице Location), даже если может существовать строка SensorHistory, которая концептуально "принадлежит" предыдущей версия местоположения, если это имеет смысл.

Это не имеет смысла для меня, есть еще пробел в связи, который мы должны закрыть. Продолжайте взаимодействовать, пока он не будет закрыт.

В реальной (стандартной базе данных ISO/IEC/ANSI SQL) мы предоставляем пользователям не GRANT INSERT/UPDATE/DELETE. Мы предоставляем SELECT и REFERENCES только (для выбранных пользователей). Все транзакции INSERT/UPDATE/DELETE кодируются в транзакциях, что означает сохранение procs. Затем мы предоставляем EXEC по каждому сохраненному процессу выбранным пользователям (используйте ROLES для уменьшения администрирования).
- Поэтому никто не может удалять из любой таблицы без выполнения proc.
- Не записывайте proc для удаления из любой таблицы History. Эти строки не следует удалять. В этом случае недопустимость и отсутствие кода есть. Ограничение.
- Технически все строки Истории действительны, нет периода, на который нужно позаботиться. Самая старая строка LocationHistory содержит предыдущее изображение исходной строки местоположения до ее изменения. Самые младшие строки LocationHistory - это предыдущее изображение текущей строки местоположения. Каждая промежуточная строка LocationHistory является действительной и применяется к промежуточному периоду.
- Не нужно "обрезать" или искать несколько строк LocationHistory, которые можно удалить, исходя из того, что они применяются к периоду, который не используется: все они используются. (Определенно, без необходимости проверять любое сопоставление дочерних элементов местоположения с любой строкой (строками) LocationHistory, чтобы доказать это.)
- Нижняя строка: пользователь не может удалить из любой таблицы истории (или транзакции).
- Или вы имеете в виду что-то другое снова?
- Примечание. Я добавил (1.1) выше.

(6) Исправлена одна ошибка в DM. Alert является выражением Reading, а не Sensor.

(7) Исправлены бизнес-правила в другом вопросе/ответе, чтобы отразить это; и новые правила, выставленные в этом вопросе.

(8) Вы понимаете/цените, что, поскольку у нас есть полностью совместимая с IDEF1X модель, re Идентификаторы:

Идентификаторы переносятся по всей базе данных, сохраняя свою силу. Например. при перечислении Acknowledgements их можно напрямую связать с Location и Sensor; таблицы между ними не должны быть прочитаны (и они должны быть, если используются клавиши Id). Вот почему в реляционной базе данных (и больше объединений требуется в ненормализованном) есть меньше фактов, чем требуется для объединения.
Подтипы и т.д. должны быть перемещены только тогда, когда этот конкретный контекст имеет значение.

Ответ 2

Удалено из-за существенного пересмотра ответа. Назовите новый ответ.

Ответ 3

Я тоже столкнулся с этой ситуацией. В зависимости от объема данных, которые вы пытаетесь отслеживать, это может быть сложным. Историческая таблица прекрасно работает для удобства использования, потому что вы можете взять "снимок" записи в таблице истории, а затем внести изменения по мере необходимости в производственную таблицу. Это довольно просто реализовать, однако, в зависимости от того, сколько данных у вас есть и как часто это изменяется, вы можете получить очень большие исторические таблицы.

Другой вариант - регистрировать все изменения, которые позволяют кому-то "воспроизводить" то, что произошло, и отслеживать его. Каждое изменение записывается в таблицу или поле (в зависимости от ваших потребностей), которое отслеживает, кто, когда и что изменилось на то, что произошло. 31 декабря 2010 года Боб изменил статус с "Открыть" на "Закрыто".

Какая система, которую вы хотите использовать, обычно зависит от того, как вам нужно продолжать/просматривать/использовать данные позже. Автоматизированные отчеты, просмотр человеком, некоторая комбинация двух и т.д.

Ответ 4

В зависимости от вашего бюджета и/или среды вы можете захотеть использовать функцию архивного архива Oracle.

Вы можете включить автоматическое "архивирование" строк в таблице, а затем запустить инструкцию в basetable, используя что-то вроде

SELECT *
FROM important_data
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' DAY)

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