Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007
    Posts
    19

    Unanswered: Extra table or not

    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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by beechfielder
    Obviously managers don't need a manager, but all employees have one.
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Oct 2007
    Posts
    19
    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;

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Moving this thread to the MySQL forum...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2007
    Posts
    19

    Solved

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •