Re: A foreign key on a self-referring table

daveb wrote:

> I wasn't trying to give a complete design...that's not possible without
> knowing the actual requirements.

AFAIK the actual requirements are what
Andrea Laforgia wrote:
> ... some tables have foreign
> keys referring to themselves.
> ...
> (That's an example, of course, it's not my real case).
> ...
> I wish to know whether it is
> acceptable, in general, or they are just violating some database
> design principle.

Gianluca Hotz wrote:

 > So to continue with the discussion I would prefer to take a more  > common example like the classical one about employees.

You wrote:
> For example, can an employee be associated
> with more than one department at a time?

I don't know. Whithin the constraints of the actual requirements it is ok for you to assume whatever is convenient to illustrate a database design principle you want to illustrate.

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

Aside: Not mine. Why do you want to attribute these assumptions to me?

> create table departments (
> dept# number not null primary key,
> dept_name varchar(30),
> )

Your assumption appears to be that the model is implemented using SQL. That's ok with me.

> 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 */
> )

I'ld try to name the table such that individual rows convey facts, hinted at by the name of the table. This helps prevent misunderstandings. Your table name, org_chart, says something about all rows, not about what one row means. Here I'ld propose something like 'org_part_of' and rename the column accordingly. Your excercise is about what Andrea asked. Sorry if I spoiled your joke.

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

employee. dept_manager. manager references employee. But such are my requirements, not Andrea's.

How do you implement the vacancies you responded to?

Enjoy :-) Received on Sun Mar 07 2004 - 03:37:50 CET