[Nottingham] SQLite and 'locking'

[Ovid]

--- martin <martin at stupids.org> wrote:

> I agree with the normalisation in your example, and also with the use
> of
> surrogate keys, but doesn't the problem here come from duplication of
> information rather than a de-normalised layout?
> 
> In other words, by removing the "booked" flag and instead using
> booked_by == null to indicate no booking, the integrity issue goes
> away.
> 
> As I said, I can't disagree on using 3rd normal form, but it doesn't
> seem to me to be the main problem in this case.

I can see your point and many would argue that your's is a reasonable
solution.  In fact, it's a simpler solution.  However, I'm one of those
database people in the 'avoid NULLs whenever possible' camp.  Another
frequent source of serious database errors is allowing NULL fields. 
I'm not so fanatical that I refuse to design a database where NULLs
exist, but I do go out of my way to avoid them.

For those curious as to why, I have a fuller discussion at
http://use.perl.org/~Ovid/journal/27927.  It's short, but it might take
a couple of reads to understand how NULLs in a database can lead to
very wrong answers.  There are a lot of problems we face every day
because popular databases ignore relational theory and allowing NULLs
is a symptom of this.

Cheers,
Ovid

--

Buy the book -- http://www.oreilly.com/catalog/perlhks/
Perl and CGI -- http://users.easystreet.com/ovid/cgi_course/