Horton

Thread

  1. Re: [GENERAL] scheduling table design

    davidb@vectormath.com — 2000-02-25T16:08:07Z

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

  2. Re: [GENERAL] scheduling table design

    Ed Loehr <eloehr@austin.rr.com> — 2000-02-25T16:54:14Z

    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?

  3. RE: [GENERAL] scheduling table design

    Barnes <aardvark@ibm.net> — 2000-02-25T23:25:56Z

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


************

  4. Re: timestamp ?(RE: [GENERAL] scheduling table design)

    Ross J. Reedstrom <reedstrm@wallace.ece.rice.edu> — 2000-02-26T00:02:20Z

    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

  5. timestamp ?(RE: [GENERAL] scheduling table design)

    kaiq@realtyideas.com — 2000-02-26T00:25:12Z

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

  6. Re: timestamp ?(RE: [GENERAL] scheduling table design)

    Ed Loehr <eloehr@austin.rr.com> — 2000-02-26T00:36:01Z

    "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