Re: A foreign key on a self-referring table

"mAsterdam" <mAsterdam_at_vrijdag.org> wrote in message news:404a3671$0$561$e4fe514c_at_news.xs4all.nl...
> daveb wrote:
>
> > "mAsterdam" <mAsterdam_at_vrijdag.org> wrote in message
> > news:404a1860$0$573$e4fe514c_at_news.xs4all.nl...
> >
> >
> >>+------------------+
> >>| 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.
> >
> > Well, it changes the functional dependencies; we now find that
employees
> > report to a department, not to a manager, and the OrganizationChart
relates
> > departments, not employees.
>
> That is not enough.
> An employee should be somehow associated with a department.
> A deparment should be able but not obliged to be associated with
> an employee as its manager. I still don't see "OrganizationChart" as a
> good name for its association with other departments.
>

I wasn't trying to give a complete design...that's not possible without knowing the actual requirements. For example, can an employee be associated with more than one department at a time? Can an employee not be in any department (e.g. a consultant maybe)? Can a department have co-managers? Can an employee manage multiple departments? etc. etc. No right or wrong, it depends on what the customer's requirements are. Here's one that embodies what appear to be your assumptions:

create table departments (
  dept# number not null primary key,
  dept_name varchar(30),
)
create table org_chart (
  dept# number not null primary key references departments,   reports_to number references departments   /* excercise for the reader to avoid null for topmost */ )
create table employees (
  emp# number not null primary key,
  emp_name varchar(30) not null,
  dept# number not null references departments   constraint unique (dept#, emp#)
)
create table managers (
  dept# number not null primary key references departments,   emp# number not null references employees,   /* restrict employees to managing only one department at a time */   constraint unique (emp#),
  /* employee must belong to department they manage */   constraint foreign key (dept#, emp#) references employees (dept#, emp#) )

Modify as necessary according to your real requirements. Received on Sun Mar 07 2004 - 02:12:20 CET