Database design question

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
nsabina
Forum Newbie
Posts: 1
Joined: Wed Feb 18, 2004 12:43 pm

Database design question

Post 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
microthick
Forum Regular
Posts: 543
Joined: Wed Sep 24, 2003 2:15 pm
Location: Vancouver, BC

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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|
Post Reply