How Does Data Get Stored in a Database?

This question came up in my private Slack channel the other day. We had a great conversation amongst the companies involved, and I’m sharing the highlights here.

For the purposes of this conversation, we’ll be talking about relational databases like MySQL, Postgres and SQL Server. There are lots of different kinds of databases, but at least for now relational is the most common. I’ll also not be getting into the particulars of the actual technology, but rather the concepts that you need to understand to be conversant with developers.

For our examples we’ll assume that we’re building an application that allows people to buy and sell cars.

Defining Your Database

A database is, at it’s very basic level, a collection of tables. A table is just like what it sounds. It’s a collection of rows and columns. The rows are the data itself, the columns are the attributes we care about for a table. Think of a table as nothing more than a spreadsheet. It’s really that simple. We give this the fancy name of tabular data.

Let’s say we wanted to store information about the cars we’re selling. We might have a table that looks like this:

In a database table, much like in a spreadsheet, a column is a specific kind of data. We call these Types, but you can think of it as a column format. There are three buckets of column types in a database: Text, numeric and other. Let’s look at details of each of these buckets.

Text Column Types

Text columns hold varying lengths of, well, text. Text can be numbers, characters, punctuation, or anything else that you think of when you think of all the stuff on your keyboard. The most common text column types are:

• VARCHAR – This is varying length character column. This means that when you define the column you designate the maximum length of the text that can be stored. For example, if you had a column that was VARCHAR(60), that would mean that the column can hold a maximum of 60 characters. In the above table, Make and Model would probably be VARCHAR columns
• CHAR – This is a character column. As with VARCHAR, you define the length of the column. The difference between VARCHAR and CHAR is that CHAR is fixed storage length, meaning that the database will pad your text with spaces if you don’t fill the entire field
• TEXT – A TEXT column type is used when you don’t know how long the text being stored could / should be. It has no pre-defined length

Numeric Column Types

There’s a good bit more complexity around numeric column types than text column types. I’m only going to cover a few here.

• INTEGER – Whole numbers, just like you would imagine. The “ID” column above is an example of an Integer column
• DECIMAL – The “Price” column above is an example of a Decimal column. In a decimal column, you typically define the largest value to the left of the decimal and the valid spots beyond the decimal point. This is why it works well for values like currency
• FLOAT – Floating point decimals. Unlike a DECIMAL column, the decimal can float in the values being stored. For example, a FLOAT could store 1.2345, 1.2, and 12354.1243435345 in the same column.

Other Column Types

The most common other types are:

• BOOLEAN – A BOOLEAN column stores the value TRUE or FALSE. The “IsSold” column above is an example of a BOOLEAN column.
• BLOB or BYTE ARRAY – BLOB columns are used to store things like images. It’s more common these days to not store the actual image but rather a link to the image, but in some cases you might want to store the image itself. The reason for this is that services like Amazon’s CDN (Content Delivery Network) make it very simple to store your images with them and serve them up faster than if they were coming from your servers. None the less, BLOB remains a column you’ll at least want to know about

How It Comes Together

Once you’ve defined your database, your developers write some code that takes the data that your users input and creates rows of data in your database table. In our example, every time a user adds a car that they want to sell, the application would create a new row in the database. If they delete a car, your code deletes that row.

Your developers would also add some code before the data gets saved to validate the inputs from the user. For example, they might make sure that the value of Year is between 1960 and 2016.

We could go on and on, as this topic is deep and wide, but let’s recap this introduction to the topic.

Just Remember

There are lots of different kinds of databases, but the most common one these days is still what’s called a relational database. Relational databases store data in tables. Think of a table as a spreadsheet. The database stores data for each table in a row, just like in a spreadsheet.

There are lots of different column types, but a column type is just a fancy way of defining the format of a column. If you try to save the wrong kind of data in a column (for example saving text to an INTEGER), the database will respond with an error. They’re particular that way.

Your Assignment

Have your developers walk you through the database for your application. There’s going to be some stuff you don’t understand, for sure, but you’ll start to get a sense for how all of your data is being organized and stored. If your developers have done a good job, the organization of your data will make sense to you, even if you don’t understand all of the nuances.

If this topic is interesting to you and you’d like to know more about it or discuss it further, please join my private Slack channel where you can ask questions like this all the time.