Database Relationships

0
Posted by: Bogey on April 12, 2024

Relationships in database vary from simple one-to-one relationships to more sophisticated many-to-many relationships and they are arguably the backbone of any complex application which would have multiple relationships set-up to make various of it’s features work.

A relationship works when a specific column in a table holds a reference to a column in a different table by which you form the logic in the application to join the two tables together. That specific column is referred to as the foreign key and the column it references on the other table is usually titled as id and is known as the primary key. So in summary, the foreign key of one table references the primary key of the corresponding table.

The format of relationship names is usually parent-to-child. For example, a one-to-many relationship signifies a relationship where one record in a parent table can be related to many records in a child table. In this type of relationship, each record in the parent table can be related to one or more records in the child table, while each record in the child table is related to only one record in the parent table.

You could follow along using SQLFiddle using the sample SQL to help mess around with various relationship types.

One-to-One

A one-to-one relationship is when one table is associated uniquely to another table. Both data entries that are associated can only be associated together and not with any other data of the same table. So one parent can have only one child and a child can belong to only one parent.

For example a person in USA will have a one-to-one relationship with their social security number; the person could have only one social security number and the social security number could only identify one person. You could have a settings table that is uniquely associated with a specific user who utilizes those settings to configure his/her experience on your site.

A one-to-one relationship is used between two related pieces of information that are stored separately for performance, logical or security purposes. If your application has reasons and the means to make payments, you would store the payment information (such as banking and billing information) in a different table from users, which would allow you to restrict the access to the payment information from groups that has nothing to do with the information. For instance, in an organization, specific groups of customer support will have the access to the payment information, as well as the required administrators/moderators.

Another appropriate use of a one-to-one relationship is the separation of concerns as it is with users table and the settings table. A user will be associated to a specific settings entry through a column in users table that references the unique identifying column in the settings table. Usually, every table has an ID column that other tables could reference to form a relationship which you could use in the logic of your application to join the two pieces of information together.

CREATE TABLE `user` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    settings_id INT,
    name VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255)
);

CREATE TABLE `settings` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    theme VARCHAR(255),
    timezone VARCHAR(50),
    newsletter BOOLEAN,
    FOREIGN KEY (user_id) REFERENCES `user`(id) ON DELETE CASCADE
);

INSERT INTO `user` (id, settings_id, name, email, password) VALUES
(1, 1, 'John Doe', 'johndoe@example.com', 'password123'),
(2, 2, 'Jane Smith', 'janesmith@example.com', 'securepass'),
(3, 3, 'Alex Johnson', 'alex@example.com', 'mypassword');

INSERT INTO `settings` (id, user_id, theme, timezone, newsletter) VALUES
(1, 1, 'Light', 'UTC', 1),
(2, 2, 'Dark', 'GMT', 0),
(3, 3, 'Light', 'PST', 1);

Here we got two tables, user table and settings where both of the tables reference each other’s id column with their respective reference column formatted as table_id. User table’s settings_id (foreign key) references the id (primary key) that is in the settings table and settings table’s user_id (foreign key) references the id (primary key) that is in the user table. This way, when you are pulling in the information for the current user that is browsing, you could use the foreign key to combine the settings for the user to set up their browsing experience as they have configured it such as utilizing the theme of the application that they chose and setting the times for posts to their timezones and so on.

In this example, a user can be associated to only one settings entry and each entry in the settings table could be associated to only one user of the application.

Having user table and settings table separate helps with organization and keeps the tables from getting cluttered and gigantic, especially since big applications would have a lot of information to store about each of their users. Another positive attribute of separating information like this is to improve performance since you aren’t always going to need the settings data with the profile information, therefore the dataset in those instances are going to be smaller.

Please note that the parent table user has a foreign key in it. Usually the parent table doesn’t have a foreign key pointing to it’s child table unless it’s necessary to constraint the parent table based on the child table’s existence or modification. The decision to have a foreign key in the parent table comes when planning out the database for the application and how certain scenarios should play out.

In the current database schema, we have added some referential integrity actions to make sure our database is consistent and up-to-date.

The integrity action on the settings table’s foreign key with ON DELETE CASCADE ensures that when a user’s record is deleted from the user table, the corresponding settings record for that user is also deleted from the settings table. This cascading delete behavior maintains data integrity by removing associated settings when the user is deleted.

The integrity action on the user table’s foreign key with ON DELETE RESTRICT prevents the deletion of a settings record if it is associated with any user. This restriction ensures that a settings record cannot be deleted as long as it is referenced by any user in the user table, maintaining the link between users and their settings.

Also take note of the attribute UNIQUE on the foreign key on the settings table. This ensures that no two rows would have the same user id. Makes sure that each user could possibly only have one record in the settings table that is associated with them.

SELECT
	*
FROM
	`user`
JOIN
	`settings` ON `user`.settings_id = `settings`.id;

That SQL query would join the appropriate record from the settings table with the returned results from the user table based on the foreign key settings_id in the user table that references the primary key id in the settings table.

One-to-Many

A one-to-many relationship is when a table can have many entries of the corresponding table it associates with while a single entry from the associated table could be bound to only one entry of the original table. A one-to-many relationship signifies a relationship where one record in a parent table can be related to many records in a child table. In this type of relationship, each record in the parent table can be related to one or more records in the child table, while each record in the child table is related to only one record in the parent table. (Repetition is good).

A user (parent table) could have many orders (child table) while each order would belong to only one user.

CREATE TABLE `order` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
	status VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES `user`(id)
);

INSERT INTO `order` (id, user_id, status) VALUES
(1, 1, 'Pending'),
(2, 2, 'Completed'),
(3, 3, 'Processing'),
(4, 1, 'Processing'),
(5, 1, 'Completed'),
(6, 2, 'Pending');

Here we add the order table to the example database schema we previously started. Each record in the order table will be related to only one user while each user could have multiple entries in the order table. Notice the missing UNIQUE attribute after the user_id column while the settings table’s foreign key has the UNIQUE attribute ensuring that there is only a single record for each user in the settings table.

This is beneficial since it helps organize the related data efficiently and keeps them together. It also reduced redundancy since the common denominator to the child table is the parent table and keeps the relevant information from unnecessary repetition. By storing shared information in the parent table, you avoid duplicating data across child tables. This also promotes efficient queries since you could retrieve more information with a single query instead of having an n+1 issue when you loop through a query result set and based on the retrieved data retrieve additional information with additional queries.

SELECT
    `user`.id,
    `user`.name,
    COUNT(`order`.id) AS order_count
FROM
    `user`
LEFT JOIN
    `order` ON `user`.id = `order`.user_id
GROUP BY
    `user`.id, `user`.name;

This query would select the required columns from the user table as well as the count of orders each user has made using the records from the order table. The LEFT JOIN ensures that the results from the left table (the user table) are included in the results regardless if they made any orders or not. If we were to use JOIN or INNER JOIN instead, the users who didn’t make any orders won’t be included in the results of that query.

Many-to-One

A many-to-one relationship is the same as one-to-many relationship, except its from the other perspective. In this instance, it is child-to-parent view-point. Using the database schema from the previous example, the order table has a many-to-one relationship with the user table.

SELECT
    `user`.id AS user_id,
    `user`.name AS user_name,
    `order`.id AS order_id,
    `order`.status AS order_status
FROM
    `user`
LEFT JOIN
    `order` ON user.id = `order`.user_id
GROUP BY
    `user`.id, `user`.name, `order`.id, `order`.status;

In this instance, we are working from the perspective of the orders, instead of getting all users with their orders, we are simply retrieving all orders with their corresponding information.

Many-to-Many

A many-to-many relationship is when a record from the parent table could be associated with many records from the child table and a record from the child table could have many associated records from the parent table. Therefore, in a many-to-many relationship, both tables can have multiple related records, and the relationship is established through a junction or associative table that manages the connections between the parent and child records.

The junction or associative table is sometimes known as the pivot table, bridge table, cross-reference table, intermediate table, or join table. This table holds the foreign key and primary key combinations per record that ties each parent record to their corresponding child table record.

Think of courses that students enroll to in school. Each course could have many students enrolled in it and each student would be enrolled in multiple courses. To keep this data in a database you would need to establish a many-to-many relationship between courses and students. In our example that we are extending from the previous relationship types, we are establishing a many-to-many relationship between the user table and the newly created group table. Each user could be part of many groups and each group could have many users in it.

