SQL Schema Explorer by Tim Abell

5 ways to make your database better

by Tim Abell, 10th July 2018

[1] Documentation

Shoot me okay, but maintenance of software is [insert large number here] times the cost of creation, especially with relational databases. You are a pro working for a client, you owe it to them to make it possible for them to have future staff (and yourself!) be as effective as possible. You put all that effort into figuring out why a column should exist and have that name, now share that knowledge before you move on to the next greenfield project

[2] Refactor your database

Migrations are a thing now. Use them. You refactor your code, why wouldn't you refactor your database? Stop leaving landmines for future people - misleading names, bad structures etc. Use the redgate tools (ready-roll etc), use your orm’s tools (EF migrations, active record migrations). Yes you have to deal with data, but it’s the exception not the rule that it’s going to take hours to run because of data volumes.

[3] Enforce data integrity

Does your app fall over if the data is bad? Databases have many powerful ways of enforcing the rules your code relies on: nullability, foreign keys, check constraints, unique constraints. Stop the bad data before it even gets in there. Now your database is enforcing these rules your code doesn't have to handle violations of them when reading data because they'll never happen

[4] Integration testing

You have an ORM. Great. You have unit tests. Great. But where the rubber hits the road and your code sends SQL to a real database it breaks at runtime more often than you’d like to admit because the generated sql didn't jive with the real database structure or data in some obscure fashion. Automate the creation/test/destruction of your db and run full end to end integration tests. I suggest automating from the layer below the UI to keep the tests fast. There are many techniques for keeping the tests quick but still realistic: do end to end smoke tests instead of individual pieces, use an in-memory database, use database snapshots or the fancy sql-clone tool from Redgate to make creation / rollback virtually instant. Can you pull realistic (anonymised) data from production? Better still, now you’ll catch a whole new class of bugs before they hit prod.

[5] Make it visible

Are the only people that can see the database structures the coders and DBAs? do the business owners, support people, Quality Assurance (QA) people find it a mystery? You should be just as proud of your database as you are of your code, by shining a light on this dark corner of your digital estate you can make it as good as it should be, not an embarrassing backwater. By sharing the database in an accessible form to the non-coders in your team you can help them be more effective in their jobs.

Combine these tools with a continuous integration system and you have easy access to the bleeding edge of your databases development.

Take action now!

  1. Make a start on at least one of these improvements today.
  2. Share this article with your team - get everyone motivated to improve.
  3. Share this article on social media - help spread the word that our databases deserve better!

I hope this has inspired you to make an improvement in the often unloved underbelly of your applications.

What do you think needs improving in the way we deal with databases? What change did you make because of this? Let me know!