[Nottingham] SQLite and 'locking'

[Martin Garton]

> Except when they return bad data.  Consider the following very
> reasonable looking query to determine how many seats are not booked:
> 
>   SELECT COUNT(booked_by)
>   FROM   seats
>   WHERE  booked_by IS NULL
> 
> In most databases, that will always return 0 regardless of how many
> seats are or are not booked but it's easy for a developer to make that
> mistake.

That seems obvious to me and should return 0.  That statement is
counting non-null values.  If you want what I think you were trying to
get then use count(*) instead of count(booked_by) for example which
works fine.

>   However, the problem is even worse than that.  By overloading
> the meaning of 'booked_by' to mean both "this seat is booked" and "this
> person booked the seat", the poor developer reading the database code
> has no way of knowing what NULL means.  Was the seat not booked?  Was
> it booked by we do not know by whom?  Is there a bug in the code?  I am
> constantly trying to stamp out bugs in a horrid database caused by
> allowing NULL values in it.

I agree that when you allow NULL you have to be clear about what it
means.  In this case, everything is fine if you define it to mean "the
seat has not been booked"

> > One reason is that because by always avoiding nulls at all
> > costs can actually make things harder for developers to understand
> > and so bugs spring up for different reasons.
> 
> To a large extent that's because most programmers treat databases as a
> dumb data store and don't realize that proper design of a database is
> as important as proper design of the code.  By ignoring database
> design, they introduce many long-term bugs that keep me employed (and
> I'm a Perl programmer, not a DBA!)

I've lost count of how often I have said similar things myself.  But
there's bad design and there's bad design.  Banning the use of NULL just
goes too far and would cause more problems than it solves in my
environment. Even in isolation being the only developer involved I would
use NULL because I understand (some of!) its problems and limitations.

> > Also, taking things to the extreme (6th normal form for example)
> > tends to destroy performance in many use cases.
> 
> We are in complete agreement here.  I would argue against "premature
> optimization" and say not to worry about it until a performance problem
> can actually be demonstrated

Agreed although I wouldn't say avoiding 6th normal form was premature
optimisation. There is a line to be drawn, and 6th normal form is the
wrong side of it right now.

-- 
Martin.

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 191 bytes
Desc: This is a digitally signed message part
Url : http://mailman.lug.org.uk/pipermail/nottingham/attachments/20070227/2e053376/attachment-0001.bin