Recursive Relationship

Spyder_Snyper · #1 (**permalink**) 01-16-05, 15:29

Alright... I'm making a very simple database that'll keep track of employess and who their supervisors are. Since Supervisors are employees as well, they all fall into the same table...

Now, the table has 3 main rows, EmployeeID, Name, and SupID.
EmployeeID is an auto-incrementing Integer starting at 0
SupID is a foreign key from the EmployeeID. Since all Sups are also employess, it becomes slightly recursive.

Now the logic behind setting it up is not a problem whatsoever. The problem is, how do I make the relationship?

r937 · #2 (**permalink**) 01-16-05, 16:42

make the relationship?

Code:

create table Employees
( EmployeeID integer not null primary key auto_increment
, Name varchar(99) not null
, SupID integer null
, foreign key ( SupID ) references Employees ( EmployeeID )
)

note that mysql does not support foreign keys unless you use innodb tables

the_spas · #3 (**permalink**) 01-17-05, 10:52

hi if not ever emp has a sup o even has 2 of em you should handle this in two tables

emp
- id (primary auto inc)
- name
- position ......

emp_sup
- emp (emp id) (key)
- sup (emp id) (key)

so there is the possibility to add two or more rows for one employee without saving the employe data twice

to select an emps sups just do this
SELECT fields FROM emp, emp_sup WHERE emp.id = emp_sup.sup AND emp_sup.emp = user_to_seach

to select a sups emps just do this
SELECT fields FROM emp, emp_sup WHERE emp.id = emp_sup.emp AND emp_sup.sup = supervisor

hope this usefull a bit

Internet Services

Web Development

Digital Marketing

Consumer Tech