A database design question

I require an employee table with this structure :

Employee (employeeid(int),supervisorids())

The supervisorid is nothing but the employeeid of the supervisor

One employee can have 0 or many supervisors (please note the 0). If he has a supervisor, then I also need to have a constraint that such employee should exist in the table...that is it would be a internal foreign key referencing to a particular em开发者_运维百科ployeeid.

How should I design my table(s) to store this info ? If I make the supervisorids column a varchar (with delimited values of multiple supervisisor's employeeids), I am unable to have a foreign key to check if that employee exists.

Thanks!

EDIT : Sorry,f orgot to mention, "Order" matters here. An employee may have a list of supervisors, but they are in an order - there is a primary supervisor, if he is absent then we go for second supervisor, then third and so on.

Why wouldn't you do something like this?

Employees table

------------------------------------------
|  id  |   name  |   address  |  etc...  |
|------|---------|------------|----------|
|   0  |  Mike   |  blah      |   ...    |
|------|---------|------------|----------|
|   1  |  John   |  blah      |   ...    |
|------|---------|------------|----------|
| ...  |  ...    |   ...      |   ...    |
------------------------------------------

And then supervisors table. Both columns can foreign key in the employees table.

------------------------------------------
|  id  |  employee_id  |  supervisor_id  |
|------|---------------|-----------------|
|  0   |     1         |       0         |
|------|---------------|-----------------|
|  1   |     1         |       2         |  <---- Employee 1 is managed by 0, 2, and n
|------|---------------|-----------------|
|  0   |     0         |       2         |  <----- Employee 0 is managed by 2
|------|---------------|-----------------|
|  0   |     1         |       n         |
------------------------------------------

etc...

This looks to be a case of many to many relation between records of the same table (many employees for one supervisor and many supervisors for 1 employee) . You need to add a separate assoc table for this case (emp_id, upervisor_id). Both thee columns will define foreign key constraints to the employee table

You want something similar to what Fazal recommended:

Your employee table, and a separate assoc table with (employeeid, supervisorid, order). supervisorid and employeeid are foreign keys to the employee table, and the ordering key is either unique globally or is unique per employeeid. Your database may provide fancier ways to do ordering like this.

When you want the supervisors, you query for the employeeid in your separate table, sorted by the order key.