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
I know the obvious is to composite key with
director_id and have the URL be
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