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

Question

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

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

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

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

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

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

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

valid_from
valid_to
edited_by

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

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

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

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

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

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

У кого-нибудь есть мысли по этому поводу?

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

Благодарю.

17

sql database database-design temporal-database

Источник

user391455 20 дек '10 в 15:39

3 ответа

Решение

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

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

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

1

Источник

user314552 20 дек '10 в 16:23

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

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

ВЫБРАТЬ *
ИЗ важных_данных
AS OF TIMESTAMP (SYSTIMESTAMP - ИНТЕРВАЛ '5' ДЕНЬ)

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

0

Источник

user330315 21 дек '10 в 13:10

Другие вопросы по тегам sql database database-design temporal-database

user484814 01 янв '11 в 03:51 2011-01-01 03:51 · Accepted Answer · 2011-01-01 03:51

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Это стандартная база данных SQL ISO/IEC/ANSI. Это позволяет декларативную ссылочную целостность. Каждая строка отношения реализована в виде ссылки 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)
    ...

Эти объявленные ограничения применяются сервером; не через триггеры; не в коде приложения. Это означает:

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

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

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

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

предотвращается. Таким образом, вставляется SensorHistory с UpdatedDtm раньше, чем сам Sensor. Но процы не являются декларативными правилами. Однако, если вы хотите быть уверены вдвойне (а я имею в виду вдвойне, потому что ВСТАВКИ все через proc, прямая команда пользователей), тогда, конечно, вы должны использовать триггер. Для меня это слишком.

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

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

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

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

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

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

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

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

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

(4) Ссылочная книга

Окончательным и оригинальным текстом являются " Временные данные" и "Реляционная модель" С. Дж. Дата, Х. Дарвен, Н. А. Лоренцос. Например, те из нас, кто принимает RM, знакомы с расширениями и тем, что требуется от преемника RM; а не какой-то другой метод.

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

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

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

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

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

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

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

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

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

В реальной (стандартной ISO/IEC/ANSI SQL) базе данных мы не предоставляем права INSERT/UPDATE/DELETE пользователям. Мы ПРЕДОСТАВЛЯЕМ ВЫБОР и ССЫЛКИ только (для выбранных пользователей). Все INSERT/UPDATE/DELETE кодируются в транзакциях, что означает хранимые процедуры. Затем мы предоставляем GREC EXEC для каждого сохраненного процесса выбранным пользователям (используйте ROLES для уменьшения администрирования).
- Поэтому никто не может удалить из любой таблицы, не выполняя процедуру.
- Не пишите процедуру для удаления из любой таблицы истории. Эти строки не должны быть удалены. В этом случае отсутствие разрешения и отсутствие кода является ограничением.
- Технически, все строки Истории действительны, нет Периода, о котором вы могли бы беспокоиться. Самая старая строка LocationHistory содержит предварительное изображение исходной строки Location до ее изменения. Самые младшие строки LocationHistory - это изображение перед текущей строкой Location. Таким образом, каждая промежуточная строка LocationHistory действительна и применяется к промежуточному периоду.
- Нет необходимости "обрезать" или искать несколько строк LocationHistory, которые можно удалить на основании того, что они применяются к неиспользуемому периоду: все они используются. (Определенно, без необходимости проверять какое-либо отображение дочерних объектов Location на любую строку (и) LocationHistory, чтобы доказать это.)
- Итог: Пользователь не может удалить из какой-либо таблицы истории (или транзакции).
- Или ты опять имеешь в виду что-то другое?
- Обратите внимание, что я добавил (1.1) выше.

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

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

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

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