Your Database Is Your Friend

Introduction
Welcome to the Database Is Your Friend screencast. My name is Xavier, and this is the online version of an in-person course I ran in 2010 about using your database effectively. I've updated small portions since then, but mostly in the ordering, presentation, and auxiliaries here. The core concepts have stayed the same as they have always been since relational databases came into use a few decades ago. Although it is pitched at Ruby on Rails developers, and that is what I use for my examples, the concepts are technology agnostic and applicable to any application that uses a data store. Similarly, while I use PostgreSQL for my demonstrations, the topics covered are applicable to all SQL databases such as MySQL and SQL Server. There is a short appendix on MySQL specific issues at the end. So, if you encounter problems following along, check there first. I'm going to cover three major topics: Data integrity, concurrency, and reporting. The first two will likely be the most immediately useful if you are running a production application already since they directly address issues I have seen in virtually every Rails application I've worked on or heard about. I generally introduce each topic with an exception page or error condition, all of which I've seen in production, and then work backwards from there to an elegant solution. The third reporting module isn't as much of a quick fix, but gives you some concepts to help understand why your current schema may be suboptimal for the things you are tying to do with it and provides some alternative modeling techniques to fix.

Data Modelling
Welcome to this module on Data Modelling. I'm going to be presenting a number of techniques for using your database schema to effectively solve common problems I have seen in Ruby on Rails applications. Each technique is presented as a problem that you're likely to experience or possibly already have experienced in your own deployed app. I will then quickly cover the common fixes and why they are less than ideal followed by a superior solution that uses the power of our database to solve the problem quickly, robustly, and elegantly. By the end of this module, we'll have learned about not null constraints, foreign keys, handling duplicate data with uniqueness validations, polymorphic association alternative, reactive data integrity checks, and how to use all of the above in your own Ruby on Rails applications. Let's get started.

Concurrency
Welcome to this module on Concurrency. Concurrent code is any code that is executing in parallel or at the same time. In Rails applications, this typically maps to multiple web requests being handled at the same time. Under these circumstances, code that works for a single isolated process such as you likely have in development, tends to break in weird and wonderful ways. I'm going to be showing you a few examples of this that I have seen in production Rails applications, as well as a robust solution that make use of your database's features. I will be covering three main techniques: Database constraints, locking, and isolation levels.

Appendix: MySQL
While I used PostgreSQL throughout the screencast, I know many people out there are using MySQL. While the former excels in its teaching environment, the latter is present in many production deployments including all the large ones I have personally worked on. This appendix covers some MySQL specific issues that are particularly useful to understanding problems you might see in production. I remember how confused I was when I first encountered some of these problems, and I hope to help others avoid it. It contains some explanations and debugging techniques for concurrent scenarios, which are roughly transferrable to other databases, so this may still be of interest even if you don't use MySQL day-to-day.