Maintain Historical data changes in Parent-child table












0















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
enter image description here



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:
enter image description here



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:
enter image description here



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:



enter image description here



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 ?










share|improve this question





























    0















    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
    enter image description here



    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:
    enter image description here



    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:
    enter image description here



    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:



    enter image description here



    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 ?










    share|improve this question



























      0












      0








      0








      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
      enter image description here



      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:
      enter image description here



      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:
      enter image description here



      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:



      enter image description here



      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 ?










      share|improve this 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
      enter image description here



      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:
      enter image description here



      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:
      enter image description here



      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:



      enter image description here



      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 ?







      sql-server database database-design change-tracking scd2






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 28 '18 at 7:18







      Rahul Roshan

















      asked Nov 28 '18 at 6:38









      Rahul RoshanRahul Roshan

      1926




      1926
























          3 Answers
          3






          active

          oldest

          votes


















          1














          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.






          share|improve this answer

































            0














            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. :)






            share|improve this answer































              0














              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.






              share|improve this answer
























              • Temporal tables which record all data changes, complete with the date and time they occurred is good for a single table record tracking... I need here to maintain parent table record change instances with child table at a particular time...

                – Rahul Roshan
                Nov 28 '18 at 10:42













              Your Answer






              StackExchange.ifUsing("editor", function () {
              StackExchange.using("externalEditor", function () {
              StackExchange.using("snippets", function () {
              StackExchange.snippets.init();
              });
              });
              }, "code-snippets");

              StackExchange.ready(function() {
              var channelOptions = {
              tags: "".split(" "),
              id: "1"
              };
              initTagRenderer("".split(" "), "".split(" "), channelOptions);

              StackExchange.using("externalEditor", function() {
              // Have to fire editor after snippets, if snippets enabled
              if (StackExchange.settings.snippets.snippetsEnabled) {
              StackExchange.using("snippets", function() {
              createEditor();
              });
              }
              else {
              createEditor();
              }
              });

              function createEditor() {
              StackExchange.prepareEditor({
              heartbeatType: 'answer',
              autoActivateHeartbeat: false,
              convertImagesToLinks: true,
              noModals: true,
              showLowRepImageUploadWarning: true,
              reputationToPostImages: 10,
              bindNavPrevention: true,
              postfix: "",
              imageUploader: {
              brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
              contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
              allowUrls: true
              },
              onDemand: true,
              discardSelector: ".discard-answer"
              ,immediatelyShowMarkdownHelp:true
              });


              }
              });














              draft saved

              draft discarded


















              StackExchange.ready(
              function () {
              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53513532%2fmaintain-historical-data-changes-in-parent-child-table%23new-answer', 'question_page');
              }
              );

              Post as a guest















              Required, but never shown

























              3 Answers
              3






              active

              oldest

              votes








              3 Answers
              3






              active

              oldest

              votes









              active

              oldest

              votes






              active

              oldest

              votes









              1














              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.






              share|improve this answer






























                1














                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.






                share|improve this answer




























                  1












                  1








                  1







                  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.






                  share|improve this answer















                  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.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 28 '18 at 9:38

























                  answered Nov 28 '18 at 9:14









                  Naren KNaren K

                  184




                  184

























                      0














                      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. :)






                      share|improve this answer




























                        0














                        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. :)






                        share|improve this answer


























                          0












                          0








                          0







                          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. :)






                          share|improve this answer













                          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. :)







                          share|improve this answer












                          share|improve this answer



                          share|improve this answer










                          answered Nov 28 '18 at 7:22









                          Jason A. LongJason A. Long

                          3,7901412




                          3,7901412























                              0














                              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.






                              share|improve this answer
























                              • Temporal tables which record all data changes, complete with the date and time they occurred is good for a single table record tracking... I need here to maintain parent table record change instances with child table at a particular time...

                                – Rahul Roshan
                                Nov 28 '18 at 10:42


















                              0














                              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.






                              share|improve this answer
























                              • Temporal tables which record all data changes, complete with the date and time they occurred is good for a single table record tracking... I need here to maintain parent table record change instances with child table at a particular time...

                                – Rahul Roshan
                                Nov 28 '18 at 10:42
















                              0












                              0








                              0







                              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.






                              share|improve this answer













                              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.







                              share|improve this answer












                              share|improve this answer



                              share|improve this answer










                              answered Nov 28 '18 at 8:18









                              EralperEralper

                              5,26511322




                              5,26511322













                              • Temporal tables which record all data changes, complete with the date and time they occurred is good for a single table record tracking... I need here to maintain parent table record change instances with child table at a particular time...

                                – Rahul Roshan
                                Nov 28 '18 at 10:42





















                              • Temporal tables which record all data changes, complete with the date and time they occurred is good for a single table record tracking... I need here to maintain parent table record change instances with child table at a particular time...

                                – Rahul Roshan
                                Nov 28 '18 at 10:42



















                              Temporal tables which record all data changes, complete with the date and time they occurred is good for a single table record tracking... I need here to maintain parent table record change instances with child table at a particular time...

                              – Rahul Roshan
                              Nov 28 '18 at 10:42







                              Temporal tables which record all data changes, complete with the date and time they occurred is good for a single table record tracking... I need here to maintain parent table record change instances with child table at a particular time...

                              – Rahul Roshan
                              Nov 28 '18 at 10:42




















                              draft saved

                              draft discarded




















































                              Thanks for contributing an answer to Stack Overflow!


                              • Please be sure to answer the question. Provide details and share your research!

                              But avoid



                              • Asking for help, clarification, or responding to other answers.

                              • Making statements based on opinion; back them up with references or personal experience.


                              To learn more, see our tips on writing great answers.




                              draft saved


                              draft discarded














                              StackExchange.ready(
                              function () {
                              StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53513532%2fmaintain-historical-data-changes-in-parent-child-table%23new-answer', 'question_page');
                              }
                              );

                              Post as a guest















                              Required, but never shown





















































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown

































                              Required, but never shown














                              Required, but never shown












                              Required, but never shown







                              Required, but never shown







                              Popular posts from this blog

                              A CLEAN and SIMPLE way to add appendices to Table of Contents and bookmarks

                              Calculate evaluation metrics using cross_val_predict sklearn

                              Insert data from modal to MySQL (multiple modal on website)