From: "Andrei Ivanov" <andrei.ivanov@ines.ro>
>
> This is a resend, don't know if the first time it got to the
list... sorry
> if it did.
>
> Hello, sorry for barging in...
> I use a similar structure for keeping some some text pages
categorized.
>
> CREATE TABLE pages (
> id SERIAL NOT NULL PRIMARY KEY,
> categ INTEGER,
> CONSTRAINT categ_fk FOREIGN KEY(categ) REFERENCES
categs(id) ON DELETE CASCADE
> );
>
> All the pages that are not contained in a category are marked
by categ IS
> NULL ( this is like the files in / in a filesystem). If I use
other values
> than NULL for marking this kind of pages, then the constraint
would
> complain, but then I can't use an index to find these pages.
>
> Do you have a better solution for this ?
If some pages aren't associated with a category, shouldn't you
have three relations?
categories (
categ PRIMARY KEY
...
);
pages (
id PRIMARY KEY
...
);
category_pages (
categ INTEGER NOT NULL,
id INTEGER NOT NULL
);
Similarly, with previous posts regarding hierarchies, the model
should look like:
employees (
employeeid PRIMARY KEY
...
)
employee_manager (
employeeid INTEGER NOT NULL,
manager INTEGER NOT NULL
)
*not*:
employees (
employeeid PRIMARY KEY,
manager INTEGER
);
NULLs are evil. ;-)
Mike Mascari
mascarm@mascari.com