[Nottingham] SQLite and 'locking'

[Duncan John Fyfe]

On Wed, 2007-02-28 at 10:32 +0000, David Aldred wrote:
> On Tuesday 27 February 2007 11:21, Duncan John Fyfe wrote:
> > If you use NULL to flag 'available' how do you record a seat as
> > 'unavailable and not booked' (eg. seat 10 has been broken) ?  
> > Here is a new perfectly reasonable 'not booked' state.
> > To accommodate this in your NULL based scheme you must change the
> > database
> 
> Not that I'm planning to use null for 'available', but surely this depends on 
> the intended purpose of the database?
> 

The purpose of a database is to store, maintain the integrity of and
provide access to data :)

> All I'm interested in at the moment is whether a seat is available for 
> booking.  If it's not, then users can't book it, and that's all they need to 
> know!
> 

The database is not about what the user needs to know.  It is for your
application to select and present appropriate data to the user.
The database is about making sure the data you can present to the user
and retain for yourself is correct and sufficiently complete for your
needs.

> I can see that for a wider application (such as a general theatre management 
> system) a more detailed approach would be necessary - but for a booking 
> system for a local theatre group who don't even have a fixed theatre to 
> manage, ISTM that putting something which can't be a valid booking reference 
> into the 'bookedby' field is all that's needed if a particular seat is 
> unavailable.
> 

If you think about it a seat can be booked then be made unavailable (it
was broken on the 2nd night of a week long run).  If that happens you
want to know so you can take appropriate action (eg contact the customer
or assign them a new seat).

If you overload booked_by with special values eg .{ { 'available' ,
'unavailable' } , { Valid names } } and they are not all mutually
exclusive then you will lose information.

If your seats table is:
	id   seat_num    booked_by    date
	1    3           available    3rd night
	2    6           unavailable  2nd night
	3    9           Joe Bloggs   6th night
	4    12          John Smith   1st night

seats.id 3 breaks as above:
	Update seats set booked_by='unavailable' where seat_num=9;

whoops you have lost data - you can no longer tell that the seat was
ever booked.

Alternatively you need to notify someone about a pending change in the
database (I am going to change Joe Bloggs to unavailable) which might
not happen (your application crashes or the database is locked by
someone booking a seat etc).

Either way you leave yourself unable to trust your data.
hmm, I thought I flagged seat_num 9 as unavailable but it still says
'booked_by=Joe Bloggs'.  Is that because the seat has been fixed or
because the update didn't complete (for whatever reason) ?  Do I still
need to act on this ?

You are right, you don't want a complicated schema but neither do you
want one so simple it is easily broken or has to be recreated every time
you want to change something.

Have fun,
Duncan