Database Organization

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

Routine Maintenance

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.

Advertisements

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s