Learning basics of Date Effectivity in ADF

This post explains the basics of date-effectivity which include mainly the basic date-effective operations like Date 'Effective Search', 'Date Effective Create', 'Date Effective Update' and 'Date Effective Delete'. But, it'll be easier to explain the concepts with examples.

So, here for example, I'm taking the simple example of HR admin, who can search/create/update/correct jobs. Let us assume, each job has a name, code and other attributes like job level, if the job requires medical checkup required, minimum salary, maximum salary, etc. What makes the job is date effective is that the attributes of job may change over the time, but still we need to be able to search the jobs based on the attributes that were applicable previously or that will be applicable in future.

For instance, let us take a job with Name 'Java Associate' which was created on 01-01-2000. When the job was created, the minimum salary for the job (minSal) was 10000 and maximum salary (maxSal) was 15000. But, on 01-01-2005, the job was revised and the management decided to move their technology to ADF and changed the job name to 'ADF Associate'.On 01-01-2010, the same job was revised and salary ranges were updated to minSal 25000 and maxSal 40000. Again, the management decided to change all Associate jobs to Developer Jobs from 01-01-2012 onwards (i.e., the job name will be changed from 'ADF Associate' to 'ADF Developer') etc.,

Here, the requirement is that if the user knows the name of the job applicable in 2000, he should be able to search the job as of that date. Similarly, if the user wants to search as of the current date or future dates, he should be able to search as well. That means we shouldn't simply update the existing values of the attributes as it will update the existing values and the previous values would be lost! We need to keep different versions of the attributes for the same job. So, how will it be possible as to have the multiple records with same JobId? Here comes EffectiveStartDate and EffectiveEndDate. Actually, we need to have a composite key based on JobId, EffectiveStartDate and EffectiveEndDate(i.e., the combination of these 3 attributes should be unique).

So, the data in the above requirement can be shown in table as follows. Changed attributes for each date-effective update are highlighted in yellow.

If you see the above table, you'll notice that for any change in the Job attribute, we have new row with the corresponding EffectiveStartDate and EffectiveEndDate and there are no gaps in between. i.e., for any given date, only one record is applicable among multiple date-effective rows of a single Job record.

Now we'll see what date effectivity means in ADF for different operations on the records.

Date Effective Search:

For example, if we want to find the jobs whose name starts with 'ADF%' as of date 01-01-2006. Or, we want to find out the jobs which will be applicable after 01-01-2012. Or, we want to find the minSal and maxSal for a given job as of 01-01-2007. Here, the results would change changing the Effective Dates as each job would have different values for the same attribute for different effective dates. Date-effective search will make all these possible. Date-effective search is explained with an example here.

Date Effective Create:

For example, we want to create a job that will be applicable in future say from 01-01-2013. Or, I want to create record which should be applicable from the past say 12-12-1900. Or, I want to create a new job which should be effective from today. Date-effective create makes all of these possible.

Date Effective Update:

For example, we want to insert a new date-effective record for the job to represent a change in one of the attributes of the job. Say, I want to update the maxSal of the job with jobId 1001 to 20000 from 01-01-2008. This will insert a new record for the same job with EffectiveStartDate 01-01-2008 with maxSal 20000. Different date-effective update modes and the corresponding behavior has been explained here.

Date Effective Correct:

For example, I want to correct or change one or more of the attributes of one of the date-effective rows of a single job record. Date Effective Correct operation just corrects the existing data. It won't insert any new date-effective rows. You can find the example of DE correction mode here.

Date Effective Delete:

For example, we want to delete a single date-effective record out of multiple date-effective records of a job. For instance, we want to delete the job record [1001, 01-01-2005, 31-12-2009]. If the record is non-date effective, then deleting the row means deleting the entire record as there won't be multiple rows. But, in case of date-effective objects, if you want to delete entire record, you need to delete all date-effective rows of that record if any.

Now, you understand the basics of date-effectivity in ADF. If you need any clarification, please leave a comment.