History of relationships

[Marcwa19197]

Hi,

im searching for a solution which will let me save the relationship history of records. I want to have something similar like a "snapshot" of every record if it gets modified.

in my example i want so save which employee had which jobs and childs over time, so i can view the past history of a specific employee.

What do you think will be the best solution for this problem?

Thanks in advice.

Edited December 15, 2015 by Marcwa19197

[comment]

22 minutes ago, Marcwa19197 said:

i want so save which employee had which jobs and childs over time,

I understand the jobs part, but not the children part. People don't have children "over time". With regards to jobs, your solution seems fine - assuming that you want to add jobs from a layout of Employees, and assuming (based on your previous question) that the relationship between Persons and Employees is one-to-one.

I would add StartDate and EndDate to the Jobs table. And I would validate PersonID in the Employees table as unique (this will make your graph clearer).

This is assuming you are not tracking Positions.

[Marcwa19197]

8 minutes ago, comment said:

 I understand the jobs part, but not the children part. People don't have children "over time".

In my example, we need to track the childrens of persons until they get of age, so you are right anyway, i can simply add an Start- and EndDate to every table.

My screenshot is a example with a few tables, the real database have much more we need to track history (Bankinfo, Address/Contactinfo, IT-Accounts, ...).

Is this still the recommended/best way?

 

25 minutes ago, comment said:

and assuming (based on your previous question) that the relationship between Persons and Employees is one-to-one.

yes, the relation between persons and employees is one-to-one. But there is a new "problem". If some person change his name, we need to track the old one. (so i think, i need an one-to-many relation here?)
 

27 minutes ago, comment said:

And I would validate PersonID in the Employees table as unique (this will make your graph clearer).

So, i dont need an unique Employees.ID field, only the Employees.Persons_ID Field as unique field?

[LaRetta]

Hi Marc,

You will always want a unique auto-enter ID in every table so yes, you will still want a unique auto-enter EmployeeID in the Employee table.  Why should every table have a unique individual ID?  Because if you ever have to retrieve records from a backup (for example if someone deletes records they shouldn't), you can match on this ID and pull back in those lost records.  Another example is if a power user with ability to run script which changes several records makes a mistake, you can update those records using a backup which also must match on this unique ID.

If you hope to add the unique ID after the fact, it will be too late of course so I recommend that you always include one - even in join tables.  :-)

Edited December 16, 2015 by LaRetta

[Rick Whitelaw]

Yes LaRetta,

Every table should have a unique id but not always auto entered serial. Child tables should, as you know, have a foreign key inherited from the record in the parent table and this should not be an auto entered serial. At least as the match field. It doesn't hurt to have an auto enter serial field in a child table but I've never seen the point.

[webko]

But a foreign key is by it's very nature usually not unique...

And you never know when you will want a new child table from the child table.

As a matter of course, I just have a unique primary key field in every table...

[Marcwa19197]

5 hours ago, LaRetta said:

Hi Marc,

You will always want a unique auto-enter ID in every table so yes, you will still want a unique auto-enter EmployeeID in the Employee table.  Why should every table have a unique individual ID?  Because if you ever have to retrieve records from a backup (for example if someone deletes records they shouldn't), you can match on this ID and pull back in those lost records.  Another example is if a power user with ability to run script which changes several records makes a mistake, you can update those records using a backup which also must match on this unique ID.

If you hope to add the unique ID after the fact, it will be too late of course so I recommend that you always include one - even in join tables.  :-)

Yes, i know this fact. I was just a bit confused, of course i will have unique auto-increment IDs in every table.

How  do you think about the "history problem"?

[LaRetta]

I think Comment's advice is spot on; typical for Comment. :-)

[Marcwa19197]

Hi,

thanks for the replies.

here is my new database design.

now i can track which Person has which childs, and if the child is of age i will set the "Aktiv" flag to 0.
same for adress, if a Person have a new adress i will set the old one to "Aktiv" = 0 and enter the new adress with "Aktiv" = 1.

for the jobs i have added an Start- and End Date.

How can i track changes of Person names or changes in the Employees Table? 
For example:
1. Person gets married and change last name
2. Person is an employee and gets a new roomnumber
How can i track the old name/roomnumber?

[LaRetta]

On 12/15/2015 at 10:32 AM, comment said:

And I would validate PersonID in the Employees table as unique (this will make your graph clearer).

Hi Marc,

You still have not set PersonsID to unique in the Employees table.  I know this because you have a fork on the end in your graph on Employees (both from Persons TO and also on the other end from Jobs TO) which indicates multiple same PersonsIDs are allowed in the Employees table.  So first, I would make that change, making PersonID unique in Employees.

As for tracking Employee Room and Persons LastName history ... I see no reason that you can't have same setup as your addresses, namely a table off Persons called NameHistory and one off Employees called EmployeeRooms or PersonRooms - either would work.

What if the business wants to then track first name change or other bits of person information or employee salary history, for example?  You can also just use a table called History with unique ID, PersonID, ActionDate, Type (address, name, room or fieldname) and prior value or you can use an actual audit table.  A Google search will provide many examples such as this one: NightWing Ultra Log.

