Thread

1. Re: [GENERAL] scheduling table design

   davidb@vectormath.com — 2000-02-24T16:36:30Z

   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?
   >
   >
   
   
   

2. Re: [GENERAL] scheduling table design

   kaiq@realtyideas.com — 2000-02-24T18:18:21Z

   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?
   > >
   > >
   > 
   
   
   

3. RE: [GENERAL] scheduling table design

   Barnes <aardvark@ibm.net> — 2000-02-25T14:28:37Z

   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
   
   
   
   
   

4. Re: [GENERAL] scheduling table design

   Patrick Welche <prlw1@newn.cam.ac.uk> — 2000-02-25T15:54:24Z

   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 ?
   
   

5. RE: [GENERAL] scheduling table design

   kaiq@realtyideas.com — 2000-02-25T15:56:59Z

   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
   > 
   > 
   > 
   > 
   > ************
   > 
   
   
   

6. Re: [GENERAL] scheduling table design

   kaiq@realtyideas.com — 2000-02-25T16:58:38Z

   
   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?