In our organisation, we are facing a huge challenge with the need to program new J2EE applications that integrate well with legacy Cobol systems.
Most of these cobol apps data models are having an "effective start date" and an "effective end date" to keep track of all changes that are made to a certain table (likely to be in all tables...). Note that no physical delete or updates are done... Deletions are made with updating the effective end date to the today date, and updates are done by inserting a new row with containing new start and end date (and updating the end date of last entry to today date).
For example, take the insurance policy table columns :
effective start date
effective end date
and others data colomns.
Data desgingers had come with a data model with including policy, customer number and the effective dates into the primary key (which I'm not sure is it nice).
But this brings the questions : how to make a good, reliable and performant design with EJB entity beans? For the time being, we had great discussions about choosing between CMP vs BMP. Furthermore, we had thought leaving entity beans for Session Bean facade (stateless) with JDBC-only approach.
We had also read chapter 12 and 13 of Ed Ronan's Mastering EJB, but I think we would need yours lights about that subject. It is also written on that book about Matin Fowler versionable object pattern that I can't find.
What would be the most elegant design for this design problem ? Changing primary keys? Keeping "naturals" key and then adding "surrogate keys" ? Any advise would be grealty appreciated!