Thinking of Databases as an Excel file

I was having a conversation recently around the value of databases as opposed to flat files and the question "What is the essence of a database?" came up. I explained it as it relates to Excel files, which I think is surprisingly apt.

Excel files are logically constructed around a few principles. First, there is the file itself. This lives on a hard drive somewhere, it has a name, and there is a program that can open it. When you open it up, things are organized on multiple sheets. These sheets contain columns and rows which contain data.

Databases are quite similar. A database is equivalent to an Excel file. It lives on a disk somewhere and there is a program that can read it. That program is probably called something like "MySQL" or "Postgres". When you open that up, it's organized in something like sheets which the database world calls "tables". These tables are oriented in columns and rows. The columns are named so you know how to decode the rows. Each row represents a single record in the database.

We then talked about why MongoDB is alluring to many folks. In traditional databases, you have to come up with the names of all of your columns in your sheets up front. If you don't, it can be a little bit of a headache to add them in later. There's not an easy "right click, insert columns" button in your terminal! MongoDB, on the other hand, allows you to skip the up-front column definition. If, while writing your code, you decide you need a new column.. just add it in. The database automatically picks it up and stores the information for you. While it has its own set of problems, MongoDB's support for loose column definitions (also known as a schema) is actually very nice and what attracts a lot of people up front.

Thinking of databases as Excel files is actually a useful enough metaphor for how databases work that it can help frame some of the tradeoffs of databases easily. It also has the benefit in that a lot of people who would be starting (or career transitioning) towards programming likely already have a passing familiarity with Excel.

© 2012 - 2023 · Home — Theme Simpleness