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
employeetable has just a
director_id, how would you ever figure out whether an employee with a
dept_idof 4 was in the department name4, name5, or name6?
departmenttables 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?
employeetable that has a
dept_idcolumn but there is no existing
departmenttable where this value can be looked up? So, currently, you can't determine the name of the department an employee is in?