Join Tables painlessly explained

Flat File vs Relational Databases

When learning about backend programming a good understanding of how modern databases work is essential and a lack of understanding can impede your progress. I decided to lay out some of the basics with a focus on join tables.

Lets begin by thinking about the spreadsheet you might have if you were selling shoes. You might start by entering every sale into a single worksheet. You could have columns that look like this:

This would be a flat file. If you filled this spreadsheet out as new orders came in it could be used to fulfill those orders. It is adequate but the problem with this design is already apparent even with just these few columns; when we have repeat customers the same data will be entered again.

If you're wondering what a list of sales like this has to do with join tables I have good news for you. This is a join table or it will be if we design our relational database correctly.

This is a representation of our sales spreadsheet as a table, with the table name at the top and each column in the database below the table name.

Primary Keys vs Foreign Keys

There is one difference from our spreadsheet found in our table description, the column: id. This is our primary key. We could have one in our flat file but it is required here. Every table you create will have one because it facilitates relationships with other tables in your database.

Let's take a peek at what this sales table will look like once we make it a proper join table.

That's it. The sales primary key and then two other entries, customer_id and shoe_id. These are foreign keys, primary keys from other tables and they are all we need to define to make our join table.

This what our other two tables look like.

And this is how they relate to the sales data (and each other).

Each table has its own primary key and its own columns of data. When accessing a row in the the join table, in our case the sales data, we now have access to everything associated with the customer and everything associated with the shoe they purchased.

Many to Many

Because our join table now exists, shoes and customers relate to each other as well. Let's examine this:

A shoe can have many sales.
A customer can have many sales.

But also, through our sales table:

A shoe can have many customers.
A customer can have many shoes.

You will hear these kinds of statements when learning about relational databases. This is describing a many to many relationship.

What it means is that you can now look up a customer and get the name, size and price of every shoe they have purchased, as well as the date they were purchased.

From the shoe side we can access the name and location of every customer that purchased a shoe and when they were purchased.

The power from a database such as this is how customizable the data is now. We can use the data to create sales reports with geographical information, make mailing lists and even help manage inventory and purchasing.

As programmers these join tables and the relational databases they belong to give us the freedom to pick and choose the data we wish to deliver.