[Helma-user] allowing NULL VALUES in foreign keys

[nighthawk]

On 8/21/06, Julian Tree <julian.tree at gmail.com> wrote:
> Well two incidents lead to this conclusion. One is 3 years back, when
> working with a MySQL database that contain NULL foreign key column.
> The database was running very slow once the record reached 200,000,
> the CTO at the time eventally replaced all the NULL with zero and the
> system speed up.

If I have the time I will do some testing in Postgresql tomorrow. I
can almost not believe this ;-)

> Another is via recommendation via another friend who worked a
> financial institution. Setting a column to 0 or '' and not allow NULL
> almost help me eliminate a lot of unnecessary checking while
> programming.
>
> I don't have to check
> if(user_id == 0 or user_id == null)
>
> I have not done performance benchmarks.

I did it the other way. I created a trigger that converts '' to NULL
upon insert or update. NULL values in columns have advantages if you
create statistics with your data. When you use GROUP BY and then count
over a column that does contain NULL values, NULL does not increase
the count. That saved me quite a few rather expensive queries.

+n