Foreign key usage in a strange relationship

SamKlinsmann · #1 (**permalink**) 12-27-04, 01:27

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

r937 · #2 (**permalink**) 12-27-04, 10:18

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)

SamKlinsmann · #3 (**permalink**) 12-27-04, 13:18

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

r937 · #4 (**permalink**) 12-27-04, 15:01

i don't understand what you're asking

SamKlinsmann · #5 (**permalink**) 12-27-04, 18:55

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  |
+---------------------+

r937 · #6 (**permalink**) 12-27-04, 19:56

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

SamKlinsmann · #7 (**permalink**) 12-28-04, 00:11

Thanks again Rudy for your nice example and explanations.
You have answer of all questions which contains keyword 'database'

[corrected] AND 'SQL'

Regards
Sam

r937 · #8 (**permalink**) 12-28-04, 00:46

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

Internet Services

Web Development

Digital Marketing

Consumer Tech