Table design for departments with duplicate department numbers

database-designoracleprimary-key

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

Best Answer

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.

Related Question