[Nottingham] SQLite and 'locking'

[Ovid]

--- David Aldred <david at familyaldred.org.uk> wrote:

> I want a routine which checks whether a 'status' field in a record 
> is 'booked', and if it isn't changes it to 'booked' and updates 
> the 'bookedby' field to show who's booked it.  Actually it's slightly
> more complex than that, but that's the basic issue.

No offense, but I don't think you really want that.

Most bugs in database applications stem from lack of data integrity and
what you're describing smacks of a violation of third normal form.  For
this form, every field in a table must be dependent on the primary key
and on nothing else.  Your 'booked_by' is dependent on 'booked' and
this suggests that bad data can creep into the system.  Just
speculating as to what you have, let's say you have a 'seats' tables
with fields 'id', 'seat_no', 'booked' and 'booked_by'.  I've
deliberately left out dates to make this easier.  Also note the use of
a non-identifying "surrogate key" to guarantee that you can renumber
the seats later, or even given them names like "a7":

  id    seat_no    booked    booked_by
  1     7          1         'bob'
  2     8          0         'alice'
  3     10         1         NULL

Hmm, what happened with #2?  It appears that it's not booked but we
have a 'booked_by' name.  And #3 appears to be booked but we have no
name.  That's because 'booked_by' isn't really dependent on the primary
key, so junk is easy to slip in there.  We can no longer tell by
looking at the tables what's supposed to be there.

Instead, break that into two tables.  'seats' has an 'id' and 'seat_no'
fields and 'bookings' has an 'id', 'seat_id', and a 'booked_by' field
(and probably a date, to be honest).  Note that a 'booked' field is no
longer necessary because the presence of a record in the 'bookings'
table means that a seat is booked.  Also, all columns get defined as
'NOT NULL':

  [seats]
  id    seat_no
  1     7
  2     8

  [bookings]
  id    seat_id  booked_by
  1     1        'bob'

The problems we saw with the first example *can't* happen with this
new, better normalized schema, so it's much harder for a programmer's
dodgy code to introduce bad data.

Cheers,
Ovid

--

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