Dennis Cote wrote:
>
> 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
>
>
Thank you so so so so so so so much!!!! So much. Even when I have your
code in front of me, it's still very very hard for me to understand it.
This is hard stuff! So much harder than a spreadsheet, but hopefully it
will work out in the end, because I'm programming in Python so I should have
a lot of power...
Here are some basic questions: what is the "null" that you insert into
Booking as the first column of the test records??? You don't insert it into
the resource records, even though it seems to me they have the same
structure!
(their first column is a unique integer resource_id and booking_id
respectively... but then you insert into the first by
insert into resource values (1, 'resource 1');
and the second by
insert into booking values (null, 1, '2008-04-10 09:00:00', '2008-04-11
04:00:00', 'test 3')
)
2) I'd like to prepare an HTML table by dates instead of by resource...
How can I "pivot" the table so that each row I fetch to add to the table
starts with a date in the first column... This way, my presentation could be
similar to my current spreadsheet, which is really quite useful. I'd just
like it not to be so "dumb". So, if I'm looking at June 12-18, for example,
I'd like
Resource1
2008-06-12 no booking
2008-06-13 no booking
2008-06-14 booking #72
2008-06-15 booking #72
2008-06-16 no booking
2008-06-17 booking #73
2008-06-18 no booking
I'm just showing 1 resource, because I don't know if my "ascii art" table
here will get mangled in transmission, but you can imagine I'd like the rest
of the columns to be the other resources.....
How could I get this view? Thank you very much!!!!
And thank you for all your help. Again, I never could have done it by
myself :)
--
View this message in context:
http://www.nabble.com/newbie-question-about-laying-out-a-simple-database-for-bookings-tp16537380p16604545.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
