Day 22 - Audit your DB schema

#1

It’s time to turn an eye toward your database schema.

Please spend 20 minutes reading through yours carefully.

Some things you might look for:

  • Inconsistent column names.
  • Missing indices for columns you frequently query by.
  • Missing unique indices to ensure uniqueness.
  • Missing null constraints.
  • Missing foreign key constraints.
  • Maybe even install bullet to detect N+1 queries in activerecord and mongoid.

Can you think of any other best practices that are missing from this list? Did you find an issue I missed? Please share it on the forum.

Enjoy!

1 Like
#2

This one unfortunately wasn’t super productive to me. I’m currently on a largely greenfield project, and we only (for the moment) have a single table in the database that’s extremely simple. I did go through the “some things to look for” to make sure they were in place, but given the state of the project there wasn’t enough here to fill the 20 minutes.

Still think this is a worthwhile exercise though. On one of the other projects we have here you could easily spend days doing this as the schema is so complex.

#3

Now you’re talking my language! Great suggestions. I just went through the exercise of retrofitting FKs on a set of data we imported from elsewhere. It was a challenge in itself!

If you don’t mind me mentioning a thing I’m working on, here’s a tool to help inspect databases that’s in beta: http://schemaexplorer.io/ and here’s my first article trying to encourage people to be better with their databases: http://schemaexplorer.io/articles/make-your-database-better - is that useful? I literally just added visibility of null/not-null in the column info, and am working on viewing of indexes right now. I’d love to chat if anyone’s interested in this area - tim@timwise.co.uk

Other ideas for things to improve in your database:

  • check constraints are often under-used, especially multi-column
  • if you’re using something like entity framework, are your enums just integers with no fk in the database? I made this to help with that annoyance: https://www.nuget.org/packages/ef-enum-to-lookup
  • are the constraints / fks disabled? I came across this on a client project when I found data that violated an existing fk, very confusing! Happens when dba does bulk data loading and wants to ignore constraints and then either forgets to re-enable them or can’t because the data is bad.