{"id":352,"date":"2011-12-05T00:06:52","date_gmt":"2011-12-05T00:06:52","guid":{"rendered":"http:\/\/www.muratyaman.co.uk\/wp\/?p=352"},"modified":"2020-04-01T13:04:11","modified_gmt":"2020-04-01T12:04:11","slug":"recursive-relationships-in-databases","status":"publish","type":"post","link":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/2011\/12\/recursive-relationships-in-databases\/","title":{"rendered":"Recursive Relationships in Databases"},"content":{"rendered":"<p>One of the tricky issues in <a href=\"http:\/\/en.wikipedia.org\/wiki\/Entity-relationship_model\" title=\"Entity relationship model\">entity relationship model<\/a> is the recursive relationships. Sometimes an entity needs to refer to itself. For example, think of an <a href=\"http:\/\/en.wikipedia.org\/wiki\/Organisational_chart\" title=\"Organisational chart\">organisational chart<\/a> 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:<\/p>\n<pre lang=\"sql\">\r\n-- T-SQL for MS SQL Server 2005\r\nCREATE TABLE tblEmployee (\r\n  employeeId INT NOT NULL,\r\n  empName VARCHAR(50) NOT NULL,\r\n  empSurname VARCHAR(50) NOT NULL,\r\n  managerId INT NULL,\r\n  PRIMARY KEY (employeeId)\r\n)\r\nGO\r\n\r\nCREATE INDEX idxManager\r\nON tblEmployee (managerId)\r\nGO\r\n\r\nALTER TABLE tblEmployee\r\nADD CONSTRAINT fkManager\r\nFOREIGN KEY (managerId)\r\nREFERENCES tblEmployee (employeeId)\r\nGO\r\n<\/pre>\n<p>Check <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms190273(v=SQL.90).aspx\">Alter Table syntax on MSDN<\/a>.<\/p>\n<p>In MySQL, you can do the same, the table can refer to itself:<\/p>\n<p><a href=\"http:\/\/stackoverflow.com\/questions\/1969338\/mysql-foreign-keys-recursive\">http:\/\/stackoverflow.com\/questions\/1969338\/mysql-foreign-keys-recursive<\/a><\/p>\n<p>..as long as the engine is InnoDB.<\/p>\n<p>Also, triggers can be used to achieve the same result.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the tricky issues in entity relationship model is the recursive relationships. Sometimes an entity needs to refer to itself.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[56,57,33],"class_list":["post-352","post","type-post","status-publish","format-standard","hentry","category-technology","tag-ms-sql-server","tag-mysql","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/352","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=352"}],"version-history":[{"count":7,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/352\/revisions"}],"predecessor-version":[{"id":998,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/posts\/352\/revisions\/998"}],"wp:attachment":[{"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=352"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=352"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.muratyaman.co.uk\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=352"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}