Поддержание ссылочной целостности - хорошо или плохо?

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

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

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

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

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

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

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

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

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

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

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

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

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

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, напротив, менее понятны. Они означают, что вы не можете выполнить (жесткое) удаление студентов и курсов - даже если не существует живых строк, которые ссылаются на них. Возможно, это именно то, чего вы хотите достичь, и в этом случае не обращайте внимания на подсказку. В моем случае я нуждаюсь в сокращении пользователей, комментариев, продуктов, заказов и так далее; внешние ключи в журналах истории делают это неудобным.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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