Аудиторские таблицы: поддержание ссылочной целостности - хорошее или плохое

Ответ 1

При обсуждении аудита я хотел бы вернуться к цели, стоящей за ней. На самом деле это не резервная копия, а история того, что было. Например, для StudentScore вы должны быть уверены, что не потеряете тот факт, что у ученика первоначально было 65%, когда у них теперь 95%. Этот контрольный журнал позволит вам пройти через изменения, чтобы узнать, что произошло, и кто это сделал. Из этого вы можете определить, что сделал конкретный пользователь, чтобы злоупотреблять системой. В некотором роде это может быть типом резервного копирования, так как вы можете откатить эти изменения в свои предыдущие состояния, не откатывая целые таблицы.

С учетом этого (если мои предположения о том, что вы используете для этого, правильны), единственное место, в котором вы хотите установить отношения FK/PK, находится между таблицей истории и ее "живым" коллегой. Таблица аудита (история) не должна ссылаться на какую-либо другую таблицу, поскольку она больше не является частью этой системы. Вместо этого это просто запись того, что произошло в одной таблице. Период. Единственная ссылочная целостность, которую вы, возможно, захотите рассмотреть, находится между таблицей истории и текущей таблицей (таким образом, возможными отношениями FK/PK). Если вы разрешаете удаление записей из таблицы в реальном времени, не включайте FK в таблицу истории. Затем таблица истории может включать в себя удаленные записи (это то, что вы хотите, если разрешаете удаление).

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

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

Ответ 2

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

Что касается информации в таблицах PK, перейдите в emptor. Настройка FK была бы простым способом получить некоторую способность отслеживания, но она не будет идеальной. Есть компромиссы. Чтобы получить абсолютно идеальную историю, вам в основном понадобится создать резервные копии всех связанных записей, в любое время, когда запись кандидата аудита что-то произойдет. Вам нужно выяснить уровень детализации, который подходит и идти с ним, потому что отличная запись событий может быть сложной для настройки и съесть много места в этом процессе.

Кроме того, это может быть или не быть вариантом для вас, но я бы настоятельно рассмотрел комбинацию таких инструментов, как ApexSQL Audit + ApexSQL Log в отличие от решения для доморощенного аудита. Основываясь на ваших потребностях, эти два инструмента в сочетании с архивированием журналов транзакций периодически охватывают то, что вам нужно делать. Инструмент аудита может хранить данные в одном и том же месте или в другом месте, а инструмент журнала может выборочно восстанавливать данные. Просто мысль.

Ответ 3

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

Вместо сохранения CourseID как "1" это будет "HTML4". Таким образом, если значение внешнего ключа удалено, таблица аудита остается в силе. Это также будет справедливо, если значение внешнего ключа будет изменено с "HTML4" на "HTML5" в любое время в будущем. Если вы сохранили только внешний ключ, вы должны сообщить аудитору, что предыдущие ученики сделали "HTML5", что неверно.

Еще одно большое преимущество - возможность отправлять контрольные журналы на другой сервер для интеллектуального анализа данных без каких-либо проблем.

Я использовал эту установку некоторое время, и она работает для меня.

Ответ 4

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

Предположим, например, что у вас есть что-то вроде этого:

table scores (
 score_id,
 student_id ref students (student_id),
 course_id ref courses (course_id),
 score_date,
 score,
 pkey (score_id)
)

В этом случае имеет смысл иметь привязку к каскаду fkey для удаления (score_id) на score_logs. Это объект; если он становится жестким, может также отбросить историю.

Внешние ключи на student_id и course_id, напротив, имеют меньший смысл в моем опыте. Они означают, что вы не можете делать (жесткое) удаление на студентах и ​​курсах, даже если нет прямых строк, которые ссылаются на них. Это может быть то, чего вы хотите достичь, и в этом случае игнорировать подсказку. В моем случае я нуждаюсь в обрезке пользователей, комментариев, продуктов, заказов и т.д.; внешние ключи в журналах истории делают это неудобным.

