Wednesday, 4 September 2013

A record self referencing in mysql

A record self referencing in mysql

I've got a self referencing table in mySql, a table of managers and
employees.
CREATE TABLE `employee` (
`employee_id` BIGINT(10) NOT NULL AUTO_INCREMENT,
`firstname` VARCHAR(50) NULL DEFAULT NULL,
`lastname` VARCHAR(50) NULL DEFAULT NULL,
`manager_id` BIGINT(20) NULL DEFAULT NULL,
PRIMARY KEY (`employee_id`),
CONSTRAINT `FK_MANAGER` FOREIGN KEY (`manager_id`) REFERENCES `employee`
(`employee_id`)
)
I want to know if it's conceptual correct that a record references
himself. I explain better: some employees has relationships with some
manager, so for example employees 3,5,7 have relationships with manager 1,
but I'd like to create a list to show all record related to manager 1 and
also manager 1. So I've tried to make manager 1 have relationships with
himself, so manager 1 refers to manager 1.
I've got no errors, but wanna know if this is a correct way to implement
this relationship or if I will have problems in future.
Thanks

No comments:

Post a Comment