Effective modeling of database relationships is essential to the most common type of database used in business – relational databases. While database technologies are now rapidly evolving into specialized solutions, such as NoSQL data stores used in MongoDB development services or cloud databases like the Databricks data warehouse solution that combines aspects of a data lake and data warehouse, this article focuses on widely used database systems. Relational databases enable users to store data and create specific types of relationships between the data captured. This is highly useful when developing software applications, as it limits the scope of changes made to the database as well as allows developers to create a “map” of the data tables and their relationships. These relationships also sometimes function as “rules” for how data is stored.
The term “database relationship” refers to the rules for one table to be related to, or “connected” to another table. There are four basic types of relationships which account for the full spectrum of development use cases. These are:
- One-to-One
- One-to-Many
- Many-to-One
- Many-to-Many
Those familiar with database design might notice that these names are descriptive of the relationships themselves. Thus, simply by reading the names, you may already have an idea of how these relationships work. However, those unfamiliar with the basic tenets of database design are probably still wondering what exactly the term “database relationships” refers to. Let’s step through each type of relationship, with examples taken from a hypothetical software development client who, prior to having their own application, had to store all of their data on spreadsheets, managed by a project administrator. These examples are useful due to the fact that, many times, it is up to the developer to take these spreadsheets, discern the relationships between them, and translate them into an ERD (Entity Relationship Diagram).
One-to-One
The one-to-one relationship is a database relationship in which a single record on one table is related to a single record on a different table. We can see simple real-world examples of this when one Employee is assigned to one Office. The office is not shared with any other employees, nor does the employee work in any other offices. Another example might be that each department has one manager – an individual manager is only assigned to one department, and that department has no other managers.
Let’s take a look at our hypothetical client’s spreadsheets:
The above example shows an employee list with columns for employee info, as well as columns for the employee’s passports – though it should be noted that not all employees have passports. Because an individual can only have one passport, and a single passport cannot belong to more than one individual at a time, we can see that this is a one-to-one relationship. While a new developer might be tempted to simply enter all of these columns as fields in a single database table, it may be useful to split this spreadsheet into two separate tables – one for Employee information, and another for Passport information. Chopping information up into discrete database tables is a process known as “normalization,” and is vital in keeping databases organized and ready for future updates.
Since we have decided to keep separate tables for Employees and Passports, we’ll need to relate them via a one-to-one relationship. This can be easily accomplished by setting the Passport ID field as a foreign key relation to the Employee ID field. This means that each Passport record’s ID will be identical to its Employee’s ID. This association will look something like this in an ERD:
This method ensures that a single Employee record can only have a single Passport record, and vice-versa, as IDs must be unique per table. Developers will need to be sure to account for this in their code – when saving a new passport record, you will need to be sure to save it with the Employee’s ID to which it belongs (as opposed to allowing it to auto-increment). While this might seem somewhat intimidating, many backend frameworks have the ability to account for this.
One-to-Many
The one-to-many relationship occurs when a single record of one table needs to be connected to multiple records from another table. A common example of this would be that an individual Customer can create many Orders. So, groups of orders may all belong to the same customer. However, a single order cannot belong to multiple customers. This example is shown in our client’s spreadsheets:
We can see that we have a Customers table, as well as an Orders table with customer names included. We can also see that one customer, John Smith, is responsible for three separate orders.
In cases like these, the solution is fairly simple. Because each Order is only related to one Customer, we can add a Customer ID field to the Orders table to establish the relation:
This allows customers to make multiple orders while only having their customer profile stored once.
Many-to-One
The many-to-one relationship can be thought of as the reverse of a one-to-many relationship. Instead of having one record that needs to be connected to many records, you may have many records that need to be related to a single record. In this case, the relationship can be established in the same way as the one-to-many relationship above.
For example, if you have an orders table where multiple orders have been placed by a single customer, the relationship is actually identical to the previous example.
Many-to-Many
Many-to-many relationships occur when multiple records of one type need to be related to multiple records of another type. A simple example of this would be employees assigned to projects. Multiple employees are assigned to a single project, but each employee is assigned to multiple projects.
In cases like these, we need to use what is referred to as a “mapping table” – this is an intermediary table that stores primary keys for both Employees and Projects. That way, every time an employee is assigned to a project, a new record will be created on the mapping table. This keeps the database free of fields that are prone to having duplicate data (if you decided to keep project assignments inside the Employee table, for example). Let’s take a look at our client’s spreadsheets:
The Employees table, which is now free of extraneous passport information, has multiple columns devoted to project names. This would be rather ungainly in a database – for one, because it creates a hard limit for the number of projects that can be assigned to an employee. A mapping table will eliminate this constraint, as shown below:
When it comes to mapping tables, it is up to the developer to make sure that any rules are imposed via their code. This may include checking for duplicates before saving a new mapping record, or simply validating the number of records currently associated with a particular foreign key. This is another case in which frameworks are often helpful.
Conclusion
While database relationships can seem challenging to learn, remember this is a business exercise. Think of the information that your company handles and how some of it can be related to one another. Most information that is related can be boiled down to one of the four types listed above. When working with an existing database, it can be helpful to take a step back, look at the database in small chunks, and identify which of the four main relationships those tables are making use of. Good database design can go a long way in future-proofing an application, while bad database design can hinder progress and cause software developers to feel as though they are attempting to untangle a bowl of spaghetti. So if you are planning your next generation of software and are using any of the Top 5 most popular relational database management systems (RDMS) used worldwide (according to Statista) topped by Oracle, MySQL, Microsoft SQL Server, and PostgreSQL, then check out this article!
