Database Design Mistakes: How to Avoid Common Bad Practices
by Lizz White // 07.02.2018
Do not Wait for the Crash. Prevent Common Database Design Mistakes Early
It is quite prevalent that database designers can make the same simple mistake over and over again. Subsequently, your initial design will have massive impact further down the road. As an old pro you know all this, you even know all the shortcuts that will save you time. Does it really? Will you only realize this when your database crashes? Here are the most common bad database design practices. Avoiding them will save you – believe me – time and money.
Poor System Design can be Detrimental
Think about what a particular database instance is going to support. Not only will a lacking system design cost you in the long run, this bad practice affects the scalability of your application directly. Try imagining the requirements of the database in 3 years’ time. Will the new design cater for expansion? Think of how the database with be backed up and restored. Think about how the database design will assist in the incident recovery plan. See your design as a big picture, and not simply the sum of its parts.
Carefully Consider Primary Key Use – And its Effectiveness
It can happen that the Primary Key is misused, or not used at all. This is arguably the worst database development mistake to make, and not just a bad practice. According to ITPro “Good database design starts with the right primary key”. Here are a few valuable reminders about primary keys to take away:
- Do not base the Primary Key Value off of the data in the row.
- The Primary Key Value shouldn’t have meaning therefore application data shouldn’t be used.
- Primary Key Values should never be changed.
- Primary keys should be sequentially or randomly generated by the database upon row insertion.
- Moreover Primary keys should be managed by the system as much as possible. Scalability is key.
Stick to the Naming Standards and Documentation Best Practices
Although everyone seems to know that the bad practice of poor naming standards can cause an array of issues, most developers don’t adhere to a standard consistently. When it comes to good database design practices – and the management thereof, make sure that your naming standards are logical. No one has the time to cross reference to through pages upon pages of documentation whenever they encounter a different object name. In addition to that, avoid including metadata in the object name. Equally vital is your documentation. Don’t be an idiot. Poor documentation is not a form of job security. It just makes you bad at your job. Make sure your documentation covers all naming standards and definitions, no matter how obvious it seems to you.
Improper Normalisation Can be Fatal
This has been around for many years in database design and is all about how you organise your data into tables. Normalising your data is crucial. This will provide you with good performance as well as ease of development. Most designers either overdo it, or don’t do it enough. Make sure you find the sweet spot somewhere in the middle.
Do not Overuse Stored Procedures in your Database Design Practices
Its common knowledge that stored procedures can be your best friend when it comes to using SQL servers effectively. On the other hand relying too heavily on them can lead to serious issues. The use of modern ORMs, (Object Relational Mappers), will not only make your process more efficient, but is also is more nimble in the development process. Remember this next time when you are considering all of your system design options.
And most importantly… Test your database during development.
Don’t be lazy and don’t try taking shortcuts. Once the system goes into production the database is always blamed if a system start running slowly. When it comes to app development services, we at Zapro Digital take extra care with our database designs – ensuring our programs are not only robust, but scalable in it’s simplicity. We believe that one should combat that perception and TEST everything. Interested in more development topics? Web Development Process: Web Design Vs User Experience
We leave you with this final thought… ‘Programming is like sex. One mistake and you have to support it for the rest of your life’. (Michael Sinz).