Building effective dated records in Django

Building effective dated records in Django

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.

effective dated explanation

  • 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.

new record insertion

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

100John Parker
200Ryan Hoover
300Mark Doe
400Jason Leo
500George Stan

EmployeeJobInformation table

1UUID101 Jan 2010100200Product EngineeringIntern
2UUID115 Dec 2010100300Product EngineeringIntern
3UUID207 Dec 2009200400Product EngineeringProduct Engineer
4UUID316 Mar 2012300500Customer SupportSupport Engineer
5UUID102 Feb 2012100500Product EngineeringSoftware Developer
6UUID116 Mar 2012100400Product EngineeringSoftware 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.

model design

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.

effective dated util

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!