Re: A foreign key on a self-referring table
Date: Sat, 06 Mar 2004 19:28:36 +0100
Message-ID: <404a1860$0$573$e4fe514c_at_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 - 19:28:36 CET