Также обратите внимание, что есть случай, когда fkey работают против вас. Если у вас есть строка заказа на заказ, и строка заказа удаляется, вам все равно нужна история в этой строке заказа. Правильный ключ p, который нужно использовать в этом случае, - order_id, а не order_line_id.

Последнее замечание, в случае, если вы в конечном итоге решили сохранить fkeys: подумайте, на что они должны ссылаться. С разрозненными фрагментами данных (например, студентами и курсами) разумно предположить, что живая строка в порядке. Однако с сильно связанными частями данных (например, продуктов и промо) то, что вы действительно хотите, должно ссылаться как на fkey, так и на его версию.

Повторяем две предыдущие точки, вы можете найти этот связанный поток и ответить интересным:

Как создать контрольный журнал для совокупных корней?

Ответ 5

Если ваша система действительно сосредоточена на обработке транзакций, то мой ответ может не очень хорошо применяться к вам, но в мире datawarehouse/BI эта проблема часто решается с помощью "звездной схемы". В этом подходе вы денормализуете важную ориентировочную информацию из связанных таблиц вместе с вашими учетными записями. Это может включать значения PK родительских таблиц (т.е. Значения FK в вашей проверенной таблице). Тем не менее, вы не сохранили бы сами ограничения ссылочной целостности.

Итак, для вашего примера ваша таблица StudentScoreHistory может сохранить столбец StudentID без ограничения FK, а также, возможно, StudentName (или то, что, по вашему мнению, вам может понадобиться от Student). Таким образом, вы можете вернуться в свой контрольный журнал, чтобы собрать вместе то, что произошло, и не беспокоиться о том, жестко ли вы или мягко удаляете родительские записи. Это имеет дополнительное преимущество (или недостаток, в зависимости от вашей перспективы) отслеживания атрибутов изменяемой родительской таблицы, как это было, когда детская запись была первоначально записана. Например, было бы полезно узнать, что ученик 123456, который теперь является миссис Марридлади, когда-то была мисс Синглгил, когда ей была присуждена степень биологии.

Ответ 6

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

Вы поднимаете вопрос о мягких ударах, что смущает проблему. Это будет иметь значение только в том случае, если вы планируете использовать внешние ключи между двумя схемами, например. StudentScoreHistory ссылки StudentScore. Это может быть правильным дизайном, но опять же, это говорит о том, что вы не доверяете своему механизму аудита. Лично я предпочел бы иметь жесткие удаления в живых таблицах и записать факт удаления в таблице "История" . Мягкие удаления - еще один источник горя.

Но в любом случае это другой вопрос. Вполне возможно иметь внешние ключи между живыми и историческими версиями каждой таблицы, например. StudentScoreHistory -> StudentScore, не применяя также реляционную целостность в схеме истории, например, StudentScoreHistory -> StudentHistory.

Ответ 7

В первый раз, когда я впервые внедряю очень похожую систему аудита, в настоящее время я сталкиваюсь с этой же проблемой. Мое мнение повторяет мнение BiggsTRC - ваша "живая" таблица поддерживает отношение FK к записи курса, и ваша таблица истории поддерживает отношение к ее "живому" аналогу (StudentScore). Это, я думаю, не имеет сирот в таблице аудита.

Теперь есть что-то еще, о чем я не упоминал в ответах: в нашем текущем проекте мы видели ценность сохранения FK в таблице истории в таблице CourseHistory, чтобы мы знали, что такое "состояние", записи курса во время записи аудита StudentScoreHistory. Конечно, это может или не важно для вас, в зависимости от вашей системной логики.

Наше решение вашей проблемы (в вашем ответе на BiggsTRC) о том, что вы могли бы иметь один и тот же курс несколько раз, заключалось в том, чтобы ссылаться не на фактический CourseId, а на столбец PK таблицы CourseHistory. Мы до сих пор не имеем твердого решения о том, как это сделать - хотим ли мы создать запись аудита в записи курса, даже если бы не было изменений, или попытайтесь ввести какую-то логику для поиска записи в формате CourseHistory, соответствующей соответствующему курсу состояние во время записи StudentScoreHistory.

Ответ 8

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

Ответ 9

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

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