In this post, I’m going to briefly cover the signs that you’re doing multi-tenancy wrong. Some of these practices are entrenched in software: there are gems in Ruby on Rails, for instance, use the first anti-pattern to achieve multi-tenancy. Listen, you can drive a car with a flat tire and you can eat yogurt with a fork. People have made these solutions work, but there’s a better way.

Creating tables or schemas per customer

If you find yourself running DDL (Create Table…) for each new company or user that you add to your system, most likely you’re committing a pretty big anti-pattern. Now every time you update the table definition or need to update data across all tables, you’ll have to use a script to generate the SQL for you. Those updates will take longer and it’s much more prone to failure.

If you’re doing this for performance reasons, you have two options in most database systems to …

I want to give you tools that you can use for building databases that can handle complex relationships. In the previous article in this series, I looked at hierarchical data. The classic example of a hierarchy is an org chart. On most org charts I’ve seen, an employee has only one boss, which is a one-to-many relationship. The other kind of segmentation is many-to-many. A good instances of this is your social circle. Most people have many friends, and those friends have multiple friends themselves.

This is a common pattern. When developing a multi-tenanted application, users will often want to see and update information for multiple tenants. Imagine, if you will, a freelance Web developer. He’ll set up Google Analytics accounts for each client’s website, and he’ll add both the client and himself as users to the account. …

They say there are two hard things in software development: cache invalidation, naming things, and off-by-one errors. Even though it seems like a simple thing, naming tables, columns, and stored procedures is hard when designing a SQL database. There are three simple rules I like to abide by when designing schema: give things meaningful names, be consistent, and favor verbosity over obscurity. Let’s expand each one of those points, and then I’ll cover some MySQL specific addendums.

Meaningful Names

The first part of this idea is to give objects unique, specific names. A table that is named Entities is going to confuse everyone. You want to name your table with the specific category of data or noun it represents. You can do tragical things to your database like have one giant table that’s just EntityID, Key, Value, but I don’t know why you’d use a relational database in that case!

Think of a table in terms of …

Whenever I have a problem query in MySQL, I say to myself, I know, I’ll use Explain Plan. Now I have two problems. I run the explain plan, but then I have to look up all the cryptic terms and read through the documentation to know what in the world is going on. Well, over the weekend, I decided to solve my own problem. I created a simple page that allows you to run an explain plan, and then each of those obscure, abbreviated terms is annotated with a tooltip that explains exactly what’s going on. I’m calling it MySQL Explain Explained.

I created a demo using the sample employees database (which you can download here). Here’s a sample query:

select gender, count(emp_no) as manager_cnt, sum(cnt) as sum, avg(cnt) as avg
from
(
    select m.emp_no, m.gender, count(de.emp_no) as cnt
    from …

Designing database tables for many customers is a surprisingly foreign concept for some programmers. I’ve been in interviews where a programmer will sketch out a normal object model, and then proceed to suggest that for the database, each customer has their own set of tables, each prefixed with the customer name. What I’d like to do in this post is introduce the concept of multi-tenancy and then show methods you can use to do it right, instead of hacky ways like the one I just mentioned. Multi-tenancy is when several customers (tenants) share the same database and codebase but can only see their own data.

The basic idea

The core method of adding multi-tenancy to your database is adding a column to every table you’d like to segment that indicates the owner of the data. I’ll call this column a tenant id. We’ll start with a basic example: a todo app. Let’s say you write a program that you’re going to use on your own to …

I’ve covered some strange default behavior around nulls in MySQL. There’s another nuance to this issue: you can still insert rows without specifying values for not null columns. MySQL will helpfully give you a default value (this is not really helpful – true help would be a quick failure with a descriptive error message). Let’s walk through this example.

mysql> Create Table NullTable (a Int Not Null, b Int Not Null);
Query OK, 0 rows affected (0.01 sec)

Here’s a table with two not null columns.

mysql> Insert Into NullTable (a) Values (1);
Query OK, 1 row affected, 1 warning (0.01 sec)

We can totally insert, even though we didn’t specify a value for b and b is not null.

mysql> Show Warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message …

I’ve written a short course on MySQL backups. It’s really a MySQL backup starter kit, giving you the basics of what you need to make sure your system is protected. The real gem in this course is XtraBackup, which is a tool that allows you to backup your MySQL server without blocking other transactions. You see, MySQL doesn’t have that out of the box. You have to spend a bunch of money with Oracle to get that otherwise. Hot backups are pretty necessary if you care about uptime, so I’m excited about XtraBackup.

Here are the topics I cover:

Simple backups

Even though XtraBackup is a great tool, no MySQL user’s knowledge would be complete without knowing how to take a mysqldump. Simple backups are just useful for moving data from server to server, or perhaps migrating your data to, say, PostgreSQL.

Disaster Recovery Plans …

MySQL is the little engine that could. It powers sites like Facebook, YouTube, Twitter, and thousands of blogs, CMSes, and e-commerce sites. Its value to the world and to the development community could be measured in the hundreds of billions, and yet it’s free, and you can use it just by downloading it. Almost every programming language has drivers for it and it can run on so many operating systems and architectures, there’s really no limit on it.

Yet there’s a dark side. MySQL is full of gotchas and bugs, and it lacks features that sometimes call into question its status as a real database. The documentation is often open-ended and confusing, with gaps in key parts. If you want to run it, you have the option of using it on Linux, Mac, Solaris, or Windows and every hosting company or provider like Amazon AWS has their own managed service, each with its own quirks and limitations. The user community has also produced thousands of …

Within MySQL, there’s a piece called a storage engine that reads and writes to disk on your behalf when you execute a query. It controls the way that your data is stored on disk. With MySQL, you can change what storage engine you use, which is helpful since every engine has different advantages and downsides, and you can select which engine to use based on your workload. There are two main engines: MyISAM and InnoDB. MyISAM was the default engine before MySQL 5.5 and it’s been there since the beginning. It’s also not crash-proof, it doesn’t have foreign keys, and it’s not transactional. InnoDB, on the other hand, has all these features.

What happens if you use MyISAM

No Safety Belt

Grooveshark (a popular music site) in …

Character sets are like the force: they surround us and penetrate us, binding all our digital world together. A character set is how we convert the 1’s and 0’s that the computer understands into human-readable characters like ABC. In one of the first character sets, ASCII, the number 97 is translated to “a” and 63 is the question mark (?).

“Are there other languages besides English?”
“Don’t think so, Bob.”

English-Only Please

The trouble with ASCII is that it was created back in the ‘60s by a bunch of Americans and they were not thinking about French or German, they were thinking about English. Guess what? ASCII works great for American English, but there’s no way it can encode even Spanish, let alone something like Chinese, so it only met the needs of around 5% of the world …