Effective dated records are used mostly in enterprise applications where you want to keep the history of some data. Not only enterprise applications, this method of keeping data can be seen across other applications as well.
But what do you mean by effective dated?
Let's say we want to store an employee and it's job information. The job information should contain the manager of the employee, the department name of the employee, and the position employee holds.
- For
John Parker,
- From 01 Jan 2010 to 14 Dec 2010
- Manager:
Ryan Hoover
- Department:
Product Engineering
- Position:
Intern
- From 15 Dec 2010 to 15 Mar 2012
- Manager:
Mark Doe
- Department:
Product Engineering
- Position:
Intern
- From 16 Mar 2012 to till date
- Manager:
Jason Leo
- Department:
Product Engineering
- Position:
Software Developer
This is how the data should look like. If you want to check who was John Parker's manager on 01 Jan 2012, it should return Mark Doe.
If a new record is added, the dates should be updated accordingly.
Now, on 10 Feb 2012, John Parker's manager will be George Stan. We have the entire history of John Parker.
Database design
In the table which we want to be effectively dated, we will add two new fields. One will be a UUID field and the other will be a date field which will specify when this record will be effective.
Since there will be multiple effective fields of a single item (eg. John Parker's job information), we will save all of them under the same UUID.
Employee table
| id | employee_name |
| 100 | John Parker |
| 200 | Ryan Hoover |
| 300 | Mark Doe |
| 400 | Jason Leo |
| 500 | George Stan |
| id | uuid | effective_from | employee_id | manager_id | department | position |
| 1 | UUID1 | 01 Jan 2010 | 100 | 200 | Product Engineering | Intern |
| 2 | UUID1 | 15 Dec 2010 | 100 | 300 | Product Engineering | Intern |
| 3 | UUID2 | 07 Dec 2009 | 200 | 400 | Product Engineering | Product Engineer |
| 4 | UUID3 | 16 Mar 2012 | 300 | 500 | Customer Support | Support Engineer |
| 5 | UUID1 | 02 Feb 2012 | 100 | 500 | Product Engineering | Software Developer |
| 6 | UUID1 | 16 Mar 2012 | 100 | 400 | Product Engineering | Software Developer |
The Django model design is given below. We create an abstract class called EffectiveDated which has two fields uuid and effective_from. It also has one model manager which we will discuss later. The model which needs to be effectively dated, we will inherit the EffectiveDated class in that model.
Creating data
There are some things to keep in mind while creating or updating the data.
- If new data is created, then normally create the data. A new UUID will be assigned to the record.
- If data is being updated,
- For the record with the existing date, then just update that record.
- For the record with a new date, then create a new record with the same UUID.
In the serializer, if it is a create record, just create the record normally. If it's an update case, we will call the EmployeeJobInfoEffectiveDatedUtility which will handle the update scenario.
The EmployeeJobInfoEffectiveDatedUtility checks if the effective_from already exists, if yes then updates the existing record, or creates a new record with the existing UUID.
The model manager & querying data
The model manager added in our abstract class EffectiveDated will help us in querying data. We have added two methods, active_all which returns us all the active records, and active_by_field which returns one record based on a query.
So, we can query like this!
Cool, right!
This was how we can implement effective dated records in Django REST Framework.
Thanks for reading!
