SQL [Normalization] Question

gordonisgod

I'm creating an employee database. An employee can be affiliated with a department and subdepartment. Right now I have the following tables and I'm wondering if there is a better way.

EMPLOYEE_TABLE

EmployeeID
LastName
FirstName
DeptID (foreign key to DeptLookup)
SubDeptID (foreign key to DeptLookup)

DEPARTMENT_TABLE

DeptID
DepartmentName

SUBDEPARTMENT_TABLE

SubDeptID
SubDepartmentName

DEPTLOOKUP_TABLE

DeptID
SubDeptID

TIA
gg

ultraslacker

Will an employee ever be associated with a dept different from the dept of his/her sub dept?

I would place dept in one table and use a reflexive key for sub dept's.

gordonisgod

I don't think the recursive option will worked. It seemed like a great idea at first, but one employee can belong to one or more subdepartments.

Any other ideas?

gordonisgod

My original diagram won't work either.

So, I think I need to do this:

EMPLOYEE_TABLE

EmployeeID
Lastname
Firstname

DEPARTMENT_TABLE

DeptID
DeptName
DeptParentID (recursive with DeptID)

EMPLOYEE_DEPT

EmployeeID
DeptID

or I can cheat and do.....

EMPLOYEE_TABLE

EmployeeID
Lastname
Firstname
DeptID(1)
DeptID(2)
DeptID(3)

ultraslacker

Yes, the first option - it is normalized and meets your requirements.

tomhath

The EMPLOYEE_DEPT intersection table will work. But is there a good reason to store the Department and the SubDepartment in the same table? They seem like different entities to me, which would imply different tables. But that's a fairly common design and not wrong either, especially if there can be multiple generations (Sub-Sub-Departments, etc).