Re: How to ensure data consistency?

"Felix E. Klee" <felix.klee_at_inka.de> wrote in message news:<20040907154957.697949a1.felix.klee_at_inka.de>...
> Hi ddtl,
>
> I have a similar problem, although mine is about enforcing "complete
> disjoint specialization", whereas yours is about enforcing "incomplete
> disjoint specialization" (terminology is from the article that you
> pointed to).
>

Not sure whether or not you found an answer to your question (I seem to miss some posts in this thread). Any how, here is how I would model it:

create table PERSORGS (

    persorg_id int not null primary key,     type char(1) not null,
    <other common attribs>,
    check (type in ('P', 'O')),
    constraint AK_PERSORGS unique (persorg_id, type) )

create table PERSONS (

    persorg_id int not null primary key,     type char(1) not null default 'P',     <other perosn specific attribs>,
    check (type = 'P')),
    constraint FK1_PERSONS FOREIGN KEY (persorg_id, type)

        REFERENCES PERSORGS (persorg_id, type) )

organisation similar to persons.

As someone already mentioned it doesnt protect against a PERSORG having 0 persons and 0 organisations. For that we would need defered constraints or something similar.

HTH
/Lennart        

person_id (PK)
persorg_id (FK)
...

ORGANIZATIONS
organization_id (PK)
persorg_id (FK) Received on Wed Sep 08 2004 - 18:26:06 CEST