Comment may have other ideas.  I suggest that you discuss the requirements with management and then choose best method based upon the possibilities you will need, otherwise they may ask for you to track history on additional details next week or next month and you'll be changing/expanding the structure to accommodate as you've had to do here with the new fields Room and LastName.

Personally, I would not have a Childs table at all.  People are people.  But then again, much depends upon your needs ... you can have a field ParentIDs with multiline of the mother and father and self join the relationship.  Otherwise, what if the child grows and becomes an Employee or a parent?  Without knowing more about your business needs, we can't give you these answers - only point out that the questions should be asked first.

Edited January 8, 2016 by LaRetta
added closing parenthesis omitted in error

[Marcwa19197]

4 hours ago, LaRetta said:

You still have not set PersonsID to unique in the Employees table.  I know this because you have a fork on the end in your graph on Employees (both from Persons TO and also on the other end from Jobs TO) which indicates multiple same PersonsIDs are allowed in the Employees table.  So first, I would make that change, making PersonID unique in Employees.

Now i understand, what you mean with the unique ID, because Persons to Employess is a one-to-one relation. Thank you for the hint with the fork, i didn't see that before.

4 hours ago, LaRetta said:

What if the business wants to then track first name change or other bits of person information or employee salary history, for example?  You can also just use a table called History with unique ID, PersonID, ActionDate, Type (address, name, room or fieldname) and prior value or you can use an actual audit table.  A Google search will provide many examples such as this one: NightWing Ultra Log.

The best for our buisness would be to save all data historically. On the design side, there will be two "edit-modes", "Correction" and "Change".
Correction: only for correcting mistakes, no audit logging needed here
Change: for example the change of the LastName, every change in this "mode" should be logged.

So are there any free "examples" for doing this? Or any "easy" way to do this in FM (maybe buildin functions)?
in the past i read much about "NightWing Ultra Log", but is anyone using it to log changes over much relationships?

At the moment we have a flat database-design, like an excel sheet, realised with FM 10 ...
So if something changes, the management copy the whole "record" and add a note to it why it was changed..

[comment]

4 hours ago, Marcwa19197 said:

On the design side, there will be two "edit-modes", "Correction" and "Change".

A logging mechanism cannot distinguish between the two. You would have to modify it to act only when [something that signifies the current mode is Change].

Another option is to use a related table to hold the values whose changes must be tracked. For example, to keep a history of person's names, you would have to enter all person's names into a Names table, probably with fields for:

PersonID
Type
Name
StartDate
EndDate

Then the current names of a person would be those that do not have an EndDate.

This is a rather complicated setup (both options), so make sure you really need it. I suspect most business do not need to track more than one previous name.

[LaRetta]

16 hours ago, Marcwa19197 said:

in the past i read much about "NightWing Ultra Log", but is anyone using it to log changes over much relationships?

Ultra Log (as in most audit solutions) is applied to each table you wish to track.  Ultra Log writes the changes to each record itself using auto-enter (replace).   I've used Ultra Log many times and it works very well.

As Comment explains, audit systems can be complex.  A good audit system can allow rollback and undelete as well, but that requires all-field tracking which is rarely needed by most businesses except governmental or financial institutions for HIPAA.  If you wish for full audit trail with rollback and undelete but yet is easy to implement, I've used FMDataGuard also and I can highly recommend it.  Ultra Log can also be designed to allow rollback but not undelete without additional scripting since the changes are written to the record that is deleted).

Good luck on your design!  :-)

[Marcwa19197]

We want to use our own logging system. So is there any script snippet for doing the following:
- Get current data of a field
- Modify this data in the field
- Save the old Data and the new (modified) data to a LogTable (Something Like: "PersonA's LastName was changed from "Marcwa19197" to "Marcwa" by "Username" on "Date")

What i found is the GetField() function. So my idead would be:
1. Save the current data of a field with the GetField() function in a global variable (with the OnRecordLoad Trigger)
2. Modify the fielddata and save the changes
3. Check if there is a change between the old data and the new data (if yes, the fielddata was modified)
4. Save the new/modified data of a field again with the GetField() function and write the data captured in step 1. with the changed data to a LogTable. (Using the OnRecordCommit Trigger)

Or is there a simplified way?

Edit:
So, i tried my above solution, it works but it will be a huge effort to set the triggers on every object in the layouts..
a other solution i was thinking about would be:
1. Have a table layout (PersonsEmployeeTable), which will show employee and persons data at all
2. Have a form layout (EditEmployee), where i can edit the employee's and the associated person information (Name,LastName, Roomnumber, ..)
  On this layout there will be a script on the OnRecordLoad-Trigger which will perform a find in the PersonsEmployeeTable, the result will be all information about the employee im viewing. The return of the find should be saved in an array. (So i have an array filled with the current record data of the employee/person) Is this step possible in FM (creating an array and fill it with the return data of the performed find)?
3. A script on the EditEmployee layout which is set to the OnRecordCommit-trigger will capture the modified data (like step 2.), make a diff with the data in step 2. and check if there are any changes, if so the old data and the new data will be saved to the Log.

 

 

Edited January 15, 2016 by Marcwa19197