Ведение исторических изменений данных в родительско-дочерней таблице

Question

1 Сотрудник имеет N Адрес. Здесь мне нужно сохранить историческую информацию об изменениях Сотрудника и Адреса, если какие-либо изменения были сделаны любыми пользователями в этих двух таблицах.

Стол сотрудника:

Employee(
EmpID BIGINT PRIMARY KEY IDENTITY(1,1),
Name varchar(200),
EmpNumber varchar(200),
Createddate Datetime2)

Таблица адресов:

Address(
AddID BIGINT PRIMARY KEY IDENTITY(1,1),
AddressLine1 varchar(300),
AddressLine2 varchar(300),
EmpID BIGINT NULL,
AddressType varchar(100),
Createddate Datetime2)

Выше,EmpID является внешним ключом к таблице Employee

Сценарий, который я должен удовлетворить:

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

Я подумал так: предположим, что изначально он находится в состоянии, показанном на рисунке ниже.

Решение 1:

Случай: когда дочерняя таблица обновляется сейчас, я обновляю запись адреса Add0001, поэтому я вставляю новую запись в таблицу адресов, делая предыдущую запись неактивной:

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

Решение 2:

Случай: когда дочерняя таблица обновляется То же, что в решении 1

Случай: когда родительская таблица обновляется

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

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

Answer 1

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

Есть пара изменений, которые я бы предложил...

1) Избавьтесь от флага "status" и используйте даты "begin" и "end". Конкретные имена не имеют значения, если они у вас есть.

2) И столбцы даты начала и окончания должны быть определены как "NOT NULL", а начало должно иметь ограничение по умолчанию GETDATE() или CURRENT_TIMESTAMP. Дата окончания должна быть по умолчанию "99991231". Поверьте мне и побороть желание сделать дату окончания NULLable и дать "активным" строкам NULL даты окончания. "99991231" для всех практических целей конец времени. и может использоваться, чтобы легко идентифицировать текущие активные строки.

3) Я бы предложил добавить триггер к следующему:

• а) предотвратить обновления и / или удаления. В идеале это будет таблица только для вставки.
• б) Когда вставляются новые строки, обновите (да, я знаю, что говорит "а)" дату окончания "существующих текущих" строк датой начала "новых текущих" строк. Делая это, вы будете иметь непрерывную историю без пробелов.

Надеюсь это поможет.:)

Answer 2

Можете ли вы использовать временные таблицы и таблицы истории, представленные в SQL Server 2016?

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

Answer 3

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

До изменений в родительском

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

После изменений в родительском

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

Любые предложения приветствуются.