If we are to follow the advice of Joshua Bloch in Effective Java, we should minimize the mutability of our objects. How does this apply to data access layers, and databases in general?

A good rule of thumb for databases is that if it is important enough to record in a database, it is important enough not to delete from your database…at least, not in the normal course of events. If Databases tables are primarily read only, then then the action of reading the current item will be “select * from table where key =  max (key)”.  Deletes indicate an error made. And so on.  Business objects are then required to provide the rule to select which is the current record for a given entity.

A good example is the Physical fitness test given in the Army (the APFT).  A soldier takes this test at least once per year, probably more.  In order to be considered “in good standing” they have to score more than the minimum in push ups and sit-ups, and run two miles in less than the maximum time, all scored according to age.  The interesting thing is that the active record for a soldier may not be the latest record, but merely the highest score inside of a time range.  Failing an APFT only puts a solider in bad standing if  they do not have another test scored in the same time period that is above the minimum standards.  A soldier might take the APFT for some reason beyond just minimum qualifications, such as for entry into a school or for a competition.

As an aside, notice that the tests are scored based on age.  Age should not be recorded, rather calculated from the date of the test and the soldiers birth date.   Never record what you can calculate, especially if the result of the calculation will change over time.  Although in this case, it would be OK to record the Age of the soldier at the time of the test as a performance optimization, providing  said calculation was done by the computer and not the person entering the scores.  Note, however, that doing so will prevent adjustments like  recalculating the scores if we find out a soldier lied about his birthday.

Relations are tricky in this regard.  for instance, should removing an item from a shopping cart in an eCommerce application be recorded directly or IAW the “No-delete” rule?  If possible, go with the no-delete, as it allows you to track the addto, remove from cart actions of the shopper, something that the marketing side probably wants to know.  For a performance optimization, you can delete the relation, but make sure you send the events to some other backing store as well.