Re: A foreign key on a self-referring table

From: mAsterdam <mAsterdam_at_vrijdag.org>
Date: Sat, 06 Mar 2004 19:28:36 +0100
Message-ID: <404a1860$0$573$e4fe514c@news.xs4all.nl>

Before I start nitpicking, let me compliment you on your very informative answer. I learned about databases when nobody claimed relational databases were available. A change of job made me need to revive and refresh my knowledge. One way of doing that is reading this newsgroup.

Valuable posts like yours make that a worthwhile effort. Yet I will snip most of it. Just take that as an agreement of sorts.

Gianluca Hotz wrote:

> ...Before continuing, let's assume our table has some more
> rows as in the following example:
>
> +------+----------+----------+
> | EMP# | EMP_NAME | MGR_EMP# |
> +------+----------+----------+
> | 1 | Bob | |
> | 2 | Eric | 1 |
> | 3 | Paul | 1 |
> | 4 | Andrea | 3 |
> | 5 | Joe | 2 |
> | 6 | Bill | 3 |
> | 7 | Mike | 2 |
> +------+----------+----------+

Let's rewrite this 'picture' as:

+----------------------------+
| Employee                   |
+-----------------+----------+

| EMP# | EMP_NAME | MGR_EMP# |
+======+----------+----------+

|    1 |      Bob |          |
|    2 |     Eric |        1 |
|    3 |     Paul |        1 |
|    4 |   Andrea |        3 |
|    5 |      Joe |        2 |
|    6 |     Bill |        3 |
|    7 |     Mike |        2 |

+------+----------+----------+

as to not forget the subjectmatter.

Aside: a name also covering the third column would be "EmployeeReporting", which would alert us to the problem you are discussing next, but as one can't capture all meaning in a name that would be too much for now anyway.

> ...So let's try to model the hierarchy differently, the
> first table is named Employee
>
> +------+----------+
> | EMP# | EMP_NAME |
> +------+----------+
> | 1 | Bob |
> | 2 | Eric |
> | 3 | Paul |
> | 4 | Andrea |
> | 5 | Joe |
> | 6 | Bill |
> | 7 | Mike |
> +------+----------+
>
> and the predicate is
>
> "Employee identified by number (EMP#) has name (EMP_NAME)"

You can imagine my amendment here, for sure.

> ...the second one is named OrganizationChart
>
> +------+----------+
> | EMP# | MGR_EMP# |
> +------+----------+
> | 2 | 1 |
> | 3 | 1 |
> | 4 | 3 |
> | 5 | 2 |
> | 6 | 3 |
> | 7 | 2 |
> +------+----------+
>
> and the predicate is
>
> "Employee with number (EMP#) is managed by employee
> with number (MGR_EMP#)"

But here I have difficulty amending.

+------------------+

| OrganizationChart|
+------+-----------+
| EMP# | MGR_EMP# |
+======+===========+

|    2 |         1 |
|    3 |         1 |
|    4 |         3 |
|    5 |         2 |
|    6 |         3 |
|    7 |         2 |

+------+-----------+

First of all I had to widen tha 'picture' ;-) But, secondly, this amended representation suggests that somehow
an 'OrganizationChart' is implied by,
for instance, OrganizationChart(EMP#: 4, MGR_EMP#: 3) which isn't true.

So - should I refrain from amending?
I don't think the problem is in the amendment, the amendment just exposes it.

Second try:

+------------------+
| Reports_To       |

+------+-----------+
| EMP# | MGR_EMP# |
+======+===========+

|    2 |         1 |
|    3 |         1 |
|    4 |         3 |
|    5 |         2 |
|    6 |         3 |
|    7 |         2 |

+------+-----------+

, the predicate being

"Employee with number (EMP#) reports to employee with number (MGR_EMP#)"

Aside: There is a reason why I still would not have this in a real database:
What if a managerspositon is vacant? But that may be another topic.

> ...Here is a list of books you may want to consider reading
> if you want to have more details:
>
> "An Introduction to Database Systems 8th edition"
> by Chris Date

My 2nd editon does't cut it anymore? :-(

> ..."Temporal data and the relational model"
> by Chris Date, Hugh Darwen and Nikos Lorentzos

I heartily second this advice. Received on Sat Mar 06 2004 - 12:28:36 CST