Database design is probably the most important aspect in developing a quality software solution. In my experience in working with multiple teams (both in-house and 3rd party vendors), I often come across alarming issues in the database architecture that could pose huge & irreversible loss to customers as well as the entire business itself. And I am not even talking about database optimisation! I am referring to the most fundamental rules that HAVE to be followed from day 1. Not following basic practices leaves tremendous technical debt that is bound to raise ugly and frequent bugs in the long term.
Here are some of the most common things that developers seem to forget.
1. Know your database engine! (if using MySQL)
MySQL has many storage engines available, of which 2 flavors are the most common: MyISAM and InnoDB. Both of these engines are vastly different in their use cases. For example, foreign key constraints are not supported in MyISAM while they are fully supported in InnoDB. There are also differences in speed, transaction & rollback support etc. It is super critical that you identify beforehand which engine you would want to use. According to me InnoDB would be your engine of choice 99% of the time. Use MyISAM only to highly specific use cases. You can find the full comparison on SO here.
2. Make your DB & application logic ACID compliant
Your database design & application architecture has to adhere to the principles of Atomicity, Consistency, Isolation and Durability. If you are new to this, read about it here and here and here. I recommend that you read it over and over again until it drills down in your head and you can design your database and query logic in your dreams with it. This is the single most fundamental concept in SQL that any and every DB admin will beg you to pay attention to. Without it, every application is bound to fail. Sometimes maybe in days or months or years. But eventually it surely will, so don’t live on a prayer and get this done.
3. NULL is not equal to “”
Okay, so this might sound plain stupid, but I have seen this happening multiple times so don’t take this lightly. In a hurry to ship code and features, sometimes developers do not differentiate between NULL and empty values (“”). They are okay to define some column as NULLABLE which means the value is not mandatory but when get a blank value from the application layer they store it as “” instead of NULL. This is a CRIME!
Apart from the fact that you are wasting memory, you will pay for it sometime in the future when you get inconsistent results while trying to match records. Read more on this SO thread.
4. Enfore maximum constraints in your database
Take full advantage of the various constraints that your database has to offer: primary keys, foriegn keys, unique keys, not null columns, indexes etc. Do not assume to take care of any of these constraints at your application/code layer. It is 100% essential that your database throws an error if your application layer somehow passes a duplicate record for insert/update. Trust me, this will ensure that your integrity errors are caught much much earlier in development & testing than in production.
5. Do not make your DB publicly accessible
It is super tempting to be able to query your production database through visual interfaces such as PHPMyAdmin, Management Studio or other GUI tools from anywhere on the internet. This helps us quickly sort out critical and urgent bugs that need fixing in seconds.
However, I would strongly recommend that your production database server should NOT be accessible on the internet. It should only and only be accessible from your application/web server itself. In case you really need to troubleshoot and fix urgent issues ensure that you dynamically open up temporary access only to your current IP address, connect to the DB, fix the issue and then destroy the remote access immediately. This will surely help you sleep better, trust me.