Re: circular relationships ok?

From: David Portas <>
Date: 2 Mar 2006 12:57:20 -0800
Message-ID: <>

Alexandr Savinov wrote:
> David Portas schrieb:
> > Let's try an even simpler and even more common example: An
> > organizational hierarchy.
> >
> > CREATE TABLE Employees
> > (employee_id INTEGER PRIMARY KEY,
> > employee_name VARCHAR(50) NOT NULL,
> > manager_id INTEGER NOT NULL
> > REFERENCES Employees (employee_id),
> > CHECK (employee_id =0 OR employee_id <> manager_id));
> >
> > The business rule is that each employee has a manager who is also an
> > employee.
> An arrow goes from Employee up, then turns down and then comes to this
> concept from down:
> /\
> Employee |
> \/
> That is very simple. Loops (self-references) can be used because they
> are very convenient and frequently used. And they have clear and
> unambiguous semantics. My opinion that loops should be permitted but not
> cycles. Cycles do not have unambiguous semantics. If you can explain to
> your database how to interpret them then cycles can also be used but I
> do not know such an interpretation. And, IMHO, it is reason why
> contemporary database do not manage such relationships - it is a task of
> the programmer.
> --

I understand now that you don't recognize cycles because your diagrams are directed graphs. Does that mean you can only support common cardinality and not N-cardinality?

For example, suppose I want to implement a constraint that each Order must have exactly one Invoice and each Invoice must have exactly one Order. That seems like a reasonable business rule, even if it's a slightly unusual one.

You say that cycles should not be permitted. I'm not clear if that is a limitation of your model or if it is just a recommendation. Do you mean that I *cannot* enforce my business rule that invoices and orders must match one for one?

David Portas
Received on Thu Mar 02 2006 - 21:57:20 CET

Original text of this message