How To Implement Parent-Child Relationship in MySQL

  • 2018-12-01 04:58 AM
  • 78

Using concept of foreign keys and references, you can create a system in MySQL with a parent-child relationship. Create a table which will act as your parent table and another one which acts as a child. Find a column in both which will act as a relationship between the two tables.

Aiodex’s Referral Program will give you 20% -80% commission from their transaction fee for 7 years. The value will be calculated starting from the date the member you invite sign up. Starting from the first year is 20%, after each year increased to 10% from the first day of next year. For the 300K members who are enjoying the transaction fee refund policy, if they are one of your invited members, you will receive a commission until the system start charging their transaction fees. For example, if the new system start charging from 2020–01–01, you will start receiving commissions and since it is the second year then you will receive 30% ☞ https://aiodex.com/p/referral-program
Get Free 15 Geek ☞ https://my.geekcash.io/ref/5b3c4924d38b6158ce04633f or http://geekcash.org/
Learn to Code ☞ https://codequs.com
CodeGeek’s Discuss ☞ https://discord.gg/KAe3AnN
Playlists Video Tutorial ☞ http://vrl.to/d5fc7d45

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.

Learn More

The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert

SQL - MySQL for Data Analytics and Business Intelligence

The Complete SQL Bootcamp

The Complete Oracle SQL Certification Course

SQL for Newbs: Data Analysis for Beginners

Suggest