Recursive Relationships in Databases

One of the tricky issues in entity relationship model is the recursive relationships. Sometimes an entity needs to refer to itself. For example, think of an organisational chart for the roles of employees in a company; each employee is managed by a manager, each manager manages 0 or more employees. The records of all the employees are kept in a table called tblEmployee:

Check Alter Table syntax on MSDN.

In MySQL, you can do the same, the table can refer to itself:

http://stackoverflow.com/questions/1969338/mysql-foreign-keys-recursive

..as long as the engine is InnoDB.

Also, triggers can be used to achieve the same result.

Leave a Comment