Re: [GENERAL] scheduling table design - Mailing list pgsql-general

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