Day 22 – Audit your schema

Exercises
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!

2

That’s a great tip. So many problems can be pre-empted by looking at the storage structures.

In fact I’m so into it I’m working on a tool to help and am looking for beta testers, I hope you don’t mind me mentioning it! - http://schemaexplorer.io/

1 Like
3

Ended up installing: https://github.com/jmcarp/nplusone (Python based library for finding n+1 queries) to try & find some n+1 queries, but came up empty.

Indices are tough as all the projects are microservices and don’t have a ton of data (so not much benefit to adding indices).

1 Like
4

My setup is Symfony with Doctrine ORM on a (innoDB) MySQL database.

  • I have to update my scheme with some DELETE ON CASCADEs beside that my setup is pretty much complete.

  • Thanks to the Symfony Profiler, and the EXPLAIN query function you can easily see which indices you should create that haven’t been added yet. Also always review if you really need to load an entity into your ORM or if a native SQL query might be sufficient(much less memory footprint).

  • I use the SQL standard naming scheme - all lowercase and combined names split by _ for columns. My table names are usually named in singular but at all depend on my entity names. All table names are prefixed with their Symfony bundle name.

  • To quickly browse and admin my database I highly recommend to use https://sequelpro.com. It’s a fantastic and free SQL browser for Mac.

1 Like
5

I don’t know if it’s a best practice, but I can no longer work without model annotations at the top of my files. Seeing them constantly ensures I know what’s up and definitely helps me ramp up more quickly on new apps.

Otherwise, in terms of auditing: i would add making sure you’re tracking dates — created_at and ipdated_at. Rails does this automatically, but I think there was a time wheee it didn’t? Anyway, if you’re not using Rails they’re insanely valuable columns.
Especially created_at. You might not need it now but if you ever need to track growth or audit something, it’s helpful to have.

3 Likes
6

No database on my current project (embedded C). So I used the time to review and start cleaning our non-volatile parameters names and usage. Either way, time well spent!

2 Likes
7

No database on my current project, so I read over our .proto files and browsed their usages. I did find one issue where a field was used in an ugly conditional, so that’s a win.

1 Like