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.
