How To Implement Parent-Child Relationship in MySQL

If the first time you heard this phrase “parent-child relationship in MySQL”, you went “Ok, anyone else got cool made up names or are we to start brainstorming now”, then this article is for you. For the uninitiated, this might not make much sense. That’s perfectly fine because you do not necessarily need it everyday.

Parent-child relationship in MySQL has to deal with establishing some hierarchal dependencies between records in a database table. In MySQL terms, it means say that “row 12 is the parent of row 14” and stuff like that. In business terms, it will mean establishing a dependency relationship between two similar entities. For example, keeping a record of all the people in a village and matching parents to their children.

So, whenever you hear that phrase, think about a real-world parent-child relationship. For the rest of this article, we are going to explore how to establish said relationships and possible use cases.

Prerequisites

1. Basic knowledge of any SQL-based database.
2. Basic knowledge of programming to help you use it in a real application

Why Should We Use Parent-Child Relationships

Let’s assume we are building a productivity tool like Slack and we want to ensure we follow database normalization principles and eliminate redundant data from our database.

Hypothetically, our database will at least have the following tables:

companies
id
name
subdomain
user_id
created_at

users
id
company_id
first_name
last_name
email
password
created_at

We have a really simple table now. Without thinking too much, you already know that a user will own a company, and multiple users can belong to a company. Right now, all is well and good here and we can proceed to build a great product… yay!!!

Issues will begin to arise when the user creates like 3 more companies (she has a saloon, a restaurant and a cake shop). She needs those 3 company profiles to manage her different businesses and different staff of the businesses. Now, she has to register 3 times with probably the same credentials. Well, it’s just one person, so no much harm done, right?

She then invites 10 staff each to these companies, which is not much if you think about it. But, each of these 10 staff also belongs to at least one more company profiles, either a local group, another side business or an open source project. Now, you have each of these staff registering twice with the same credentials. I’m sweating a little bit thinking about it, but maybe we do not have a crisis on our hands.

Now, scale this scenario up by 1000x and try to imagine what your database table looks like… Take it all in my friend… Take it all in.

Establishing Parent-Child Relationships

The first thing we need to do would be to add a parent_id column to the users table. That column will hold the id of the first record we created for the user. We can then afford to not ask the user to create an account every time they are invited to a company. They can join directly and maybe set their firstname and lastname on that company (you have to allow them to do that). Our users table can now look something like this:

This looks better than what we had above, yes? We have eliminated redundant data completely from our table. Querying the records for our table becomes easier. After users are logged in, you can run a query like this to fetch all the companies tied to the user:

SELECT * FROM `companies` 
WHERE `id` IN (
  SELECT `company_id` FROM `users` 
  WHERE `id` = 1 OR `parent_id` = 1
)

Then you can list out all the companies for the user to select, and you can pick the user’s profile based on the companies they selected. You can do a similar query for that selection this time around:

SELECT * FROM `users` 
WHERE 
  (`company_id` = 217 AND `parent_id` = 1) 
OR 
  (`company_id` = 217 AND `id` = 1);

We use two conditions because we are not absolutely sure if we are selecting a parent or a child.

A better option may be to select both the company and the user profile tied to it, so when the user clicks, you can just retrieve that record directly.

SELECT 
  `companies`.`name`, 
  `users`.`id`, 
  `users`.`name` 
FROM 
  `companies` LEFT JOIN `users` 
ON 
  `users`.`company_id` = `companies`.`id` 
where 
  `users`.`parent_id` = 1 or `users`.`id` = 1

This will return the name of the company, id of the user and the name of the user. You can add this as a data-attribute to your HTML when you render the data and send it to your server when the user clicks on a company card, name or whatever you represent it with. You can also return more stuff depending on your own needs.

Do not forget to set limits where appropriate, as you do not want to overload your server with the task of fetching unnecessary results. Also, replace variables as they relate to your database tables.

Conclusion

We have explored a parent-child relationship at a very basic level. From the little table records we created, we can already see how this keeps our table a lot cleaner as we begin to add more users. Equally, we can already anticipate how much this will improve the experience of our users if all they have to do is just click a link and join a company.

Slack has a similar implementation for their authentication process. When next you use their company finder, understand that such a scenario could be implemented with a parent-child relationship.

Source: Scotch.io