At 06:03 PM 15-11-01 +0000, you wrote:

>Hi,
>
>Jason forwarded me to this list, so here I am:
>
>It is clear I've problems in my database design. And this is the
>first time I've contact with C/S databases, I hope some people can
>give me some hints.
>
>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
>approach?

Dates are stored as double, which can cause precision errors if you store the "same" date in a related table as a foreign key. For example, at one moment, the primary key value might be 234567.00000 and, a moment (or maybe months) later the same date gets stored as 234567.00001. As designer, you expect them to match and form the relationship. But they don't match, do they? Other complex keys are to be avoided - the golden rules for keys are that they should be atomic (don't carry any meaning), immutable (can't be changed by humans), simple and short. Your date key is none of these.

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

As a general rule in client/server, table components are to be avoided. If you have a query that has to be select * from table, with no criteria, then do use a table comp. It sounds as if your Holidays table would be an example where a TIBOTable would be better than a TIBOQuery. I refer to such tables as "Control tables" (a legacy from my AS/400 days). These tables are typically very small, both in latitude and longitude, carry values that are used by a lot of other tables and their data change rarely, or never, i.e. they are static.

btw, find another name for your table. "Date" is a reserved word so use of it for an object identifier will force you to double-quote it forever. Don't do this to yourself. :)

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

It utterly depends on what kind of update you are doing. For one-by-one updates to a simple (i.e. not joined) query that are done at random (the typical interactive, live update situation) you should set your Keylinks, set RequestLive true and use the Edit method of the dataset.

For updates on a joined dataset, it is usually fastest and most elegant to write a SP for the job and invoke it in a statement in the dataset's EditSQL property.

For batched updates, you can use a parameterized query or a SP, as required, and fire it from a separate TIB_DSQL that is linked to your TIBODatabase's via its IB_Connection property.

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

The best way to iterate through a table and do stuff along the way is to do it on the server, in a FOR SELECT...DO loop type of stored procedure. IBO (unlike BDE, IBX, FIB, etc.) is responsive to activity on the server - datasets in the UI can be kept informed of work committed on the server.

The job of the UI in a good client server is to target the rows that need updating on the server and supply parameters for the work. Batch-processing data by iterating through datasets (Paradox TCursor style) on the client should be avoided. IBO has lots of methods that help to keep client-side activity closely in sync with server-side work.

You should take advantage of the client-side iteration methods help you target rows that you want to find and operate on randomly - interactive updates to single rows selected by the user, searching and targeting rows from which to extract parameters for other queries, etc.

hth
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________