Building Scalable Databases: Perspectives on the War on Soft Deletes
This blog posting titled Building Scalable Databases: Perspectives on the War on Soft Deletes is interesting reading describing what to do with deleted data. The author comes to the correct conclusion, because he agrees with my viewpoint <smile>, that you model the business situation and mark records as inactive, canceled, etc. You seldom need or want to truly delete data.
I used to have an inactive flag on the equipment in the Granite Fleet Manager. That worked for quite a while until I had someone tell me that they have reserve equipment that is only used when the California wild fires are rampant. These fire engines are paid for by the state of California and stashed at various fire halls in strategic locations. They are all identical. Thus fire crews from outside the affected crews have already trained on that equipment and know how to use it. So now I have a status combo box which includes Reserve and Out of Service. The Out of Service is meant for the situation where a given unit will be unavailable for a period of time longer than a few days. For example maybe the boiler needs an inspection or the mechanic is waiting for parts to come from overseas.
P.S. I almost always add a general purpose comment memo field to strategic tables such as the equipment and the service order field. And you can search in those fields. Because no matter how many explicit fields you create the users can always think up a few more but don’t have any place to put the random data.