Tutorial

But First: A Very Brief Introduction to Databases

All of the content you see on Stack Exchange—all the questions, answers, edits, reviews, users, badges, tags, and more—is stored in a relational database. A relational database is a flexible and efficient way to store information—lots and lots of information. Stack Overflow has over 10 million questions—lots of information.

You may be used to using spreadsheets to store information. Why not? They're easy to create, and they're easy to sort, search, and use in other ways—no programming required. Spreadsheets don't scale, though. Imagine that you wanted to store questions and answers in a spreadsheet. It might look something like this:

Title Body Asker Tags Answers
Why do unicorns eat daisies? blah blah blah... (3000 words here) Joe unicorns diet gardening
Do unicorns really eat waffles? insert a question here... Princess unicorns diet cooking Wait, how do we represent multiple answers???

We're only two questions in, and already we have some problems. That second question has three answers; how do we store that in a spreadsheet? We have that problem with tags too, you may have noticed—it'd be hard, using this spreadsheet, to find all the questions about unicorns and diet but not the ones about dragons and flight and cooking.

Further, we might want to know more about the users than just their names. Users have badges, reputation, and a bunch of other information. We could add more columns to the spreadsheet (you can put a lot of columns in a spreadsheet), but you'd be repeating that information for every question a user asked.

A spreadsheet represents one type of thing—questions, in this case. If you want to represent more than one kind of thing you use more than one spreadsheet, but they're all independent. You could have a spreadsheet for questions and one for users, but you wouldn't be able to look up questions asked by users with at least 1000 reputation.

A database may seem similar to a spreadsheet at first glance. It has tables, which are like individual spreadsheets. A table has columns (like in a spreadsheet), and there's one row per item. But in a relational database the tables can interact with each other. Instead of jamming everything about users into your questions table, for example, you can use keys to cross-reference them:

Questions
ID Title Body Asker
192 Why do unicorns eat daisies? blah blah blah… (3000 words here) 4921
433 Do unicorns really eat waffles? insert a question here… 2011
692 How do I tame a unicorn if I have only sons? question body goes here 4921
Users
ID Name Reputation
4921 Joe 1302
2011 Princess 101

(We'll come back to answers and tags in a bit.)

Here we've created two tables, Questions and Users. Every user has a unique ID, and the Questions table uses the IDs from the Users table to indicate who asked the question. When a user asks more than one question (like Joe, aka User 4921), we don't have to repeat information about the user for each question. There will be many more rows in the Questions table than in the Users table, most likely.

Tags, like users, are something we want to define somewhere else and just refer to from the question. Tags have names, descriptions, and some other information. A database column can store a set of items, so that's fine. And we can take the same approach with answers. So our Questions table could look like this:

ID Title Body Asker Tags Answers
192 Why do unicorns eat daisies? blah blah blah… (3000 words here) 4921 5 12 19
433 Do unicorns really eat waffles? insert a question here… 2011 5 12 31 91256 54312 44409
692 How do I tame a unicorn if I have only sons? question body goes here 4921 5 47 98751 41926

That's not exactly how the Stack Exchange data is really laid out. We'll get into those details in the next sections. But the important points are:

  • Databases are organized into tables, one table per logical concept (like users or tags).
  • As with spreadsheets, tables are organized into columns for the various attributes of the data.
  • Each item in a table (that is, each row) has an identifier that is unique within that table. This identifier is called a key.
  • An entry in a table can refer to entries in other tables by using those keys.
  • The definition of a table specifies which other tables those keys are for. Our Questions table has keys for users, tags, and answers, and each column "knows" what type of key it holds.