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
employee
table has just adept_id
and nodirector_id
, how would you ever figure out whether an employee with adept_id
of 4 was in the department name4, name5, or name6?employee
anddepartment
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?employee
table that has adept_id
column but there is no existingdepartment
table where this value can be looked up? So, currently, you can't determine the name of the department an employee is in?