Database Anatomy

So you probably know that a database essentially just stores data. And yeah, you're right. But if you're curious about HOW said data is stored, then this is the blog for you. We're going to go over the different concepts in your everyday database to give you an idea of not only how data is stored, but why certain practices are considered 'best'. And for our example, we're looking at SQL as an example, specifically MySQL. Let's get into it.

Database Tables

So databases can store pretty much whatever information you want them to, and different types of data that exist in the same database will be separated by tables. Each table will have several columns. This is almost perfectly analogous to an excel spreadsheet if you've ever used one. You will have multiple rows in this table, each one with the same columns as the rest, though the information will be different. You can call these columns whatever you want, but it's best to call them something relevant. Let's look at a couple of examples here. First off, let's make a 'profiles' table so people can store their basic profile data. And we're going to keep it simple, so not a bunch of preferences will be held. Just general account information.

We start with the following columns:

• ID
• First Name
• Last Name
• Email
• Password
• Bio
• Photo
• Status
• Created
• Modified
• Login

Okay, so let's unpack this all. The first column of ID is going to be a number, and it's usually set to auto-increment. This means it will go up by 1 every time a new profile is created. We'll make this a "Primary Key", which is a way to let the database know this is the main way we want to identify each individual profile. It will also prevent any two profiles from having the same ID. First name and last name, those are just going to be text, nothing special. Email will technically be the same, but we can apply something called "Unique" to this column. It'll basically keep from multiple users having the same email address, so you can almost treat it as a secondary identifier when you don't have the profile ID, such as someone signing in with their email address. Password, also text. Though don't worry, as almost every single web application out there encrypts your password before it reaches the database. So even if someone DID get database access, they'd just see gibberish. Bio, that's just gonna be a short description that users can enter about themselves. Image is a very purposeful example here. Rather than storing an image, which a database can't do, it WILL store the URL of said image that can be used wherever needed.

So now we get to the fun stuff. These are what make a solid database developer. "Status" is a good way to 'delete' a row in a database table. See, when you delete something from a database, it's gone. And unless you have an older copy of that database, there's no getting it back. So when you deactivate an account somewhere, it usually won't get deleted. Instead, there's going to be a column like our "Status" example that may have options like "Active" or "Inactive", or even "Deleted". That way, the information is still in the database in case the user wants to reactivate their account, and can just pick up where they left off.

Created, this is a good one. It won't store text, but a date of when the profile (or whatever other tables you're using) was created. Modified is a similar situation, but it updates any time you make a change to the table. Mind you, this isn't automatic. But it's great practice to have these two columns to help keep track of things. And lastly, login will do the same thing. Here is where we'll keep track of the user's last login. You won't see this a lot on other tables, as there's nothing that needs to log in or out, but it should always be a column in a user/profile table.

So now let's look at another example because we're going to need it to bring the 'relational' part into focus. It's going to be simple, just a "Project" table where we can store things that are being worked on. We'll use the following columns:

• ID
• Name
• Description
• Status
• Created
• Modified

Most of these are going to be the same as what you saw in the profile example, so we won't bother going into the details of all of these. Similarly to the "email" column for profiles, we may want to make "Name" a unique column so we don't have multiple projects with the same name. Now let's get to the exciting part.

Connecting The Two

So these two tables, projects, and profiles have no way of knowing that each other exist. We want to create a way to connect them, make them 'related' to one another in some way. So we're going to need to make ONE more table:

• ID
• Profile
• Project
• Status
• Created
• Modified

So now we only have two 'new' columns that you haven't seen yet. One for a profile, and one for a project. These will store a bridge between the two other tables. Ideally, "Profile" will store a number (that matches a profile ID), but we can also use the profile email and that would be fine. We can even tell the database that it is something called a "Foreign Key", so that it knows what it is meant to do. "Project" will also be the same. We store the project's ID here, though again the project name would also suffice since it's unique. This will basically grant users access to certain projects. So now, let's walk through the thought process an application will go through. After all, who doesn't love a good example?

Walking through it.

So you logged in to this imaginary application. Your profile ID is 123456, and you want to see all the projects you're assigned to. Well, you won't reach into the project database. At least not yet. You're going to go into the "Access" table and get a list of all "Access" rows where the project column matches your profile ID. This will give you a list of project IDs. Then once you have those, you can go into the "Project" table and get the data for each of those projects where the IDs match the Access->profile->ID.

And that's all, folks. Hopefully, now you understand how a relational database works.

‍