How do I update a user on the effective day?
We are currently developing a custom web application in Java.
A user can have many properties such as first name, last name, region (for example, Asia, Europe, US, UK), country, department, branch, product, etc.
There are regular CRUD screens for this user with all the above fields.
In the add / edit screen, there will also be a date field for the user called effective date. The way to add / modify a user differs from the usual add / edit in normal CRUD, is that the update made to the user will not show up until the effective date.
Suppose today is April 6th, and I add a new user with the region "Asia" and the effective date is April 10th. And now I go and change the same user and change my region from Asia to US, but the effective date is May 15th.
Thus, until the 15th year, the system can show its region only as Asia, and on May 15th, its region can automatically go to the USA.
You can think of it as a user who works in Asia in April, but from the 15th year he can go to work in the USA i.e. the area changed from the effective date in Asia to the US on May 15th. Thus, until the 15th year, it can only be shown as a user in Asia.
So I can't just update my region from Asia to us in the database as a simple update operation.
And this applies to many other areas such as division, branch and product.
I cannot have multiple entries for the same user.
EDIT 1:
We should also be able to see the user's history or future updates.
As of April 6, I should see the user area change from May 15, and its division changes from A to B starting May 10.
I can also uninstall the updates, I will say I will find out later that the proposed user transfer from Asia on our 15 effective date might not happen now, so I should be able to uninstall this update.
EDIT 2: -
Given the situations above, how do I make changes to my original user table and user changelog table?
Tell a user with regional asia on a system that will change from asia to us in the next few weeks. the user will have the same update for the user. It changes the region from Asia to User and chooses the effective date as some future date.
Now How to check if region has changed from asia to us (there may be namy mre fields like region). Do I have to do this at the code level, or can I do it at the database level using triggers, etc.?
Please help me develop a system and database for this.
source to share
I suggest you implement this system by supporting CHANGELOG table and schedulerthat will run at a specific time every day.
CHANGELOG table Attributes:
- TargetTable
- targetPrimaryKey
- targetColumn
- targetValue
- effectiveDate
Now, whenever an update is made in the required fields, insert the appropriate record into the change table.
Example,
- targetTable: User
- targetPrimaryKey: 3 (the primary line number of the user's record)
- targetColumn: Region
- targetValue: US
- effectiveDate: May 15, 2012
The scheduler will now run every day (at 12:00 AM) and will check for changes made on that day in the CHANGELOG table and update the corresponding target tables.
Note. This approach has 2 advantages:
-
You don't need to keep two records for one record.
-
You can customize multiple fields (area, division, branch) and multiple tables.
source to share
You can create a mapping table as User_Region
containing User_ID
, Region_ID
and Change_Date
. Change_Date
will give when the transition occurs in the region for the user. When the modified date is zero, that may mean that the user is currently in the region itself.
Then you can have a list of regions for User_ID
along with dates that can be displayed according to your convenience.
CREATE TABLE User_Regions{
User_ID INT,
Region_ID INT,
Change_Date DATE,
FOREIGN KEY User_ID REFERENCES User(ID),
FOREIGN KEY Region_ID REFERENCES Region(ID)
};
source to share
The historical table would do the trick. This means you have as many history records with a date column and only handling the one closest to the current date as the current one. Otherwise, you need to keep the update history in a separate table and do a batch update to update the changes. I would recommend trying to overcome the hurdle of unavailability to have multiple records for the same user by going to a normalized user object structure: the users table will have key columns (like id) and a merged table containing historical updates with the rest of the columns.
source to share
Here you have two possibilities:
- You have another table that looks like your custom table and with an extra column "validFrom" and then the cron job (quartz) is updated every morning.
- You add a column to your normal users table along with the actual value and modify all of your queries to reflect this.
As you said, you cannot have multiple entries, so 2. would be a bad decision. Stick to 1.
source to share
a "pure" data model will include the date in the key and have multiple releases, but there are some problems with changing future events.
I think I will try to simulate future changes, since the list of "activities" and changing the field in the future is actually planning an operation that will change the field on that date.
The application periodically applies operations as they occur.
In a GUI, it is trivial to display a list of pending operations and it is easy to undo or modify future operations.
You can configure rules to restrict or warn conflicting operations, but if the list is clearly visible, this is not a problem.
source to share
In my opinion you should use the Event statement. Here are 2 events:
CREATE EVENT myevent
ON SCHEDULE AT DATE_CHANGING
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
CREATE EVENT myevent
CREATE EVENT myevent
ON SCHEDULE AT DATE_BACKUP
DO
UPDATE myschema.mytable SET mycol = mycol - 1;
source to share
I can imagine two approaches:
Store all versions in the master table
| user_name (PK) | effective_date_from (PK) | ...remaining columns
In this approach, each user is represented by multiple rows in the table User
, but only one row is current. To find the current data, you need to add additional queries:
SELECT *
FROM User
WHERE user_name = ?
AND effective_date >= NOW()
ORDER BY effective_date DESC
LIMIT 1
So we retrieve all users with a given user_name
(there are several versions of such a user with different ones effective_date
, so effective_date
it should also be part of the primary key). We restrict the result to the most recent version (but effective_date
not in the future).
This approach has several disadvantages:
- what to do with foreign keys? Logically, there is only one user in the system.
- poor performance, complicating queries
- what to do with outdated versions?
Pending versions table
Save the original (main) table schema User
without any changes and get a second table named Pending_user_changes
. The latter will also have a column with the same + schema effective_date
. If the dimension of the changes is one day, write a job (in the database or in the application) that looks for any pending changes that are due to take effect from today and replace the main table.
I find this solution much cleaner: the primary / foreign keys in the master table never change, and the table is main
not cluttered with old and duplicate data.
source to share