Home » Developer & Programmer » Designer » A logical design and its problems?!
A logical design and its problems?! [message #90695] Wed, 11 August 2004 00:03 Go to next message
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 Go to previous message
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
Previous Topic: CDG-00071 Error
Next Topic: Storage space consumed by NUMBER datatype
Goto Forum:
  


Current Time: Fri Mar 29 07:35:10 CDT 2024