Re: [sqlite] newbie question about laying out a simple database for bookings
sqlfan wrote:
>
> 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...
>
You're welcome. I think you will have fun with Python.
> 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')
>
Null is an unspecified or unknown value. For columns declared as
"integer primary key" SQLite will assign a unique integer value when you
insert null.
Yes, I could have inserted null's into the resource table as well, and
SQLite would have assigned the same id numbers I inserted manually.
I tend to use manual id numbers when I have a known fixed number of
entries, and I want to refer to them directly by that id number. If I
let SQLite assign the numbers, then I would, in general, have to do a
lookup using the resource name to get the id number that had been
assigned. A typical insert into the booking table would then look like this:
insert into booking values (null,
(select resource_id from resource
where resource_name = 'resource 1'),
'2008-04-14 09:00:00', '2008-04-13 04:00:00', 'test 3');
where the resource id is replaced by a sub-select that retrieves the id
assigned to the resource.
>
> 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?
>
This is a little trickier. :-)
First you need to generate a temporary table with the range of dates
that you want to report. This will create a temporary table with 32 day
starting from the current date.
create temp table date_range(
id integer primary key,
date datetime
);
insert into date_range values(1, date('now'));
insert into date_range select null, date from date_range; --x2
insert into date_range select null, date from date_range; --x4
insert into date_range select null, date from date_range; --x8
insert into date_range select null, date from date_range; --x16
insert into date_range select null, date from date_range; --x32
update date_range
set date = date(julianday(date) + id - 1);
Next you need to join this table to the booking table several times,
once for each column in the output.
select d.date as date,
r1.info as 'resource 1',
r2.info as 'resource 2',
r3.info as 'resource 3',
r4.info as 'resource 4',
r5.info as 'resource 5'
from date_range as d
left join booking as r1 on r1.resource_id = 1
and d.date between r1.begins and r1.ends
left join booking as r2 on r2.resource_id = 2
and d.date between r2.begins and r2.ends
left join booking as r3 on r3.resource_id = 3
and d.date between r3.begins and r3.ends
left join booking as r4 on r4.resource_id = 4
and d.date between r4.begins and r4.ends
left join booking as r5 on r5.resource_id = 5
and d.date between r5.begins and r5.ends
order by d.date;
If you execute this in the shell you can set the mode to column and turn
on the headers to get a nicely formatted table. The following trace
shows this query in action.
sqlite> insert into booking values (null, 1,
...> '2008-04-07', '2008-04-10', 'test 1');
sqlite> insert into booking values (null, 2,
...> '2008-04-08', '2008-04-09', 'test 2');
sqlite> insert into booking values (null, 4,
...> '2008-04-08', '2008-04-12', 'test 3');
sqlite> insert into booking values (null, 5,
...> '2008-04-15', '2008-04-20', 'test 4');
sqlite> insert into booking value
Re: [sqlite] newbie question about laying out a simple database for bookings
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 > sqlite-users@sqlite.org > 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,
Re: [sqlite] newbie question about laying out a simple database for bookings
On 4/7/08, sqlfan <[EMAIL PROTECTED]> wrote: > > I guess I fit in the more ambitious category then. I hope my workload will > increase at any rate. > > Anyway I've looked at some tutorials, but I'm a bit daunted. > > What do you think about this layout: > > [integer booking # (=unique ID)] - [integer start date] - [integer end date] > - [text everything else about the booking] > 1 - > 2 - > 3 - > 4 - > 5 - > 6 - > 7 - > [...] > > then it seems I can "derive" the other "views", such as a view that is by > date > 2008-04-06 [free] [free] [Joe for conference] [free] [free] > Is that right? > > Do I need any other tables other than the one above that I derive the other > views from? > > Thank you! Did you see the most incredibly detailed and helpful reply that Dennis Cote provided? You owe him a beer (or a free ticket to one of your bookings). He has done most of the work you want. You can take that as the db structure and run with it. Good luck. > > > > > P Kishor-3 wrote: > > > > On 4/7/08, sqlfan <[EMAIL PROTECTED]> 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!! > >> > > > > HI sqlfan, > > > > You may benefit from a database, but for something as simple as what > > you are wanting to do, you might simply get more done by using a > > simpler, readymade program. I work on a Mac, so I don't know about > > other programs, but there a countless "journals" and "diaries" type of > > programs that exist on the Mac. Check out macupdate.com and search for > > keywords like "journal" or "personal information manager." > > > > On the Mac, most of these programs actually utilize SQLite as their > > datastore, but for the user, for you, the interface is really easy, > > readymade, attractive... best of all, most of these programs are very > > inexpensive once you decide to buy one of them... $20 to $40 for a > > license. You will support shareware, you will get your work done, and > > you will be using SQLite without even realizing it. > > > > On the other hand, if your future needs are very ambitious, and you > > are determined to roll your own db-based solution, I suggest you > > search for free SQL tutorials on the web... there are countless. Once > > you go through them, you will be better equipped to decide if you want > > to build your own. At that time you can use this SQLite list to ask > > SQLite-specific questions. > > > > Good luck, but I do recommend a readymade solution for the sake of > > getting work done. > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question about laying out a simple database for bookings
I guess I fit in the more ambitious category then. I hope my workload will increase at any rate. Anyway I've looked at some tutorials, but I'm a bit daunted. What do you think about this layout: [integer booking # (=unique ID)] - [integer start date] - [integer end date] - [text everything else about the booking] 1 - 2 - 3 - 4 - 5 - 6 - 7 - [...] then it seems I can "derive" the other "views", such as a view that is by date 2008-04-06 [free] [free] [Joe for conference] [free] [free] Is that right? Do I need any other tables other than the one above that I derive the other views from? Thank you! P Kishor-3 wrote: > > On 4/7/08, sqlfan <[EMAIL PROTECTED]> 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!! >> > > HI sqlfan, > > You may benefit from a database, but for something as simple as what > you are wanting to do, you might simply get more done by using a > simpler, readymade program. I work on a Mac, so I don't know about > other programs, but there a countless "journals" and "diaries" type of > programs that exist on the Mac. Check out macupdate.com and search for > keywords like "journal" or "personal information manager." > > On the Mac, most of these programs actually utilize SQLite as their > datastore, but for the user, for you, the interface is really easy, > readymade, attractive... best of all, most of these programs are very > inexpensive once you decide to buy one of them... $20 to $40 for a > license. You will support shareware, you will get your work done, and > you will be using SQLite without even realizing it. > > On the other hand, if your future needs are very ambitious, and you > are determined to roll your own db-based solution, I suggest you > search for free SQL tutorials on the web... there are countless. Once > you go through them, you will be better equipped to decide if you want > to build your own. At that time you can use this SQLite list to ask > SQLite-specific questions. > > Good luck, but I do recommend a readymade solution for the sake of > getting work done. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/newbie-question-about-laying-out-a-simple-database-for-bookings-tp16537380p16543002.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question about laying out a simple database for bookings
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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newbie question about laying out a simple database for bookings
On 4/7/08, sqlfan <[EMAIL PROTECTED]> 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!! > HI sqlfan, You may benefit from a database, but for something as simple as what you are wanting to do, you might simply get more done by using a simpler, readymade program. I work on a Mac, so I don't know about other programs, but there a countless "journals" and "diaries" type of programs that exist on the Mac. Check out macupdate.com and search for keywords like "journal" or "personal information manager." On the Mac, most of these programs actually utilize SQLite as their datastore, but for the user, for you, the interface is really easy, readymade, attractive... best of all, most of these programs are very inexpensive once you decide to buy one of them... $20 to $40 for a license. You will support shareware, you will get your work done, and you will be using SQLite without even realizing it. On the other hand, if your future needs are very ambitious, and you are determined to roll your own db-based solution, I suggest you search for free SQL tutorials on the web... there are countless. Once you go through them, you will be better equipped to decide if you want to build your own. At that time you can use this SQLite list to ask SQLite-specific questions. Good luck, but I do recommend a readymade solution for the sake of getting work done. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] newbie question about laying out a simple database for bookings
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!! -- View this message in context: http://www.nabble.com/newbie-question-about-laying-out-a-simple-database-for-bookings-tp16537380p16537380.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