CREATE TABLE `group` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) UNIQUE
);

CREATE TABLE `user_group` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    group_id INT,
    FOREIGN KEY (user_id) REFERENCES `user`(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES `group`(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_group (user_id, group_id)
);

INSERT INTO `group` (id, name) VALUES
(1, 'Admins'),
(2, 'Editors'),
(3, 'Viewers');

INSERT INTO `user_group` (id, user_id, group_id) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 1, 2),
(5, 2, 3);

The user_group table there is the junction table that ties each user to the group that they are participating allowing you to join the results of both tables through the key combinations stored inside this junction table.

SELECT 
    `group`.id AS group_id,
    `group`.name AS group_name,
    `user`.id AS user_id,
    `user`.name AS user_name,
    `user`.email AS user_email
FROM 
    `group`
INNER JOIN 
    `user_group` ON `group`.id = `user_group`.group_id
INNER JOIN 
    `user` ON `user_group`.user_id = `user`.id
ORDER BY 
    `group`.id, `user`.id;

This SQL will retrieve all the users and the groups that they are part of and using these results we could format this data in a human readable format.

Conclusion

In conclusion, understanding and effectively utilizing standard relationships in databases is fundamental to designing efficient and scalable data models. Throughout this article, we’ve explored the core concepts of one-to-one, one-to-many, and many-to-many relationships, along with their practical applications and considerations.

One-to-one relationships are useful for modeling closely related data that is uniquely associated with another entity, such as a user and their settings.

One-to-many relationships are common in scenarios where one entity can have multiple related records, such as a user having multiple orders or comments.

Many-to-many relationships are essential for representing complex connections between entities, like users belonging to multiple groups or products being associated with multiple categories.

By choosing the appropriate relationship type and implementing foreign keys, constraints, and indexes effectively, database designers can ensure data integrity, optimize query performance, and maintain a robust data architecture.

It’s important to remember that while these standard relationships provide a solid foundation, real-world databases often require more nuanced modeling, such as polymorphic associations or denormalization, to address specific requirements and optimize performance further.

Overall, mastering standard relationships in databases empowers developers and database administrators to design scalable, maintainable, and efficient data structures that form the backbone of modern applications.

Below is the entire SQL used throughout the post.

CREATE TABLE `user` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    settings_id INT,
    name VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    password VARCHAR(255)
);

CREATE TABLE `settings` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT UNIQUE,
    theme VARCHAR(255),
    timezone VARCHAR(50),
    newsletter BOOLEAN,
    FOREIGN KEY (user_id) REFERENCES `user`(id) ON DELETE CASCADE
);

CREATE TABLE `order` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
	status VARCHAR(255),
    FOREIGN KEY (user_id) REFERENCES `user`(id)
);

CREATE TABLE `group` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) UNIQUE
);

CREATE TABLE `user_group` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    group_id INT,
    FOREIGN KEY (user_id) REFERENCES `user`(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES `group`(id) ON DELETE CASCADE,
    UNIQUE KEY unique_user_group (user_id, group_id)
);

INSERT INTO `user` (id, settings_id, name, email, password) VALUES
(1, 1, 'John Doe', 'johndoe@example.com', 'password123'),
(2, 2, 'Jane Smith', 'janesmith@example.com', 'securepass'),
(3, 3, 'Alex Johnson', 'alex@example.com', 'mypassword');

INSERT INTO `settings` (id, user_id, theme, timezone, newsletter) VALUES
(1, 1, 'Light', 'UTC', 1),
(2, 2, 'Dark', 'GMT', 0),
(3, 3, 'Light', 'PST', 1);

INSERT INTO `order` (id, user_id, status) VALUES
(1, 1, 'Pending'),
(2, 2, 'Completed'),
(3, 3, 'Processing'),
(4, 1, 'Processing'),
(5, 1, 'Completed'),
(6, 2, 'Pending');

INSERT INTO `group` (id, name) VALUES
(1, 'Admins'),
(2, 'Editors'),
(3, 'Viewers');

INSERT INTO `user_group` (id, user_id, group_id) VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 1, 2),
(5, 2, 3);

Comments

No Comments. Be the first to make a comment.

Need to Register or Login to comment on post