As with most fields, the business of database creation and maintenance has developed a number of best practices. A best practice is a method or technique that consistently obtains results superior to its alternatives. Best practices can be applied to almost every industry and professional discipline. Their ubiquity, however, does not mean that each one is universally applicable or even universally accepted. The fields that best practices are meant to standardize are incredibly complex. Each situation involves untold variables, and seemingly small differences in context can result in quite a different outcome. Even assuming identical situations, there are many different performance metrics by which to judge a company’s success. Still more complications arise from the fact that similar businesses in the same field may have different goals. Best practices are often contested. Below is a short list of some best practices that are generally accepted. Keep in mind that it is very important to balance the unique qualities of your business with the practices shared by others.
Note: These practices are intended to be used with a SQL database. They may not apply to other paradigms.
- Always have a primary key.
Always. If there is no logical key that can be made primary, generate an artificial one. You want your table to be joinable to something. You’ll need to be able to uniquely identify a record. Even if you think you truly don’t need one in your table, there’s no telling whether it will need one in the future. Assigning one in the beginning saves extra work in the future.
- Retain vowels when shortening words in field names.
If you are from an earlier generation of programmers, you may be used to omitting vowels when shortening words (e.g. strcmp, setjmp, memcpy). This practice is discouraged by the younger generations of programmers. The resources you may save by omitting a letter or two don’t necessarily make up for the added difficulty of typing a familiar word in an unfamiliar way. There is a time cost associated with this difficulty, and it adds up. There is a school of thought that proscribes shortening words at all, but this is highly contested and probably a matter of personal preference.
- Back up your data.
Even the most well-built and well-maintained database can crash unexpectedly. From natural disasters to human blunders, there is a host of things that can stall the operation of your business. Backing up your data is absolutely essential, and in many cases can save you from having to use a SQL recovery tool.
These are just a few of the generally-agreed-upon best practices in the field of database creation and maintenance. Stay tuned for the next installment in this series.
Alan Chen is President and Chairman of DataNumen, Inc., leading in data recovery technologies worldwide. For more information on any of our products, including our SQL recovery programs, visit DataNumen.