Great organization is the key to success in many aspects of our lives. Usability is a key feature in the applications we build, yet these factors don’t always carry over into database organization, especially in growing systems. Just because your database isn’t the face of the application, doesn’t mean it shouldn’t have the same sense of flow and organization that the application does. Maintain and organize your database as you would other areas of your life (i.e. your home). Organization should be purposeful, have a basic sense of flow, and be ready to show off when necessary.
Table and Column Naming Conventions
I often see table and column naming conventions that appear cryptic, coded or simply incorrect. Table and column names should reflect the data that they store. Find a standard in your application and stick to it; it will make maintaining and troubleshooting the application in the future a whole lot easier. You wouldn’t organize your office files and label them “x” or “col1,” so why do it with your database?
Related and Duplicate Data
Keeping redundant data can cause multiple issues with your application, including rapid growth in your database and confusion about which data is accurate.
In the same sense, storing related data in unrelated tables can cause the same issues. It falls in line with the same point regarding table and column naming conventions. Data should be grouped together as it makes sense for the application. Doing this will most likely improve the performance of your application; databases with normalized, properly indexed data translate to higher performing applications
Vehicle engines and home utilities require routine maintenance–your application database is no different. Applications grow and evolve over time and you need to ensure that your databases grow and evolve with them. Maintaining database indexes is the key to keeping your application performing at a high level. Scheduling weekly, monthly, or quarterly reviews of your database indexes will help maintain these efforts.
Review the growth of your database tables; it’s easy to assume transactional table data needs to be stored indefinitely. Know how long your application needs to review and access historical data. Data can be easily archived out to a reporting database to minimize the size of your transactional tables
The simple, every day organization and guidelines that make you successful should reflect in your application database. Remember, bad habits and sloppiness in setting up your application database are the quickest way to cause problems for your application.