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

EmployeeJobInformation table

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!