When creating database for enterprise applications, is it any useful to keep create date

Question

I have just started with professional programming and my manager tends to dictate that i keep create, modified and delete date in all database tables that we use. I want to know does it makes sense to keep these fields for enterprise application. The reason I am asking it that because all examples, tutorials i have ever read on net, books never had such fields in place and also it increases complexity because each time I have to work with entities, i have to make sure I am using entity.DeleteDate == null

Thanks for your suggestions

Answer 1

It depends on the business your solution addresses. However, this should not cause you problems. Simply add this functionality into a base class extended by all of your entities.

Answer 2

its generally better to keep everything you can, and then in the future you may decide to remove certain fields / records, which is possible. if you dont keep it, when its gone, its gone, and you're never getting it back. if you store it, and these days, theres no reason not to store it since space costs next to nothing, you will have the data 2 years later when you need to pull some audit report your manager is going to ask for

Answer 3

Sooner or later, some kind of auditing is required. Soft-deletes are sometimes matter of design convenience, but often legal requirements that nothing is ever deleted. The more a business grows, the grater the likelihood that they will be sued for one reason or another. Having records preserved in tables helps when it's time to gather legal documentation.

Certain percentage of people (~2%) will be happy to use your services for a while, then they will close (try to delete) their account and call Visa/Master card to claim fraud. You will need to document their activity and submit it to the credit people in order to fight that claim; being on "bad merchant" list with credit card processors is very expensive.

Answer 4

This can be a good thing. The first thing I recommend you do is learn about views. Views are simply stored queries. Views are your friend.

For example instead of having to look for "Where Deleted not null" in your base table, you could create a view called viewActiveWhatever and filter the view so it only produces data where Delete is not null. Remember, views are your friend.

Soft deletes (marked as deleted) vs Hard deletes (Poof it's really gone). This can really mess things up depending on what kinda of data structure you have in place.

• If you mark as deleted and rely on views that show only active data (Remember, views are your friend) it works nicely.
• Some data (especially inner joined data may appear to be missing when it's not)

Not to make this any more difficult but, it sometimes is nice to preserve how something looked prior to it being changed. So, simply placing a modified date on a data row only tells you when that row was changed... it doesn't tell you what was changed.

Now if the data you are refering to is called "Control Data" you might want to consider keeping a historical table. Then before each update to a record in a control table you insert the current data row to the historical table and then make your update. Now you have a permanent record of what was changed and can go back over time and see all the changes.

If you do all your updates in stored procedures, this kind of record keeping is not that difficult.