Subject Re: [IBO] info on database design
Author G. Allen Casteran
At 06:03 PM 11/15/2001 +0000, you wrote:
Good Day! :)

>1. I need to make a program which allows to make appointments
>automatically, but certain dates have to be avoided. I thought on
>storing these dates in a table. Now, what is the best primary key for
>such a table? I thought to use a date key for this, but it looks like
>people don't find this such a good idea, why? A date key in this case
>is always unique. If I would use an autoincrement, then this value
>will at a certain time get it's maximum. Yes I know... it will take a
>long time and perhaps never reaches it's maximum, but with a date as
>key this will never happen. So why is using a date key not a good

I have a rule that ALL tables use a surrogate key as their PrimeKey. This
eliminates problems of key values changing. What happens if someone changes
the office holiday from Wed to Friday one week? Your date changes and this
affects the other rows that are linked to this date record.

Since IB generators are now Int64 values you are going to have a LOT of
holidays before you get into trouble. :)

>2. What is the best, using a TIBOTable or a TIBOQuery (with select *
>form table). Speed?

If you do a "Select * FROM table" (spelling counts in SQL) I hope you are
adding a WHERE clause to that!

If you are getting the entire table, then the Query will open and fetch as
needed. The Table will fetch all AFAIK. The general rule with SQL is NEVER

If you are fetching the entire table to the client then the time to do so
should be the same with each component. The perceived time may vary, do to
the delayed fetching of the query. Call FetchAll on the query and it should
be the same performance as the table (Really Poor).

>3. What is best in speed, using a storedprocedure to update a table
>or a query?

StoredProc is always better be cause the SQL is pre-compiled.

>4. I need to iterate through a table, but not visual, how can I do
>this best, concerning speed?

Use a unidirectional cursor and just keep calling Next!

>Thanks in advance,

My Cousin Vinny: "The jury will ignore the counsels entire opening
statement - with the exception of 'Thank You' " :)