A short disclaimer: These rules are coming from my own experience. The list is not complete, and, actually, it's not a list - rather a reminder for myself and an advice for all others. Most of the rules might seem obvious, but I have spent several nightmarish days recently fixing the consequences of not following those rules.
1. Every item of business data which enters the database should have the information about the source of the data and the time when the data was inserted. The source should be as atomic as possible (not "one of mail servers", but "server mail05.mycompany.com"; not "a user input", but "Entered by user Joe Schmoe"). Of course, this does not have to be a text field - a source ID will do.
2. All changes to business-impacting data items should be recorded in corresponding history tables, together with the information about the time and the source of the changes (ID of the user who performed editing, name of the process that changed the data etc.). A note - this rule doesn't state that changes of all data items should be archived - just the ones that might be important for the business.
3. All changes to the database that are not done according to standard operation procedures should be documented. If there is some problem with the data, and there is no other solution but to fix it manually, the script used to perform the fix (even if it is a single SQL statement!), together with the description of the change and the time the change was performed, should be stored in some archive.
4. For each and every table in the database there should be a documentation describing how the data in this table can be changed, and what are the procedures for fixing the incorrect data. And, I will add, the designer of the database should take into consideration the fact that sometimes some data in each table might need to be fixed. For some tables, the documentation would be very simple: "Use UI to modify data". Some tables might require more complex procedures -for example, an error in financial transaction requires creation of a reverse transaction (and transaction types should include reverse transaction in this case!). Another example would be a table which somehow aggregates data from other tables - in this case incorrect data in this table might require changing data in some other table and, for example, running some update script.
Following these rules is not always easy - but in the long run it will save you from some extremely unpleasant experiences. Trust me - I've been through that!
Technorati tags: database design, software design