Database Best Practices

Preamble

Like all things, we tend to speak in black and white while we act in grays. This implies that we should be wary the individual that speaks in grays because they probably act in black and whites. Documentation should always be black and white; we want to provide a body of work that elicits understanding.

Best practices define the tried and true methodologies. The vast majority of the time, we can and should abide by the conventional wisdom. All too frequently, people justify a poor approach by citing complexities. It has been my experience that if you cannot make something sound simple, it is not well understood. Complexity is just a sequence of simple steps. Just like designing a database, break down the concepts and strive for the beauty of simplicity. If you arrive at something that cannot be fitted into the mold, accept that you may have to step outside of best practice; just make certain you document why.

Key Best Practice Points

• Backup and Maintenance Plans – The first tenet of being a DBA; always have a backup plan. The backup plan should accommodate the business needs by guaranteeing minimal downtime and optimal data recovery.
• Minimum Permissions –All database users should operate with only the permissions required to complete assigned tasks.
• Change control – Change control ensures minimal downtime and efficient and effective updates. All proposed changes to production should be documented and provided in a manner that can be tested and rollback if necessary.
• Separate Environments –Changes should be migrated across physically distinct environments; development, test and production. Developers should not have access to production environments. Test should be treated as production; changes should be tested against a production backup.
• Documentation – Complete documentation serves to illustrate that thought went into the design, that maintenance is not an afterthought and provides a forum to justify design decisions and points of contention.
• Comments – While code is largely self-documenting for those that know the language, clarifying the methodology with logic/process comments is tremendously useful.
• Naming convention – Determine and adhering to a naming convention promotes consistent coding and documentation.
• Always define primary keys – Candidate keys should be documented and a primary key should be physically enforced. If using surrogate keys, logical keys should still be physically enforced.
• Constraints – Referential integrity constraints prevents orphaned data. Check constraints are the physical enforcement of business rules.
• Normalization – Third normal form is the default level to design to.Avoid NULL – Understand what null values are. Minimize null values by properly normalizing.
• Stored Procedures – Promotes maintainability via a separate and complete body of database code. Security is easier to implement by function. Improves performance.
• Avoid wildcard selects – Explicit column lists reduce unnecessary I/O and processing. Additionally, they promote better change control.
• Avoid Distinct – Adds I/O and processing. If data is not being uniquely selected, re-evaluate the query. Distinct is often a shortcut for lazy querying, poor design or both.
• Derived statements – Derived statements can often drastically improve performance.
• Explicit joins –Qualify intent by using explicit joins.
• Minimize Cursor Utilization – Cursors are expensive operavtions; by thinking in sets, cursors are often rendered unnecessary.
• Minimize triggers—Triggers are often misused to accommodate poor process and/or poor design. Triggers should not perform any complex actions, nor perform non-deterministic actions that may affect other data.
• Avoid dynamic SQL – Dynamic SQL complicates maintenance, requires escalated privileges (explicit table grants) and typically negates performance benefits of stored execution plans.
• Error/exception handling – After modifying operations, check the success. As well, general exceptions that are likely to arise should be handled.
• Avoid wildcards at the start of strings – Wildcards reduce index efficiency; when used at the beginning of strings, indexes are largely disregarded.
• Do not store derived columns – If you can calculate the values from existing fields, it should not be stored; use views to present derived values.
• Minimize text/blobs – Fundamentally, compound objects do not belong within the relational model.

Tags: best practice, dba