| A logical design and its problems?! [message #90695] |
Wed, 11 August 2004 00:03  |
Ach
Messages: 26
Registered: April 2004
|
Junior Member |
|
|
I had a simple ERD which had an entity named Department and another one named Professor.They related in this way:
Dept<--1--membership--N--[>]Prof
Dept<--1--management--1--[>]Prof (which the partenership of Dept is substantial in this relationship)
I did a logical design like this:
1.Department(DeptID,....,ManagerID) which DeptID is PK and ManagerId is FK to ProfID of Professor relation.
2.Professor(ProfID,....,DeptID) which ProfID is PK and DeptID is FK to DeptID of Department relation.
Is it OK till now? but when I decided to insert real data in one of these tables it could not because of Referential Integrity that violated (FK to a null value).
So What is the problem?Is it any mistake in my design or other things?Is there any solution for similar cases?
-Thanks in advance
|
|
|
|
| Re: A logical design and its problems?! [message #90703 is a reply to message #90695] |
Thu, 19 August 2004 05:09  |
Himanshu
Messages: 457
Registered: December 2001
|
Senior Member |
|
|
Hi,
Indeed you design is incorrect.
Make a Separate Table(DEPT) for Department which will serve as the Master Table.
Now you can make 1 detail table say "Managers" for Managerid which will be related to Dept i.e. Managerid is PK and Depid is FK.
Make another table "Professors" which will have Profid as PK,Depid & Managerid as FK.
This is the case when a Manager cannot be a Manager of 2 departments and same Professor cannot work in same Dept and/or under same Manager.
If you wish to have otherwise then you will have to make composite primary keys like in Manager table you will have to have Managerid & Deptid as PK while inf Professsors table you will require Managerid,Profid and Deptid all as a Primary key.
HTH
Regards
Himanshu
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
