Thread: Re: [GENERAL] scheduling table design
Hi kaiq, You asked: >it is a good idea. but why it is really necessary? As you guessed, we chose this approach to keep from having to batch extend the data table. Batch extending the table would not have worked for this project for two reasons: 1) The application tracked reservations at expensive restaurants. Most reservations were for one to two weeks in advance, but they could be for as much as a year and a half in advance. I had not considered the possibility of dynamically extending the data table. 2) Ordinarily only a small percentage of the available items (tables at the restaurants) were reserved. The restaurants liked this because they wanted to maintain an uncrowded atmosphere. However, this meant that batch extending (or even dynamically extending) would create a large percentage of records that were never used. I have to decided to add a third reason: 3) Batch extending would not allow overloading of appointments. David Boerwinkle -----Original Message----- From: kaiq@realtyideas.com <kaiq@realtyideas.com> To: davidb@vectormath.com <davidb@vectormath.com> Cc: Barnes <aardvark@ibm.net>; pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org> Date: Thursday, February 24, 2000 9:56 AM Subject: Re: [GENERAL] scheduling table design > > >On Wed, 23 Feb 2000 davidb@vectormath.com wrote: > >> Hello Mr. Barnes, >> >> I don't know of a nice solution to the problem of scheduling events that may >> occur indeterminately far into the future. The way I have solved this >why you need that? cycling scheduling? -- that is my "issue" also. For >cycling scheduling, I have to set a limit. I'm considering a subroutine to >automatically batch-extend the limit. And, the third step is add a >subroutine to kind of sense the need to extend the limit Dynamically (not >only batch-extend) -- that is much more difficult, and I do not really >plan to do that ;-) >> problem before is to have a table of available items. In this case the >> available items would be something like: >> 1 9:00 Dr. Jones >> 2 9:30 Dr. Jones >> 3 10:00 Dr. Jones >> . >> . >> . >> 17 9:00 Dr. Smith >> 18 9:30 Dr. Smith >> 19 10:00 Dr. Smith >> etc. >> This serves as the control table. >nice. > >> One problem with this solution is that your client will have to settle on a >> minimum granularity for appointment times. That is, does he have >> appointments every half hour, or every fifteen minutes? >it is a good idea. but why it is really necessary? > >
there are two issues here: one is the html(or applet) gui, another is the database. I was talking about the latter. but seems you also on the former? for gui: I also setup a minimal time granularity because I do not want the user to type in, I only let them click -- I hate javascript and I hate junk data. However, the database and scheduling manipulation algorithm do not require minimal granularity (I hope the system can be used longer and broader). for database: batch-extending is only needed for cycling scheduling. e.g., if someone schedule Every WeekEnd for the high-end hotel (good life!). then, the program can only put finite number of appointments (events, schedules, whatever name you like) in the database. I choose to do it for example two years. OOPS, I found a design bug!!!!! It surly affect other scheduling also. however, it is not related with gui. On Thu, 24 Feb 2000 davidb@vectormath.com wrote: > Hi kaiq, > > You asked: > >it is a good idea. but why it is really necessary? > > As you guessed, we chose this approach to keep from having to batch extend > the data table. Batch extending the table would not have worked for this > project for two reasons: > 1) The application tracked reservations at expensive restaurants. Most > reservations were for one to two weeks in advance, but they could be for as > much as a year and a half in advance. I had not considered the possibility > of dynamically extending the data table. > 2) Ordinarily only a small percentage of the available items (tables at the > restaurants) were reserved. The restaurants liked this because they wanted > to maintain an uncrowded atmosphere. However, this meant that batch > extending (or even dynamically extending) would create a large percentage of > records that were never used. > > I have to decided to add a third reason: > 3) Batch extending would not allow overloading of appointments. > > David Boerwinkle > > -----Original Message----- > From: kaiq@realtyideas.com <kaiq@realtyideas.com> > To: davidb@vectormath.com <davidb@vectormath.com> > Cc: Barnes <aardvark@ibm.net>; pgsql-general@postgreSQL.org > <pgsql-general@postgreSQL.org> > Date: Thursday, February 24, 2000 9:56 AM > Subject: Re: [GENERAL] scheduling table design > > > > > > > >On Wed, 23 Feb 2000 davidb@vectormath.com wrote: > > > >> Hello Mr. Barnes, > >> > >> I don't know of a nice solution to the problem of scheduling events that > may > >> occur indeterminately far into the future. The way I have solved this > >why you need that? cycling scheduling? -- that is my "issue" also. For > >cycling scheduling, I have to set a limit. I'm considering a subroutine to > >automatically batch-extend the limit. And, the third step is add a > >subroutine to kind of sense the need to extend the limit Dynamically (not > >only batch-extend) -- that is much more difficult, and I do not really > >plan to do that ;-) > >> problem before is to have a table of available items. In this case the > >> available items would be something like: > >> 1 9:00 Dr. Jones > >> 2 9:30 Dr. Jones > >> 3 10:00 Dr. Jones > >> . > >> . > >> . > >> 17 9:00 Dr. Smith > >> 18 9:30 Dr. Smith > >> 19 10:00 Dr. Smith > >> etc. > >> This serves as the control table. > >nice. > > > >> One problem with this solution is that your client will have to settle on > a > >> minimum granularity for appointment times. That is, does he have > >> appointments every half hour, or every fifteen minutes? > >it is a good idea. but why it is really necessary? > > > > >
First, let me start off by thanking you two for the design ideas. You've been very helpful, as have Ed and Omid who focused more on laying the groundwork for approaching the problem. Maybe I'm overcomplicating things. You both seem to be suggesting a table something like: 1) date | doctor | time | patient_id# | reasonfor_app | kept_app | authorized with David's variation of putting the doctor and time information in a separate table so that I might have two tables: 2) date | time_doc_link | patient_id# | reasonfor_app | kept_app | authorized and time_doc_link | time | doctor | active_flag I was previously thinking that I needed to do something like creating the following table: 3) date | doctor | 0800 | 0815 | 0830 | 0845 | 0900 ....and so on every 15 minutes where each time slot holds a reference# to an appointment database such as: reference# | patient_id# | reasonfor_app | kept_app | authorized Assuming I am summarizing 1) and 2) correctly-the way you suggested-then you two have already explained the advantages and disadvantages of each of those solutions compared to one another. 3) however, is fundamentally different in that time is a field name instead of an actual field. It is inflexible timewise, but does it offer any advantages such as speed or simplicity in the SQL searches? Has 3) ever been done, or is it seriously flawed somehow? Are there other solutions? Thank you again. David Barnes aardvark@ibm.net
3) is weird. it looks like a typical mistatke that use the data as the schema. It is not flexible and waste of disk (ya, I know it cheap. but it you waste too much!). And, more importantly, you gain nothing. the "correct" table is already so simply! do not use date, use datetime. why? it's sql92 standard (another good reason: M$sql only has datetime :-). A lot of useful functions only apply to datetime, not date. you did not mention eventid or appointid. David or somebody else(? sorry) mentioned this: do not use datetime as the primary key. It makes thing complicated and lose an important feature (overlapping events). those id's should be serial type (or sequecne). you may need another table to differentiate "event" and "appointment". event is something need to happen, no time set yet. An event could have many proposed appointments. -- ok, "events" and "appointments", you can use your words. you got the idea. It's only needed if you want differentiate them (for some fancy feature). On Fri, 25 Feb 2000, Barnes wrote: > First, let me start off by thanking you two for the design ideas. You've > been very helpful, as have Ed and Omid who focused more on laying the > groundwork for approaching the problem. > > Maybe I'm overcomplicating things. You both seem to be suggesting a table > something like: > > 1) date | doctor | time | patient_id# | reasonfor_app | kept_app | > authorized > > with David's variation of putting the doctor and time information in a > separate table so that I might have two tables: > > 2) date | time_doc_link | patient_id# | reasonfor_app | kept_app | > authorized > and > time_doc_link | time | doctor | active_flag > > > I was previously thinking that I needed to do something like creating the > following table: > > 3) date | doctor | 0800 | 0815 | 0830 | 0845 | 0900 ....and so on every 15 > minutes > where each time slot holds a reference# to an appointment database such as: > reference# | patient_id# | reasonfor_app | kept_app | authorized > > > Assuming I am summarizing 1) and 2) correctly-the way you suggested-then you > two have already explained the advantages and disadvantages of each of those > solutions compared to one another. 3) however, is fundamentally different > in that time is a field name instead of an actual field. It is inflexible > timewise, but does it offer any advantages such as speed or simplicity in > the SQL searches? Has 3) ever been done, or is it seriously flawed somehow? > Are there other solutions? > > Thank you again. > > David Barnes > aardvark@ibm.net > > > > > ************ >
On Fri, Feb 25, 2000 at 09:56:59AM -0600, kaiq@realtyideas.com wrote: > > do not use date, use datetime. why? it's sql92 standard (another > good reason: M$sql only has datetime :-). A lot of useful functions > only apply to datetime, not date. On a side note: if I have a date d and a time t column, how can I combine them into a single datetime dt column? Simply insert into foo (dt) select (d+t)::datetime ?
On Fri, 25 Feb 2000, Patrick Welche wrote: > On Fri, Feb 25, 2000 at 09:56:59AM -0600, kaiq@realtyideas.com wrote: > > > > do not use date, use datetime. why? it's sql92 standard (another > > good reason: M$sql only has datetime :-). A lot of useful functions > > only apply to datetime, not date. > > On a side note: if I have a date d and a time t column, how can I combine them > into a single datetime dt column? Simply > insert into foo (dt) select (d+t)::datetime ? > converting it? not too late :-) seriously: sounds right. I remember somebody asked that before. and a smart guy find out the formula thro experiment, very impressive -- the plus (+) is important. not tested myself tho. check the archive please. BTW, how do you know it's plus (+)? are you genius?