Re: Index not used with IS NULL

From: "Andrei Ivanov" <andrei(dot)ivanov(at)ines(dot)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(at)mascari(dot)com