Maintain Historical data changes in Parent-child table

Question

1 Employee has N Address. Here I need to maintain the historical information of Employee and Address changes if any changes is done by any users in these two table.

Table Employee:

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

Address Table :

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

Above,EmpID is a foreign Key to the Employee table

Scenario I have to satisfy :

1. I should be able to track the changes of an individual address(Child table records) record of any employee.
2. I should be able to track the track the changes of a Employee(Parent table records) with child address record.

I thought following way: Suppose, Initially it is in the state shown in image below

Solution 1:

Case : when child table gets updated Now, I update a Add0001 Address Record, So i insert a new record in address table making previous record inactive as:

Case : when Parent Table gets updated Now, When Parent Table gets update, I have history table for the Parent Table and i am moving old data to the history table and update the current records into the parent table as shown:

Solution 2 :

Case : When child table gets updated Same as in solution 1

Case : When Parent Table gets updated

We insert a new record in the parent table making previous records inactive. In this case we get a new ID and that ID, we update as foreign key to the child tables as shown below:

Is this the best way of maintaining historical data of parent-child table together? or is there any way i can keep the design so that i should be able to track the changes altogether of parent and child records data ?

Answer 1

Are you able to use Temporal tables and history tables introduced with SQL Server 2016?

These enable data professionals to keep history of data on related table, so you don't need to think about parent or child, etc.

Answer 2

If the parent data changes are not that frequent then you can maintain the history record of the parent also in the same table and update the foreign keys of the child tables.

Before Changes to Parent

Now if you change the name of the employee and add a new address, then update the employee id in the child table(Address).

After Changes to Parent

You can always get the addresses of the employee before the name has changed using the valid time. This way, we need not create an additional history table. But it may be little complex to fetch the history doing all the date comparisons.

Any suggestions are welcome.

Answer 3

There are quite a few ways to go about this sort of thing and what you're proposing is a perfectly valid approach... At least you appear to be pointed in the right direction.

There are a couple of changes that I would suggest...

1) Get rid of the "status" flag and use "begin" and "end" dates. The specific names don't matter so long as you have them.

2) Both the begin and end date columns should be defined as "NOT NULL" and begin should have a default constraint of GETDATE() or CURRENT_TIMESTAMP. The end date should be defaulted to '99991231'. Trust me and fight the urge to make the end date NULLable and giving "active" rows NULL end dates. '99991231' is, for all practical purposes, the end of time. and can be used to to easily identify the currently active rows.

3) I would suggest adding a trigger to the following:

• a) prevent updates and/or deletes. Ideally this would be an insert only table.
• b) When new rows are inserted, update (yea I know what "a)" says) the the "existing current" rows end date with the "new current" rows begin date. By doing this, you will have a continuous, gap free history.

Hope this helps. :)