Database design is an art. Just like swimming, it is easy at the start but mastering it requires more than just dedication and simple lessons. For someone who is out to learn database design, sound theoretical background is necessary. Most importantly, you must be willing to practice on a regular basis. Along the way, you will find yourself making a couple of mistakes; you are not alone. Even experienced database designers may end up making simple but serious mistakes. This is in turn affects the use of such databases by managers and DBAs. So what are these mistakes?
Poor Naming Standards
Most database designers understand the serious consequences that arise from poor naming standards. However, some of them still ignore the proper naming standards. The naming standard, as it is, might be a matter of personal choice. However, it is important to ensure consistency, logic, and documentation while doing this. For instance, someone working on a customer address field should ensure there is consistency in the words used. You cannot use customer address at one point and cutsomeaddresssomewhere else in the same field.
Normalisation is all about organisation of data into tables. It is fair enough to agree that many database design experts pay a lot of attention to normalisation, with some even overdoing it. Others underperform when it comes to normalisation. Proper normalisation makes subsequent development easy. In addition, it promotes enhanced performance.
It isn’t all about proper naming standards. The name should be supported by detailed definitions of constraints and columns, including all the established relationships. Ideal documentation makes it easier for anyone following the design to understand it. Simply having the definitions is not enough though. It is recommendable for a database designer to clearly spell out the working structure of the databases. This will ease the work of database developers.
Closely related to improper normalisation is this mistake. It is so common among database designers working in a hurry trying to beat deadlines. It is important to ascertain that one is using the right amount of indexing. In case of doubts, you can take advantage of query analysis. It will help you determine the appropriate amount of indexing necessary. Just in case you forget, it is important to note that foreign keys and WHERE fields require indexing.
Ignoring Time Zones
Some businesses run across different time zones. As such, they have customers across the globe. To avoid any confusion that might arise from the use of the database by a dba consultant or customers, a multi-time zone database system allows for display of only one standard time zone, say UTC.
Database designers have in the past associated this job with success and happiness. For those who are just about to get their feet wet, it is important to have certain things in mind. Most importantly, you need to be on the lookout for common errors that can easily spoil a smooth database design. Avoiding them will enhance your progress.