This is the second installment of my three-part series on database best practices. A best practice is a technique or method that has been proven more effective than others. Best practices are useful in almost every industry. However, it is important to remember that they exist by consensus and are often hotly debated. In this series I share several of the commonly accepted best practices regarding database design and maintenance.
Database Best Practices Series – First Installment
Note: These practices are intended to be used with a SQL database. They may not apply to other paradigms.
- Always use meaningful column names.
It can be very tempting to reduce column names to meaningless strings of characters that only vaguely resemble the words for which they stand in. Reduction is tempting for many reasons. A programmer may dislike typing. He or she may not have a good grasp of English. Simple, meaningful names can sometimes collide with third parties. These are all valid concerns, but they aren’t sufficient reason not to use meaningful names. A concise, meaningful column name is clearer and easier to read. It doesn’t drain mental resources by forcing someone to remember the exact spelling of a symbol because he or she can rely on it being spelled correctly and consistently. A meaningful name will also be free from the caprices of fashion and regional idioms; it will be understandable years after it is written.
- Design your database carefully, with normalization a primary goal.
Normalizing means organizing the fields and tables of relational databases (which are the focus of this series) to minimize redundancy and dependency. Divide large tables into smaller ones and define relationships between them. The goal is to be able to make a change once, and let it propagate through the database using the relationships you’ve defined. It is important to have this in mind in the initial design stage. Once your database is in production it can be costly and dangerous to change. Be particularly wary of using a spreadsheet as a basis for a table; it is most likely not normalized. Note: As a rule of thumb, if one of your tables contains more than about twenty fields, it may not be properly normalized. Sometimes it is necessary to have a large table, but it should at least raise a red flag.
Implementation of these techniques can greatly improve your system’s performance and reliability. Be sure to come back for the third and final installment in this series on SQL database creation, maintenance, and recovery.
Alan Chen is President and Chairman of DataNumen, Inc., the leader in data recovery technologies, trusted by businesses of all sizes worldwide. For more information regarding any of our repair/recovery products, visit DataNumen.