Re: circular relationships ok?
Date: 2 Mar 2006 12:57:20 -0800
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.
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 PortasReceived on Thu Mar 02 2006 - 21:57:20 CET