Design relation in DB properly

Databases are the backbone of any modern system, allowing us to store, organize, and retrieve data efficiently. One of the key aspects of the database design is establishing relationships between different entities or tables. Despite the fact relations are fundamental concepts, developers still struggle with organizing them properly.

In this article, we will delve into three fundamental types of relationships: one-to-one (1–1), one-to-many (1-M), and many-to-many (N-M). We're also going to find out what the heck self-joining relation is. You will see how easily determine which one should be applied. The difference between those and the caveat they hide.

If this is exactly what you are looking for, let’s get straight to the article👇

Determine relations like a pro

It may seem like determining the relation between tables are complex and complicated topic that can be handled only by experienced devs, but in practice, it is a piece of cake. You don’t need to analyze the data model, consider business rules and use cases, seek normalization, and so on. Those are for nerds.

Here is how real giga chads do it 💪:

1. Peek any domain. A blogging platform like Medium would be great for that.
2. Peek any 2 entities from that domain. Let’s say Article and Comments.
3. To determine the relation between 2 random entities, ask yourself a simple question, “How many of one Entity can relate of another?”. Then do the same for another entity.

Let’s practice it with our domain:

“How many comments One Article can have?” — Many.

“How many articles One Comment can relate?” — One.

That’s the answer. You have a one-to-many relation between Articles and Comments.

To master it, practice that trick every time you see db relations 😉

As you see, there are multiple ways of representing relation on a schema, using numbers, arrows, marks, and so on. Here is mine, beloved notation:

Mandatory and optional relations are rarely used, but I will still lets them be there, so you don’t get confused seeing them.

One-to-one

The most fundamental relation is one-to-one, where one record in a table is associated with a single record in another table. This relationship represents a strict pairing of data elements between two tables.

This relation may seem like the easiest one, but in practice not so. Developers even struggle to decide whether everything should be placed in one table or separeted😅. We will examine the second case.

Let’s say we have Users and their Settings. Surely, it is a one-to-one relationship. The real question here is where to put a foreign key 🤔.

Records in the Users table can exist on their own, therefore it is a primary table. You can not say the same about Settings, so it will be a secondary one.

If you put a not-nullable foreign key in the principal table, it will force records in the dependent table to be mandatory. There is always a Setting record for a User.

However, if you put that foreign key in the dependent table, it will make them optional.

A nullable foreign key in the primary table will also make the dependent one to be optional.

On the other hand, a nullable foreign key in the secondary table makes no sense.

Usually, a one-to-one relation appears when you want to extract some columns from a table to a separate one. However, that is not always the case. If you have 2 tables in one-to-one relation where both of them are primary and can coexist independently, then a foreign key can be placed in any of those, regardless if it is nullable or not.

One-to-many

The most common relationship would be one-to-many, allowing one record in a table to be associated with multiple records in another table.

The foreign key in this case will always be on the side of a table with the “many” end of the relationship.

The foreign key can be nullable if only the table is primary and records can exist without that relation.

Many-to-many

The most complex relation is many-to-many. It comes into play when multiple records in one table can be associated with multiple records in another.

It is not possible to express such a relationship with a single foreign key in any of the tables.

As a result, an intermediate table appears. Usually, it is named after those two tables.

The common misunderstanding here is that this table is only for foreign keys. It is not. It can contain additional attributes. If you feel that is your case, just go for it without any hesitation. For example, we could add the next fields to the ArticleCategory table: Priority, AddedDate, IsFeatured, and so on.

Self-joining

The most rare relation is the self-join one. A self-join, also known as a self-referencing or recursive relation, is a type of relationship in which a table is associated with itself. In other words, it involves creating a relationship between rows within the same table.

It is not very common, however, recursive references will still appear from time to time, so you need to know how to handle them.

Let’s say we have employees of different types: regular white collars and their managers. We can store them in different tables, but since they have the same properties, let’s just put them together. That’s how recursion appears:

To find the manager of each employee, you can use a self-join operation. Here is an example of a query:

SELECT 
  [E].[Name] AS [EmployeeName], 
  [M].[Name] AS [ManagerName]
FROM [Employee] AS [E]
JOIN [Employee] AS [M] ON [E].[ManagerID] = [M].[EmployeeID];

However, that only works if you have a 1–1 or 1-M relationship between managers and employees. What if not only a manager can supervise many employees but also an employee can obey multiple managers, meaning N-M relation? In the same way as we had in a many-to-many relationship, an intermediate table has to be introduced:

Conclusion

I hope it wasn’t too hard 😅. In case it was, do not give up and still practice it 😤. Understanding the nuances of database relationships is essential for building robust and efficient database structures.

Whether it’s the precision of a one-to-one relationship, the versatility of a one-to-many relationship, or the complexity of a many-to-many relationship, each type has its own significance in database design.

By understanding these relationships, you can create cohesive and scalable data models that accurately represent real-world scenarios.

That’s all folks 🐷 I hope you learned something new here 🤓 Give this article a clap 👏 Support me with coffee ☕️ And do not forget to follow to receive more on SQL and databases design✅