0

How should I define a database table for my company's departments where we have a set of duplicate department numbers used by an upper management tier. 

  dept_id   dept_name   manager_id  director_id   vp_id   description     mission_statement  over_view    web_url
      1       name1      10            20          30       ...
      2       name2      11            21          30
      3       name3      12            22          31
      4       name4      13            23          32
      4       name5      13            24          32
      4       name6      13            25          32

The column which makes the row unique is the director_id. But I really only want to use the dept_id as the pk because I'm using the department data for a web application. I have the Employee table with a column named dept_id. When I display an employee on a webpage, I dynamically create a link to the department's splash page containing the above information. But if I list director 24 I need URL parameters that take me to the department named name5

blahblah.com/view_dept.jsp?dept_id=4

I know the obvious is to composite key with director_id and have the URL be

blahblah.com/view_dept.jsp?dept_id=4&director_id=24

but when I list a regular employee I don't have the director_id. I suppose I could get it in the query, but it may not be as straight forward as it would be if HR just used unique ID's for all departments. Getting HR to do a reorg is probably out of the question, but it would be nice to know that that is the most recommended answer.

I'd like to hear suggestions and best alternatives to the reorg

Improve this question
7
  • I'm not sure that I follow. If the employee table has just a dept_id and no director_id, how would you ever figure out whether an employee with a dept_id of 4 was in the department name4, name5, or name6?
    – Justin Cave
    Jan 29, 2014 at 17:53
  • right. this is the dilema. its only the directors which are in a dept with a duplicate dept number but unique name. If I only use dept_id as the dept table's PK, I can't even load the duplicate depts The employee table has a supervisor_id so I could through a connect_by determine the chain of command.
    – jeff
    Jan 29, 2014 at 18:02
  • Are you replicating the employee and department tables from the same system (or two different systems) into a new system that you're building? Or are you re-designing an existing system? How, exactly, would you figure out which department an employee is in using the existing data model? Is that how the existing system really figures out which department an employee is in?
    – Justin Cave
    Jan 29, 2014 at 18:10
  • Employee table is db View I get from our IT group. Departments are in an Excel document, thus the non-normalize dataset.
    – jeff
    Jan 29, 2014 at 19:33
  • So there is a system with an existing employee table that has a dept_id column but there is no existing department table where this value can be looked up? So, currently, you can't determine the name of the department an employee is in?
    – Justin Cave
    Jan 29, 2014 at 20:31

1 Answer 1

Reset to default
1

The PK of your department table is not dept_id. The column dept_id is an attribute of a manager table that you have not defined, since it is functionally dependent on the manager_id.

The PK of your department table is dept_name.

Primary keys don't have to be integers.

Note: If your HR department creates departments with the same name, that is going to land you back in hot water. You might need to create a composite primary key of dept_id and dept_name. This could still lead to trouble if your HR department can't be trained to keep department names unique within managers. Excel is not a good data source for being able to enforce constraints.

Improve this answer
2
  • interesting. If I didn't have duplicate dept numbers would you still suggest a separate Managers Table? Would you also suggest breaking out a Director Table and VP Table? Even if I had separate tables how I can determine a department NAME that a regular employee is in? In the employee table their dept number is listed. I want to join to some table(s) get the department name. I'd have to determine the employees Manager, then join manager_id to department table. If dept number happens to be 4 (the duplicate), I'm screwed. If HR didn't have duplicate dept numbers, the query would be trivial.
    – jeff
    Jan 30, 2014 at 14:07
  • @jeff - The issue is about normalization based on functional dependencies. For a logical model you probably would have separate director and VP tables. That doesn't mean your physical model needs them. It may be, based on what you say about employee-department mapping that the PK of department is a composite of dept_id and manager_id. That would also work and it might solve your problem of how to join employee in. I assume your employee table has a manager_id and a dept_id so that would solve your problem.
    – Joel Brown
    Jan 30, 2014 at 22:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.