I am having hard time with one attribute in the following schema:
I am talking about managerID which is a part of Department table right now. Normally there should be Foreign key relationship between managerID and userID in CMSUser table. But if I set up this relationship, I am not able to fill tables. If I fill CMSUser first, I do need a departmentCode. Then again if I fill the Department first, what do I put for managerID of CMSUser is empty.
I would appreciate if someone gives me a hint on how I can resolve this.
If you say: First there were users, and then they created departments...
(thus you should use NULL for departmentID in the beginning)
Or you could say: First there were Departments and they started employing users.
IMHO the first way to see it is more logical. Because to build a department you need people. So there can't be departments without people.
Have you thought about what happens if a user is part of 2 or more departments? This n-m relationship can be worked around be adding an extra entitiy taskt that would give you |user| 1 - n |tasks| n - 1 |department|