Thread: Re: [GENERAL] scheduling table design
The advantage of (3) is that it would be extremely easy to write an application around. However, the inflexibility of it makes my stomach tighten. I agree with kaiq, I think you're making a mistake. David Boerwinkle -----Original Message----- From: kaiq@realtyideas.com <kaiq@realtyideas.com> To: Barnes <aardvark@ibm.net> Cc: davidb@vectormath.com <davidb@vectormath.com>; pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org> Date: Friday, February 25, 2000 9:12 AM Subject: RE: [GENERAL] scheduling table design >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 >> >> >> >> >> ************ >> > > >************ >
davidb@vectormath.com wrote: > > The advantage of (3) is that it would be extremely easy to write an > application around. However, the inflexibility of it makes my stomach > tighten. I agree with kaiq, I think you're making a mistake. Hmmm. What would a SQL query look like in (3) that finds all appointments for a person? Cheers, Ed Loehr > >> 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?
Nay, my friend, no mistake. Rather, I have you and Kaiq to thank for setting me straight, and I fully intend to follow your advice. What you say makes sense, and I'll go with it. I will use the datetime as well. Thank you. David Barnes -----Original Message----- From: owner-pgsql-general@postgreSQL.org [mailto:owner-pgsql-general@postgreSQL.org]On Behalf Of davidb@vectormath.com Sent: Friday, February 25, 2000 11:08 AM To: kaiq@realtyideas.com; Barnes Cc: pgsql-general@postgreSQL.org Subject: Re: [GENERAL] scheduling table design The advantage of (3) is that it would be extremely easy to write an application around. However, the inflexibility of it makes my stomach tighten. I agree with kaiq, I think you're making a mistake. David Boerwinkle -----Original Message----- From: kaiq@realtyideas.com <kaiq@realtyideas.com> To: Barnes <aardvark@ibm.net> Cc: davidb@vectormath.com <davidb@vectormath.com>; pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org> Date: Friday, February 25, 2000 9:12 AM Subject: RE: [GENERAL] scheduling table design >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 >> >> >> >> >> ************ >> > > >************ > ************
oops, it's "timestamp" now (just name change). BTW, I remember datetime is in sql92. "timestamp" is also in sql92? why "timestamp" is better than "datetime" ? sql99(96) ? thanks! On Fri, 25 Feb 2000, Barnes wrote: > Nay, my friend, no mistake. Rather, I have you and Kaiq to thank for > setting me straight, and I fully intend to follow your advice. What you say > makes sense, and I'll go with it. > > I will use the datetime as well. > > Thank you. > David Barnes > > > -----Original Message----- > From: owner-pgsql-general@postgreSQL.org > [mailto:owner-pgsql-general@postgreSQL.org]On Behalf Of > davidb@vectormath.com > Sent: Friday, February 25, 2000 11:08 AM > To: kaiq@realtyideas.com; Barnes > Cc: pgsql-general@postgreSQL.org > Subject: Re: [GENERAL] scheduling table design > > > The advantage of (3) is that it would be extremely easy to write an > application around. However, the inflexibility of it makes my stomach > tighten. I agree with kaiq, I think you're making a mistake. > > David Boerwinkle > -----Original Message----- > From: kaiq@realtyideas.com <kaiq@realtyideas.com> > To: Barnes <aardvark@ibm.net> > Cc: davidb@vectormath.com <davidb@vectormath.com>; > pgsql-general@postgreSQL.org <pgsql-general@postgreSQL.org> > Date: Friday, February 25, 2000 9:12 AM > Subject: RE: [GENERAL] scheduling table design > > > >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 06:25:12PM -0600, kaiq@realtyideas.com wrote:
> oops, it's "timestamp" now (just name change).
> BTW, I remember datetime is in sql92. "timestamp" is also in sql92? why
> "timestamp" is better than "datetime" ? sql99(96) ?
Nope, DATETIME is not an SQL92 type, it's a class of types. Here's a
snip from the standard:
<datetime type> ::=
DATE
| TIME [ <left paren> <time precision> <right paren> ]
[ WITH TIME ZONE ]
| TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]
[ WITH TIME ZONE ]
So the three SQL92 datetime types are DATE, TIME, and TIMESTAMP.
Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005
"Ross J. Reedstrom" wrote: > > On Fri, Feb 25, 2000 at 06:25:12PM -0600, kaiq@realtyideas.com wrote: > > oops, it's "timestamp" now (just name change). > > BTW, I remember datetime is in sql92. "timestamp" is also in sql92? why > > "timestamp" is better than "datetime" ? sql99(96) ? > > Nope, DATETIME is not an SQL92 type, it's a class of types. Here's a > snip from the standard: > > <datetime type> ::= > DATE > | TIME [ <left paren> <time precision> <right paren> ] > [ WITH TIME ZONE ] > | TIMESTAMP [ <left paren> <timestamp precision> <right paren> ] > [ WITH TIME ZONE ] > > So the three SQL92 datetime types are DATE, TIME, and TIMESTAMP. Hi Ross, Is the standard document you referenced online? Cheers, Ed Loehr