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:

-- T-SQL for MS SQL Server 2005
CREATE TABLE tblEmployee (
  employeeId INT NOT NULL,
  empName VARCHAR(50) NOT NULL,
  empSurname VARCHAR(50) NOT NULL,
  managerId INT NULL,
  PRIMARY KEY (employeeId)
)
GO

CREATE INDEX idxManager
ON tblEmployee (managerId)
GO

ALTER TABLE tblEmployee
ADD CONSTRAINT fkManager
FOREIGN KEY (managerId)
REFERENCES tblEmployee (employeeId)
GO

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.

Tags: , ,
Categories: