Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Question Unanswered: Foreign key usage in a strange relationship

    Hi all,

    I saw the table arrangement at the bottom in some table pairs in a MySQL 4.xx (InnoDB) dataset. I was wondering if you could help me to understand why the table relationships have been made like that.

    The relationship between each of those table pairs was made by setting the primary key of one in the other one. In the other word, in addition to placing the PK of the Parent Table in the Child Table as FK, there is the PK of the Child Table in the Parent Table as FK.

    All of those 3 pairs of tables have One-to-one relationship, one end is Mandatory the other end is Not mandatory.

    The following is a sample of one of those pairs:

    Tb_A(A_id [pk], B_id [fk], A_date, A_type)
    Tb_B(B_id [pk], A_id [fk], B_date, B_type)
    Relationships between A and B are 1:1
    The A side of relationship is mandatory and the B side is Not mandatory.

    If we presume the table A is the parent and B is the child, would you please tell me:

    1- Why the PK of A was used in B as FK?
    2- If it is an acceptable practice, is this way of FK usage depend on how we use ON DELET [ CASCADE and NO ACTION ]?

    Any comment and help would be appreciated.

    Thanks
    Sam

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    1. because there are two relationships, A-->B and A<--B

    2. yes, it's acceptable, and no, the ON DELETE clause is optional

    here's an example:

    every employee belongs to an office
    every office has a manager (who is an employee)
    Code:
    create table offices
    ( id         integer not null primary key
    , fk_manager integer null
    );
    
    create table employees
    ( id         integer not null primary key 
    , fk_office  integer not null
    , foreign key (fk_office) references offices (id)
    );
    
    /* load data */
    
    alter table offices
    add foreign key (fk_manager) references employees (id)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    Hi Rudy,

    Thank you very much for your explanations. I'm lucky that you saw my post.

    Now I know that this kind of relationship is acceptable. But I am still not sure about the rational behind this kind of relationships.

    For example:

    Code:
    If we have 4 offices (D can be vacant) and 3 employees 
    (one of them is a manager), what is the benefit of 2 
    relationships in such a case?
    
    This example is very similar to those tables I mentioned 
    before. (One-to-one relationship, each employee must 
    have an office [mandatory side], but offices can be 
    vacant [not mandatory])
    
    The only difference is that in those tables "there is no 
    manager amonge the employees". In those tables, as 
    far as I understood, the entries in the 2nd tables have 
    similar 'rank', 'condition', 'type'...
    Does this 2 relationship kind still applicable to them?
    
       OFFICES
    +-------------+
    | O_id | E_id |
    +-------------+
    |  A   |  1   |
    |-------------|
    |  B   |  2   |
    |-------------|
    |  C   |  3   |
    |-------------|
    |  D   |      |
    +-------------+
    
    USING ONE RELATIONSHIP (O -> E)
    
       EMPLOYEES
    +---------------+
    | E_id | E_name |
    +---------------+
    |  1   |  John  |
    |---------------|
    |  2   | Marry  |
    |---------------|
    |  3   |  Mike  |
    +---------------+
    
    USING TWO RELATIONSHIPS (O -> E) (O <- E)
    
       EMPLOYEES
    +---------------------+
    | E_id | E_name |O_id |
    +---------------------+
    |  1   |  John  |  A  |
    |---------------------|
    |  2   | Marry  |  B  |
    |---------------------|
    |  3   |  Mike  |  C  |
    +---------------------+
    Thanks again
    Sam

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i don't understand what you're asking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2003
    Posts
    14
    I am sorry if my question was not clear enough.

    I just wanted to know when one relationship provides enough linkage between OFFICES and
    EMPLOYESS table, what is the reason of adding the 2nd relationship?
    Code:
    O_id = OFFICES PK
    E_id = EMPLOYEES PK
    
    USING ONE RELATIONSHIP (O -> E)
       OFFICES
    +-------------+
    | O_id | E_id |
    +-------------+
    |  A   |  1   |
    |-------------|
    |  B   |  2   |
    |-------------|
    |  C   |  3   |
    |-------------|
    |  D   |      |
    +-------------+
    
       EMPLOYEES
    +---------------+
    | E_id | E_name |
    +---------------+
    |  1   |  John  |
    |---------------|
    |  2   | Marry  |
    |---------------|
    |  3   |  Mike  |
    +---------------+
    why we may need the 2nd relationship by adding O_id into EMPLOYEES as FK?
    (let's assume all employees are the same and there is no manager among them)

    Code:
    USING TWO RELATIONSHIPS (O -> E) (O <- E)
    
       OFFICES
    +-------------+
    | O_id | E_id |
    +-------------+
    |  A   |  1   |
    |-------------|
    |  B   |  2   |
    |-------------|
    |  C   |  3   |
    |-------------|
    |  D   |      |
    +-------------+
    
       EMPLOYEES
    +---------------------+
    | E_id | E_name |O_id |
    +---------------------+
    |  1   |  John  |  A  |
    |---------------------|
    |  2   | Marry  |  B  |
    |---------------------|
    |  3   |  Mike  |  C  |
    +---------------------+

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there are two different relationships!

    offices:
    downtown
    uptown
    outtatown

    employees:
    tom
    dick
    harry
    curly
    larry
    moe
    fred

    employee-offices:
    tom downtown
    dick downtown
    harry downtown
    curly outtatown
    larry outtatown
    moe outtatown
    fred uptown

    office managers
    downtown dick
    uptown NULL
    outtatown fred

    note that fred is located in the uptown office but he's the manager of the outtatown office

    the uptown office doesn't have a manager
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2003
    Posts
    14
    Thanks again Rudy for your nice example and explanations.
    You have answer of all questions which contains keyword 'database' [corrected] AND 'SQL'

    Regards
    Sam
    Last edited by SamKlinsmann; 12-29-04 at 00:15.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the kind words, but that's not true

    i totally avoid questions that deal with database administration (installation, backups, restores, replication, throttling, striping, allocating tablespaces, connection strings, stuff like that)

    i am so not a DBA
    Last edited by r937; 12-28-04 at 01:51.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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