Thursday, January 10, 2019

How can we store self-referencing entities in MySQL?

Self-referencing entities are one of the database designs we are facing in many applications.
An employee will have direct reporting manager who is also an employee.
Each person has mother who also has her own mother and so on.
We can efficiently store those type of hierarchical data in RDBMS.
This type of relationship is called as recursive association which connect to the same class type.
Well, let's have some hands-on work now.

Following SQL code will create the employee table with self-referencing key.
CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `managerid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`managerid`),
  CONSTRAINT `fk` FOREIGN KEY (`managerid`) REFERENCES `employee` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

After creating the table, we will enter the dummy data rows to test.
INSERT INTO employee(name) values('CEO');
INSERT INTO employee(name, managerid) values('Mgr1', 1);
INSERT INTO employee(name, managerid) values('Mgr2', 1);
INSERT INTO employee(name, managerid) values('TL1', 2);
INSERT INTO employee(name, managerid) values('TL2', 3);

Now, we can easily retrieve the values from our self-referencing table using following SQL join query.
SELECT EMP.id, EMP.name AS "Employee", MGR.name AS "Manager"
FROM employee EMP LEFT OUTER JOIN employee MGR
ON EMP.managerID = MGR.id
ORDER BY EMP.managerid, EMP.name

Output
Id Employee Manager
1 CEO (null)
2 Mgr1 CEO
3 Mgr2 CEO
4 TL1 Mgr1
5 TL2 Mgr2

4 comments:

Image compression using C#

Sometimes, we need to compress the image files while maintaining the image quality. This can be achieved by the following C# implementation....