Having used MongoDB during my much of my early Javascript develpment, I was spoilt by how simple mongoose had been. However, as you develop further into backend development, it becomes difficult to ignore SQL.

You will notice many differences between SQL and mongoose — in particular, that there is no equivalent for mongoose for SQL database systems. And now since everything is stored in tables (not objects), relations are handled differently too.

Types of Relations

This was a new concept to me when utilising SQL databases. SQL puritans will claim that there are only two types of relations, Many-to-One and Many-to-Many, (with a small subset referring to None as another type of relation).

Direction becomes very important when defining these. We will approach it from the table’s point of view and look at five relation types:- Join-to-One, One-to-Join, Many-to-One, One-to-Many and Many-to-Many. However along the way, you will be introduced to one or two more types for completion.

One-to-One

Wait, this wasn’t even one of the relations you identified.

So to understand where Join fits in, we should first look at one-to-one relations and see why none of this (one-to-one, join-to-one and one-to-join) is recommended. We will use two tables USER and PROFILE. The relationship dictated is that each User can only have one Profile and each Profile can belong to only one User. The two tables would initially look something like this:

However, if there is a true one-to-one relationship between these two tables, there shouldn’t be two tables at all. This table structure is best optimised by merging the two tables together to form one table containing both sets of fields:-

However, we may still need two separate tables for a variety of reasons (at the expense of efficient database queries). One table may contain sensitive data, or its mere existence is expected to be temporary. In this case, we would still need to invent a way of linking or joining rows between the two tables.

Join-to-One

The rows in the USER table do not match exactly with the rows in the PROFILE table. Another column needs to be added to link one table to the other. In a true one-to-one relationship, it does not matter in which table this occurs. The Join keyword helps identify the table which contains this column.

In this example, the USER table has a join-to-one relationship with the PROFILE table and thus will contain the column profile_id. This identifies the row in the PROFILE table that is associated with the User.

USER.profile_id will have 2 constraints

- FOREIGN key constraint (referencing the PROFILE table)

- UNIQUE constraint, as the relationship is one-to-one

One-to-Join

If the reference column was stored on the PROFILE table instead, the USER table would have a one-to-join relationship with the PROFILE table. Conversely, the PROFILE table would have a join-to-one relationship with the USER table. The two tables would look something like this:-

PROFILE.user_id will have 2 constraints

- FOREIGN key constraint (referencing the USER table)

- UNIQUE constraint, as the relationship is one-to-one

Join-to-Join

!!!! NOT RECOMMENDED !!!!

Wouldn’t it be niftier if both tables contained reference_ids so I could easily access one from the other?

No. No, it wouldn’t. Errors are bound to crop up inserting and updating data. They will be thrown when rows are deleted. A cyclical reference may have been fine in MongoDB but it is definitely not recommended here.

Choose a table to contain your join column and stick to it! Or better yet, just merge the two tables together already.

However, for the curious, this is what it would look like:-

PROFILE.user_id will have 2 constraints

- FOREIGN key constraint (referencing the USER table)

- UNIQUE constraint, as the relationship is one-to-one USER.profile_id will have 2 constraints

- FOREIGN key constraint (referencing the PROFILE table)

- UNIQUE constraint, as the relationship is one-to-one

One-to-Many

Let’s use two different tables here: AUTHOR and BOOK.

Each Book can belong to an Author, but several Books may have the same Author. In other words, the AUTHOR table has a one-to-many relationship with the BOOK table. (Each Author can have multiple Books)

In MongoDB and other NoSQL databases you could store this as an array with your document.

{

id: 1,

name: JK Rowling,

dob: 1965,

Books: [1, 2]

} As long as Author.Books references the Book schema/model.

This does not work in SQL as well due to several reasons. The first is the incapability of many relational database management systems to store array. And if arrays could be stored via serialization or conversion into a string, the ability to easily sift through the items within the array is then lost. Postgres does have support for array types and they can be queried with the ANY operator. This is helpful but still does not represent the way one-to-many relations should be designed.

Instead, as each Book can only have one Author, the Author ID should be saved as a column in the BOOK table. This saves us having to save arrays of any kind.

BOOK.Author_id will have 1 constraint

- FOREIGN key constraint (referencing the AUTHOR table)

This leads us very naturally into…

Many-to-One

This is our first true SQL relationship. Using the same example as above, we can say that the BOOK table has a many-to-one relationship with the AUTHOR table. The foreign key column is stored in the BOOK table referencing the id of the corresponding author.

BOOK.Author_id will have 1 constraint

- FOREIGN key constraint (referencing the AUTHOR table)

Many-to-Many

But if we can’t store arrays, how could we possibly deal with many-to-many relationships! Let’s create a new table here GENRE, of which each Book can have multiple Genres and each Genre can be associated with many Books.

The idea is to create a whole new table altogether — RELATION_BOOK_GENRE. In this new table, two columns will exist: one referencing the BOOK table and another referencing the Genre table. To reduce the risk of redundancy, we can also introduce a constraint ensuring that no two rows in this table can be identical.

Relation_Book_Genre will have 3 constraints

- FOREIGN key constraint on book_id (referencing the BOOK table)

- FOREIGN key constraint on genre_id (referencing the GENRE table)

- UNIQUE key constraint on ( book_id, genre_id )

None

For the sake of completion, I have to mention the None relationship. This is when a table has no relationship with another table.

Conclusion

I hope I have managed to explain the intricacies of SQL relations in a clearer fashion. Please do drop a comment if anything I mentioned was incorrect for future readers and I will try and edit this article as soon as possible. I do hope this has been useful to get your head around this very unnatural way of thinking.

The next article will be about Creating Tables in SQL taking into account relations.