Page 1 of 1

Database design question

Posted: Wed Feb 18, 2004 12:43 pm
by nsabina
I am having hard time with one attribute in the following schema:

Image

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.

Thank You,
Sabina

Posted: Thu Feb 19, 2004 3:48 am
by microthick
Populate the departments table first, but leave the managers null.

Then, populate the users table.

Then go back and add the manager to each department.

Posted: Thu Feb 19, 2004 6:40 am
by timvw
It all comes down on the way you see it...

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|