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(at)ibm(dot)net
>
>
>
>
> ************
>