sqlfan wrote:
> I'm booking five resources, and right now I just use an excel sheet with all
> the dates in the first column (1/1/2000, 2/1/2000 ..., and the next five
> columns are the five resources. When I book one, I just scroll to the
> appropriate date and change the color of the column for that resource for
> all the dates it's booked for, and then I put any information about the
> booking (who booked it, their e-mail address if it's someone I don't know,
> whether it has been paid) somewhere in the colored area.
>
> I'd like to start using a database instead, but I'm very very new and I
> don't know how I should structure it. I think if I do it correctly I think
> I could check much more easily whether anything is available for a date
> range, without having to scroll through my excel sheet to find that date and
> look, which is a pain because it's kind of long. It seems most databases
> are much more complicated than mine though -- for example, I don't even have
> any structure to the "extra" information I put about a booking, I sometimes
> just put a name like "Joe for conference"....
>
> How should the database structure look. Am I even doing the right thing by
> wanting to use a database? I'm very very new and kind of lost, any help
> would be appreciated. Thank you!!
Here is a sample database to do what you want. It uses date and time
strings to store the begin and end time of each booking. You could
change these to hold only dates if you don't need bookings that last
less than a day.
The first check constraint ensures that the bookings begin and end times
are correctly ordered.
I added a nice check constraint to ensure you couldn't add a booking
that conflicted with an existing booking, but SQLite doesn't support
subqueries in check constraints, so I had to remove it (by commenting it
out). Your application will need to perform this check using a query
before inserting new bookings. There is a sample query that will return
true if there is an existing booking that conflicts with a proposed new
booking.
The last query shows how to find all the bookings at a given time.
HTH
Dennis Cote
-- resources that can be booked
create table resource (
resource_id integer primary key,
resource_name text unique
);
insert into resource values (1, 'resource 1');
insert into resource values (2, 'resource 2');
insert into resource values (3, 'resource 3');
insert into resource values (4, 'resource 4');
insert into resource values (5, 'resource 5');
-- bookings table
create table booking (
booking_id integer primary key,
resource_id integer references resource,
begins datetime,
ends datetime,
info text,
check (begins < ends) /*,
-- unsupported check constraint commented out since
-- SQLite doesn't allow subqueries in check constraints
check (not exists (select * from booking as b
where resource_id = b.resource_id
and ((begins < b.ends and ends >= b.ends)
or (begins <= b.begins and ends > b.begins))
))
*/
);
-- index to speed searches
create index time_idx on booking(begins, ends);
insert into booking values (null, 1, '2008-04-07 09:00:00', '2008-04-10
04:00:00', 'test 1');
insert into booking values (null, 2, '2008-04-08 09:00:00', '2008-04-09
04:00:00', 'test 2');
-- begins > ends constraint violation
insert into booking values (null, 2, '2008-04-14 09:00:00', '2008-04-13
04:00:00', 'test 3');
/*
-- overlap with existing booking violations would be caught by
unsupported constraint
insert into booking values (null, 1, '2008-04-10 09:00:00', '2008-04-11
04:00:00', 'test 3');
insert into booking values (null, 2, '2008-04-07 09:00:00', '2008-04-09
10:00:00', 'test 4');
insert into booking values (null, 2, '2008-04-06 09:00:00', '2008-04-12
10:00:00', 'test 5');
*/
-- query to check for conflict with existing bookings
-- application must use this to check for conflicts prior to inserting
new bookings
select exists (select * from booking as b
where :resource_id = b.resource_id
and ((:begins < b.ends and :ends >= b.ends)
or (:begins <= b.begins and :ends > b.begins))
);
-- report all bookings at a given time
select * from booking where :some_time between begins and ends order by
resource;
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
