Hello,

I am designing a table where employees can also be managers, giving a recursive relationship. Obviously managers don't need a manager, but all employees have one. I am thinking about creating a table for managers.

The Employees table will look like this
CREATE TABLE Employee
(EmployeeID MEDIUMINT NOT NULL,
EmployeeFirstName VARCHAR(20) NOT NULL,
EmployeeLastName VARCHAR(20) NOT NULL,
CONSTRAINT Employee_PK PRIMARY KEY(EmployeeID)
)
ENGINE=InnoDB;

I am not sure exactly how to create a Manager's table, but was thinking two columns as follows. Then creating referential integrity checks between both columns and the Employee(EmployeeID) column.

CREATE TABLE ManagerEmployee
(ManagerEmployeeID MEDIUMINT NOT NULL,
EmployeeID MEDIUMINT,
CONSTRAINT Employee_PK PRIMARY KEY (EmployeeID, ManagerEmployeeID));

Not sure if this is the best way to do it though. No historical data about past managers will be needed. I could just leave the ManagerEmployeeID column in the Employee table and allow NULL values, which would take care of those without managers. I am interested in how others would do this and why.

Really? you work in a two-tiered company? That is a pretty flat organizational structure...
But really, you do not need a separate table. Just add a ManagerID to the Employees table as a foreign key to its own EmployeeID.

Thanks for that Blindman. This is an assessment I am doing rather than a real company. I am interested in the best way to do things. I have tried your suggestion and added the column to the Employee table, however now I cannot create the table. MySQL gives me an error saying it cannot create the table. I have checked and checked my code, but cannot see a mistake. All the right columns are there.

I have also tried creating the table without the Foreign Key constraints and using an Alter table statement, but get the same result. I get error 1005 cannot create table

Can you see any reason?

CREATE TABLE Employee
(EmployeeID MEDIUMINT NOT NULL,
EmployeeFirstName VARCHAR(20) NOT NULL,
EmployeeLastName VARCHAR(20) NOT NULL,
ManagerEmployeeID MEDIUMINT ,

CONSTRAINT Employee_PK PRIMARY KEY(EmployeeID),
CONSTRAINT Manager_Employee_FK
FOREIGN KEY (ManagerEmployeeID)
REFERENCES Employee (EmployeeID)
)
ENGINE=InnoDB;

Moving this thread to the MySQL forum...

All, have resolved this by dropping all tables and starting again, although the Foreign Key was only referencing itself, MySQL did not seem to like my adding a Foreign Key afer adding other tables.

Thanks