I'm designing a system that acts as a master data service for what I shall here call boxes. The system is to be implemented in Java with a relational database (SQL) as the main storage. Each box has a ~dozen different top-level properties: ranging from simple primitives (booleans, integers, etc) to other objects and lists of other objects.
The main issue is that most of the box properties may change over time, and one needs to be able to schedule those changes in advance. Furthermore, one needs to be able to schedule any number of upcoming changes to any of the attributes, in any chronological order.
For example, in October we might schedule new set of derps for a box for December, to be returned back to normal on January 1. If, in mid-December, we find out the box gets a new foobar_id in February, we need to be able to schedule that change for February without affecting the upcoming derp change on January 1 -- and without accidentally reverting the derps of December back when the time comes to apply the foobar_id update.
My idea is to create some sort of a queue of upcoming change events. Each queue item would only change the values of the properties given in that exact event. New events could be added into any position of the queue and existing events could be removed from it. When an event would occur, it would record the old value of the property it changed.
Now, the keywords of the previous paragraph are some sort of a queue. I'm unsure how to actually implement this in Java + a relational database! It seems that a language with strong static typing doesn't lend itself well to this kind of an exercise in generic attribute changes.
I'm considering a relatively simple database table with a timestamp (date of the change), the name of the property that's going to change (an enumeration), and a serialized (JSON) representation of the new data. Then each property would basically need their own handler/deserializer. Another way would be to copy the box database structure for upcoming changes and just store a bunch of "boxes" with no other properties than the ones that are going to change. This seems like it might be easier Java-wise but the database would become quite complex, when almost all tables would need to be duplicated.
I need the system to be robust so that it's not too easy to break it when new properties inevitably are added, or some old properties are changed. As such, I'm not too fond of the idea of using reflection on this. New changes can only come in to the system as fast as human beings can type, so I don't need the solution to be optimized for speed. But I do plan to keep one complete and up to date version of each box object in the database, so that I don't need to reconstruct the object from a number of changes every time I need it. Also, for what it's worth, the database queries that the system is going to be handling are going to be pretty simple and the number of boxes is unlikely to exceed ten thousand. I'm not concerned about the actual scheduling part, i.e. triggering the changes at the correct time.
So I guess that basically my questions are these, starting from the most important one:
- Does the queue pattern that I just described have some name by which I could find some more material on it? If it does not have a name, can you point me to something similar?
- Can you point me to some good resources specific to Java, SQL and this kind of a pattern?
- Any other thoughts? Anecdotes? Am I missing something obvious and I should do it some other way? (Java and SQL are going to stay anyway.)