Database Design Principles

Database design should always provide client access with up-to-date, accurate information. A well defined database design will execute this by coupling complexity and redundancy. The end goal is to have a database that meets cliental needs, but at the same time is also maintainable. Proper database architecture is the forefront for a stable software application.

"If you don't know where you are going, any road will take you there" – George Harrison [1]
The following will explain how this previous statement should not be the case in database design.

Design Process [3]

Determine the purpose of your database
Find and organize the information required
Divide the information into tables
Turn information items into columns
Specify primary keys
Setup the table relationships
Refine your design
Apply the normalization rules

Normalization

Normalization is the process of efficiently organizing data in a database. There are two main goals for the normalization process – eliminating redundant data while ensuring logical dependencies. After the normalization process is complete, one can expect to see a reduction in the amount of database space required. Additionally, the identifying data should be logically consistent. Doing so should prevent or limit the number of anomalies from entering the system.

What not to do

1. Poor design and planning
2. Poor naming standards
3. Lack of documentation
4. Lack of testing

Building a database is like building a house. It can be a monumental task depending on the preparation. This is why design and planning is an essential step for creating a successful system – For the house example: You cannot hire a contractor and have him immediately start laying the foundation and placing walls (because the project was due a week ago). If he does, what will happen to the structural integrity? How many floors can it handle? Will the ground below the foundation support the structure or sink? A blueprint must be drafted before work can begin.

Likewise, if there is a lack of documentation or naming standards, for your columns and tables, then you will not be able to collaboratively work on the project. One person’s apple is another person’s orange. Abbreviations, as well as ambiguous headers, only add to this problem and should be avoided. Naming should be driven down to the most specific, human readable definition, such as Customer or Product.

Lastly, proper testing enables system confidence. Due to time constraints and project budgets, testing is usually the first process to go. Without the adequate testing procedures in place there is no way to verify that the house is livable, the light switches work, or the plumbing works. If our house has twenty light switches and one is flipped on, does that mean all twenty will work? No. It means that one works. Proper due diligence is flipping every one of those switches to verify that the wiring is intact and the connection is strong.

Conclusion

Just like building a house, if you start building without a blueprint, your house or “your system” is doomed for failure [1]. The success of the database is left up to the time spent in properly seeing a design through.
Following the design process will enable you to get your feet wet. As you get projects that require larger amounts of tables and relations the dependencies will make themselves apparent. Ultimately it will be up to you, the designer, to make the hard choices between reducing complexity (denormalization) and reducing redundancy (normalization).

References

1. http://www.simple-talk.com/sql/database-administration/ten-common-database-design-mistakes/
2. http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
3. http://office.microsoft.com/en-us/access-help/database-design-basics-HA001224247.aspx
4. http://databases.about.com/od/specificproducts/a/normalization.htm