Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Posts
    370

    Unhappy Loop in relatinships!

    Hi,
    I have 2 entities named by:"Department" and "Professor" which are related in this manner:
    Professor(ProfID,....,DeptID)-----------Department(DeptID,....,ManagerID)

    which managerID is a professors id in this system.OK?so I have a FK from Professor to Deptartment and another from Department to Professor.
    So I want to insert records into this two tables but any one could not be first because its FK is there!
    What should I do? Is there any problem with this design?
    -any solution is appreciated

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Use a deferred foreign key constraint on one (or both) of the tables (if your DBMS supports them). A deferred constraint is not validated until the end of the transaction (i.e. COMMIT statement):

    insert into Professor(ProfID,....,DeptID) values (123, ..., 400);
    insert into Department(DeptID,....,ManagerID) values (400, ..., 123);
    commit;

  3. #3
    Join Date
    Mar 2004
    Posts
    370
    Thanx Andrew for your quick reply,
    now consider I have many records in both tables and want to truncate both tables in Oracle so should I disable FKs? How can I do this in Oracle?
    and if some DBMS unlike Oracle does not support deferable FKs what should I do?
    -thanks again

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by Ach
    and if some DBMS unlike Oracle does not support deferable FKs what should I do?
    Some of the tricks that you might consider:
    - make department.manager_id nullable; you'll then insert a department record with manager_id set to null, insert a professor record, then update the department record to set manager_id to whatever;
    - if any professor can only be a manager of one department at a time you can eliminate department.manager_id altogether and add an attribute to the professor record indicating that the professor is a manager of that department;
    - create a relationship table so that there are no foreign keys between department and professor, only between the relationship table and the other two.

  5. #5
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    There's one more:

    Professor(ProfID,...)

    ProfessorDepartment(ProfID(FK),DeptID(FK),ManagerY N)

    Department(DeptID,...)

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    I agree with certus's model.

    Whenever you have such a problem, it points to an area where the design can be improved. In plain English, the design is wrong.

    I do not have these problems because my design takes care of them and I do not have to resort to arcane code and in-depth knowledge of various DBMS engines to solve self-created problems.

    Ravi

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    But what if the manager doesn't have to be in the same department?

    There is nothing instrinsically "wrong" with the original table design - it is just different.

  8. #8
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    Ok, Let's see. Here is what I understand to be the requirements,

    1. Need to keep professor info.
    2. Need to keep dept info.
    3. Need to associate a prof with a dept. (Reasonable to assume that a professor will not move from one dept to another.)
    4. Need to have managers for dept.

    Then the model as defined by certus with some simple modification works.

    1. Professor (prof_id, name, ..., dept_id)
    2. Dept (dept_id, name, location, ...)
    3. Dept_Manager(dept_id, prof_id, start_dt, end_dt).

    This generally avoids all the problems that we saw and whose solution depended upon the DBMS we are using.

    I am generally uncomfortable with circular relationships.

    Ravi

  9. #9
    Join Date
    Mar 2004
    Posts
    370
    Hi,
    Let me explain the model more precisely:There are two relationships in fact:
    1. "Membership" between prof and dept that is N..1 and I designed this by putting the DeptID in Prof table.
    2."Management" again between prof and dept which is 1..1 and departments participation is mandatory here.So I designed this one by putting the ManagerID (profID) in Dept table. Note that one dept has just ONE manager.

    So the initial design is not wrong (You can refer to accepted references such as Date and Elmaseri and...).IMO it should be a nice alternative than designing this model as a M..N relationship by using a relation_table.

    -thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •