Introduction

This session will focus on the holy grail of application development  the best way to develop multi user database applications. Since there are many excellent sessions on n-tier development, I will focus on Local and client/server (2-teir) techniques using TTables, TQueries, TClientDataSets, and TDataSetProviders. Each technique discussed will be done my example. All of the examples are available for Delphi 4, Delphi 5, and C++Builder  check the subdirectory for your particular product. Since this session covers C++Builder and Delphi, there will be very little code discussed  see the examples for actual code.

A Little Background

Before we begin, Id like to start by admitting that Im a Paradox guy. I started out developing database applications in Paradox for DOS 3.5. Some of you may remember when Borland did on-line support via CompuServe. Back then all of the Paradox gods would dispense their wisdom. Those that showed intelligence along with their wisdom were invited to join TeamB - a group of volunteers that still remain today. After reading the CompuServe SIGs for several days, I thought to myself I can do that! - and dispensed my wisdom. I dont remember what the question was, but I do know I got for responses from various members, including one from John B. Moore who had one thing to say:

You are working to hard.

Of course he was right, what I posted could have been done in one line - I was using five lines of code. Shortly after this embarrassing episode, I attended the second annual IPUC (International Paradox Users Conference -which turned into ICON), where I learned one of the secrets to Borland database development:

If you are writing a lot of code, you are probably doing something wrong.

What I found was that developers took pride in writing as little code as possible. Most of the experts found that the better they understood the Paradox model, the less code they had to write. When Paradox for Windows was released, it became even more necessary to learn the event model before writing a single line of code. Many developers were writing a lot of unnecessary code to display data. In most cases, a simple understanding of the BDE solved many problems. A good understanding of the event model separated the hacks from the true developers.

With the Dont Work Too Hard rule in mind  lets get started.

What is Right Sizing?

What a question as far as C++Builder and Delphi are concerned, it means using the right tools for the job. A better question would be What can I do to fix this slow beast? The standard questions seem to be:

• When should you use a TTable?
• Why should you use a TQuery?
• How should I deploy the application?
• Should I use MIDAS components in a 2-teir application?
• Do I need the BDE?
• Can I deploy an application without the BDE?

The answer to all of these questions is It depends. (Sorry to disappoint those looking for the quick answer). Before size, platform, and VCL choice questions can be answered, the following need questions need to be asked:

• How many users will be using the application?
• How will data be entered?
• Where will the data be stored?
• What is going to be done with the data?

One thing I can tell you for sure about development is:

If youre not using the Enterprise version of Delphi or C++Builder, get it. There are tools that are only available in these versions that make developing database applications much easier. Yes, the Enterprise versions are more expensive than the developer versions, however the time saving that can be gained by using MIDAS, as well as the SQL Monitor, can easily offset the price difference. Your time is worth it.

The BDE

The BDE stands for the Borland Database Engine. Some like it, some hate it, but it is what we have to use when using TDataSets. Those that hate it want to see it replaced, those that understand it, dont have much of a problem with it (other than distribution size). Regardless of how you feel about it, it is important that you understand a few things:

Every BDE application MUST have a Private Directory.

The private directory must be unique for EVERY application that uses the BDE. There are temporary files that are stored in this directory  even if you are only using TQueries. If you get a Directory is Busy error, then you have an application that is already using that directory (or you had a TSession open in design mode before you ran the current application).

Every BDE application MUST have a Net File directory.

If you are sharing local tables, every application on the network MUST have the NetFileDir pointing to the same physical drive location. Although this is set in the BDE Configuration utility, never rely it on it to be accurate.

Every BDE application must have an Alias defined for pointing to the data OR have a TDatabase that defines where the data is located.

For local tables this is the directory that tables are located. For remote tables, it is the actual file (or database) name. This one is rarely messed up  if you can't see the data, you are not going to get very far.

If you can remember these 3 simple things about the BDE, you will learn to live with the BDE. I dont expect the BDE to go away in the near future  there are too many apps that need it (backward compatibility). There were not any significant changes to the BDE in Delphi 5, and there is a lot of new development in ADO. This may be a sign of things to come, but for now, we have the BDE.

If you are using TTables on a local file server, you will need to make a few changes to the BDE and network configurations:

For Novell Servers:

• Make sure a mapped drive exists for the Novell server. Using F: is fine.
• All users need to have full rights to the Netfile directory and the Data directory. These should be different physical directories.
• If using Novells Client32 you will need to make a few changes to the Advanced Client settings. If you do not change these settings, you will see some different behavior  updates not appearing (due to writes not being committed to the server) as well as locking issues. At a minimum, change the following:
  • Set Opportunistic Locking to Off.
  • Set Packet Burst to Off.
  • Set Cached Writes to Off.
  • Set File Cache Level = 0.

For NT Servers:

• Make sure a mapped drive exists for the NT share.
• All users need to have full rights to the NT share for the NetFile directory and the Data directory. These should be different physical directories.
• You MUST change the Local Share setting in the BDE to True as shown in the BDE configuration below.

Examples

Every database application I develop starts with a DataModule that contains a TSession and TDatabase. I do this for defensive purposes as well as my own sanity. Since I might test an application while other applications are running, I need to control where the PrivateDir is set, as well as the lock file. If I do it in a TSession, I can make changes at design time without affecting any other applications (any change made to the BDE Configuration would affect all BDE applications). If I am doing TTable work, I can also control the NetFileDir. Once again, this is somewhat redundant, but I NEVER trust the alias that the BDE returns. By using a TDatabase I can control where I am getting the data at design time  great for pointing at test data rather than production data.

TTable_1  Local TTables

The first example is a simple master/detail of Vendors and the Parts they supply. This is a file server based application (as noted in the TDatabase). I thought I would deviate a little from the usual Client/Orders demo <g>. The only thing really worth looking at is how the DBNavigator is assign the DataSource property at runtime:

DBNavigator1.DataSource := (Sender as TDBGrid).DataSource;

Once the application is run and the Open button is clicked, the TTables are open, the detail records are displayed for each master, and both grids are editable. Note that when you insert a record in the detail table, the master record key is automatically inserted for you.

How many users?

At some point, the application may grow too large to use local tables. At that point the data is usually moved to a SQL server.

The biggest problem is deciding when this might happen. Everyone seems to be looking for the magic number of users the BDE will support with local tables. The physical limitation is 250 users. The real world number is it depends.

I have a client with a Paradox for Windows application that uses BDE 2.5. They regularly have ~60 users in it. The application is for real time physician staffing. It has ~90 tables, with 4 of them used on every form. There are also several sites that are running 100+ users on Delphi applications, but mostly for read only information. The point is that there is not a magic number.

My recommendation is to use InterBase when the application will have more than 40 simultaneous users. Below this number, ANY well designed application will work fine using local tables.

All examples from this point on will require a running InterBase server. I am using the default C:Program FilesBorlandBorland SharedData install directory, which should contain the MastSQL.gdb and Employee.gdb files.

TTable_2  Remote TTables

If you read the Borland documentation (and I highly recommend that you do), you will get the idea that all you have to is run the Data Pump to move your data to InterBase, then change the Alias. Since the MastSQL.gdb contains the Vendor and Parts table that we used in the local example, we will change the alias in the application.

Since we added a TDataBase to our application, all we need to do is change the parameters.

Double click on the Database1 component. The current parameters are set for local tables. Since we want to change all of the parameters, we can copy them from an existing alias by doing the following:

• Select an existing remote alias from the Alias Name.
• Click the Defaults button.
• Copy the contents from the Parameter Overrides.
• Select INTERBASE for the Driver Name. Notice that the Parameter Overrides are reset to blank.
• Paste the values back into the Parameter Overrides.
• Modify the following parameters:
SERVER NAME to d:program filesBorlandBorland SharedDataMastSQL.gdb
USER NAME=SYSDBA
PASSWORD=masterkey

• Disable the login prompt

To check the connection, change the Active property to True. If all goes well, it should connect. If it doesnt connect, then the SERVER_NAME is not correct.

Run the application. It appears to work exactly like the local table version  but at a cost. While this example works with a few records in the example table, it will crawl on large tables.

The BDE is doing a lot of work for you behind the scenes. To find out how much work is really going on, use the SQL Monitor (you are using the Enterprise version right?). The SQL monitor will show what is being processed by the BDE to InterBase. Move from record to record. Note the amount of activity in the log. Every time a new master record becomes current, the BDE is fetching all of the details for that record.

One of the keys to optimizing a SQL application is reduce the amount of data moving to and from the server. In the current example, a lot SQL activity is happening. Since the application uses TTables, it is really doing a SELECT * statement at the server, and repeating it every time it lands on a new master (to see if it needs to be refreshed), and for every detail table.

TQuery_1  Change from TTables to TQueries

The alternative to using TTables is to use TQueries. TQueries still use the BDE, but you the developer define the SQL that executes.

According to the conventional wisdom, a rookie developer usually swaps out the TTables and replaces them with TQueries. Make sure you change the Session and Database properties in the TQueries to the values in the DataModule.

The next step is to write the SQL statements. The common choice for SQL at this level is:

Master table:

SELECT *
FROM VENDORS
ORDER BY VENDORNO

Detail table:

SELECT * FROM PARTS
WHERE VENDORNO = :VENDORNO
ORDER BY PARTNO

Since we have changed to TQueries, the master/detail link needs to be updated. Change the detail querys DataSource to point to the masters DataSource. If you look at the detail SQL, it references the :VENDORNO. Using the DataSource, it will fill in the proper value at execution time.

Run the application. The first thing you will notice is that both the master and detail are not editable by looking at the DBNavigator. The other major thing to note is that the number of SQL statements did not decline.

TQuery_2  Live Query

The first thing that will probably be done is to make the TQueries editable. The first obvious choice is to use a live query. This can be done by setting each of the Tquerys RequestLive property to True.

When running the application, it might appear that things are working like the TTable example. As you try and add/edit/delete data, you will notice the following problems:

• Detail records do not get the master record key value.
• Inserting detail records post, but disappear until you move off of the master record then move back to it.
• Changes to the master record key value are not cascaded to the detail records.
• Inserted records are not sorted  even though the SQL has an ORDER BY clause.

There are many limitations to live queries  for the most part, unless it is a SELECT *, they will not return a live result set. See the online documentation for more about other limitations.

TQuery_3  Cached Updates

After realizing that live queries have problems, the next solution seems to be cached updates. Cached updates hold all modifications to the data in memory until they are applied.

To change from live queries to cached updates, do the following:

• Change the Request Live property to false for both TQueries
• Change the Cached Updates property to true for both TQueries
• Drop 2 TUpdateSQL components on the application
• Link each of the TUpdateSQL components to its own TQuery

Now we need to generate the SQL statements that TUpdateSQL will use applying the data. The steps are :

• Double Click on the master TUpdateSQL component. The Table Name should be VENDORS
• Click the Select Primary Keys button. Note that the primary key field VENDORNO is now highlighted
• Make sure all of the Update Fields are selected
• Click on the Generate SQL button.

The SQL for updates, inserts and deletes is now generated. If necessary, you can make changes to the SQL code in the dialog window.

Now do the same for the detail records  the Table Name should be PARTS, and the Primary Keys should be PARTNO.

Since we are caching the updates for the TQueries locally, we need some way to apply them. If they are not applied, any change we made will be lost. To apply the changes:

• Add a new button to the application next to the Open button
• Double click the open button to add an OnClick event
• In the Event Handler, add the following code:
Query1.ApplyUpdates;
Query2.ApplyUpdates;

Since you opened up the TQueries in design mode, it opened both the Database and Session in the main DataModule. Before running this application, change the Active property of the TSession to false. This will close the database and all tables associated with it.

All of the changes are now made, so run the updated application. Note that the following problems still exist:

• Detail records do not get the master record key value.
• Inserting detail records post, but will go to never never land if you move off of the master before applying the update.
• Changes to the master record key value are not cascaded to the detail records.
• Inserted records are not sorted  even though the SQL has an ORDER BY clause.

Additionally, there is not much change in the amount of SQL statements that are moving back and forth to the server. However it is still not as big of a problem as the 4 problems listed above.

TQuery_4  Cached Updates with better SQL

In order to simplify some things, we decide to change the SQL for the master to show only the VENDNO, VENDNAME, and a Location. Change the master SQL to:

SELECT VENDORNO, VENDORNAME,
CITY || ', ' || STATE || ' ' || COUNTRY) AS LOCATION
FROM VENDORS
ORDER BY VENDORNO

Since the SQL has changed, we need to change the TUpdateSQL as well:

• Double Click on the master TUpdateSQL
• Click Select Primary Key. The key should still be VENDORNO.
• In the Update Fields, select ONLY VENDNO and VENDNAME. The LOCATION is calculated, so there is no way to update this field.
• Click on the Generate SQL button.

What will now happen with the master is that it will only update the VENDNO and VENDNAME fields. The Select query will still get the Location however.

We need to change the detail SQL to show an Inventory Value:

SELECT VENDORNO, PARTNO, DESCRIPTION, ONHAND,
ONORDER, COST, LISTPRICE,
(ONHAND * COST) as INVENTORY_VALUE
FROM PARTS
WHERE (VENDORNO = :VENDORNO)
ORDER BY PARTNO

Once again the details TUpdateSQL will need to be changed:

• Double Click on the detail TUpdateSQL
• Click Select Primary Key. The key should still be PARETNO.
• In the Update Fields, select everything but the INVENTORY_VALUE field.
• Click on the Generate SQL button.

Before running the application, make sure the TSession is closed  it was opened when generating the TUpdateSQL statements.

Run the application. The following problems still exist:

• Detail records do not get the master record key value.
• Inserting detail records post, but will go to never never land if you move off of the master before applying the update.
• Changes to the master record key value are not cascaded to the detail records.
• Inserted records are not sorted  even though the SQL has an ORDER BY clause.

The purpose of changing the master was to reduce the amount of data coming from the server. At least that was the intention. To check if this is really true, we need to look at the SQL monitor. As you scroll records, notice that there are still SQL select commands every time you move the master record. The amount of data that is being retrieved is less since we are not pulling in most of the address information  a good thing.

The changes to the detail record were done to show that server generated result fields (calculations of fields returned by the server) are not editable, nor will they update. A better choice would be to use Calculated Fields.

In order to make cached updates work, there has to be a lot of code written by the developer. The basics are:

• Before leaving the detail grid, you need to check to see if records need to be applied.
• If detail record changes need to be applied, a bookmark must be set for the master record.
• The changes to the detail record need to be applied.
• The master table needs to be closed, then reopened, as well as the detail table.
• The master record must be found in the result set (using the bookmark set above).
• Every insertion of a detail record must have code in it that sets the foreign key to the masters value.

TQuery_5 - Server Generated Keys

There is one other scenario that might pop up with remote tables  server generated keys. In most cases, the server holds a number that is used as the next value for a Primary Key. Properly designed databases use this technique regularly. The problem is that the value is unknown until after the record is posted to the server.

To illustrate this, open example TQuery_5 on the code disk. It is a SELECT * query for employees and a SELECT * query for the details. New employees have a server generated key. Otherwise it is identical to the TQuery_3 example.

Run the example application. Insert a master record, then insert a detail or two. Now apply the changes. What happens?

The best possible way to deal with the server generated key problem is to use a stored procedure that returns the next value. You would have to code it so that was called every time you insert a record at the application level (on a BeforeInsert event). This would be very quick. The down side to this approach is that key values may be used and never posted. If you need to make sure that every value is used (like for a check register for instance), you will have to write a LOT of code.

TClientDataSet_1  Who needs cached updates?

To say that cached updates are problematic is an understatement. There has to be a better way right? What about my initial statement of If you are writing a lot of code, you are probably doing something wrong? Lets look at the help file for cached updates. At the very bottom of the window is the following hint:

Note: Instead of using cached updates, applications can obtain the same benefits with greater control by using a client dataset and a provider component.

Since we are using the Enterprise version, we can use MIDAS. MIDAS contains TClientDataSets and TProviders (In Delphi 5, this is replaced by TDataSetProviders).

TClientDataSets are in memory tables that are nearly identical to TTables. They support Keys, Indices, master/detail relations and all server constraints. They are an ideal replacement for cached updates.

Open the TClientDataSet_1 example.

As you will note, there are several component changes. Data flows from the TQuery, through the TProvider, and ends up in the TClientDataset. Other changes of note:

• The master/detail relationship is kept at the TQuery level.
• There are primary keys defined for both the master and detail.
• The open statements are directed at the ClientDataSet rather than TQuery.
• The detail ClientDataSet is linked to the master by DataSource and the query parameter.
• The Apply update statement has changed. ClientDataSets require a parameter. It defines the number of errors allowed before aborting the update process.

Run the application. Note the previous problems are now fixed:

• New detail records now get the master value.
• Detail records will not disappear.
• Changes to the master do not affect detail records (other than primary key changes).
• Since each of the ClientDataSets have a key, all records are sorted correctly.

As an added benefit, the amount of SQL statements have been reduced (as per the SQL monitor).

TClientDataSet_2  Index Fun

Lets have some fun and give the user the ability to change the master sort on the fly. First we need to define an index for the VendorName and Location fields:

• Select the master ClientDataSet.
• Select the IndexDefs property and open it up.
• Note that there is already a PK_VENDOR primary key listed.
• Add an index called IDX_VENDORNAME and use VENDORNAME for the Fields property.
• Create another index called IDX_LOCATION and use LOCATION for the Fields property.
• Close the index editor dialog.

We want the index to change when the user clicks on the title bar of the master grid:

• Choose the master grid.
• Click on the Events Page and double click the OnTitleClick (at the bottom).
• Enter the following code:
try
ClientDataSet1.IndexName := 'IDX_'+Column.FieldName;
except
on EDataBaseError do
ClientDataSet1.IndexName := 'PK_VENDOR';
end;

The correct index will be selected by using the Field Name that the user clicked on. If an index does not exist for the field name, an exception is raised. The exception is captured in the Try/Except portion, and the index is reset to the Primary key. Not rocket science, but it does require less code than several if statements.

Open the SQL monitor and run the application. Look at the last reference number in the SQL log. Now start changing indices by clicking on the title bar of the master table. Since this is a in memory table, it does not need to go out and re-issue the query, however, if you move the master record, it still must go out and query the detail records.

TClientDataSet_3  Nested Tables

One of the things that I have not discussed is Nested Tables. From the help file :

A nested table component provides access to data in a nested dataset of a table. The NestedDataSet property of a persistent nested dataset field contains a reference to the nested dataset. Since TNestedDataSet descends from TBDEDataSet, a nested table inherits BDE functionality, and so uses the Borland Database Engine (BDE) to access the nested table data. A nested table provides much of the functionality of a table component, but the data it accesses is stored in a nested table.

To demonstrate what this means, open the TClientDataSet_3 example. The major difference between this and the previous example is that there is only one TProvider. That provider is connected to the master table. The detail TQuery is still connected to the master by a DataSource.

Double click on the ClientDataSet to open the fields editor. All of the master fields are listed, along with a new field called query2. This field is actually a nested dataset. The best part about this is that it required no work  all I had to do was:

• Open both TQueries.
• Double click the ClientDataSet.
• Add all fields in the field editor.

Other changes:

• Only ClientDataSet is opened.
• Apply the changes to the same dataset.
• The TProvider had the following parameters changed in its options:
• poCascadeDeletes = True.
• poCascadeUpdates=True.

Run the application. Look at the SQL monitor when moving from record to record. No statements! The master is replicated in our application on the Open statement. Since there are no other records to fetch, we move around these without re-fetching data.

But where are the details records?

• Move to the end of the record.
• Select the field called query2.
• Double click this field.
• Now click on the combo box button.





The details are fetched on demand  meaning the query for the detail records will only execute when this dialog box is open.

Other things that nested tables fixed:

• Deleting the master automatically deletes the details (if the poCascadeDelete is set to true in the TProvider).
• Key fields are update automatically for details if (the poCascadeUpdates is set to true in the TProvider).
• Records are still sorted.

We have almost designed optimized, right sized master/detail application.

Optimized_BDE  Best Practice

From the previous examples we have learned:

• TTables are not optimized for client/server applications.
• Live Queries are not much better than TTables.
• TUpdateSQL is nothing more than customizable Live Queries (as far as updating goes), but is more flexible.
• TClientDataSets are a better solution the TUpdateSQL.
• Master/detail relations should not be displayed in two grids.

What we need to do is separate out the selection of the master record from the editing of it and its details. We also need to get the user to try and give us a little help in reducing the number of records returned from the server. We also need to the give the user a flexible editing environment.



Open the Optimized_BDE application now. Several things have changed:

• There are several forms rather than one single form.
• The TQueries have moved to the DataModule.
• There is only one TProvider, but two TClientDataSets.
• There is considerably more code.
• The code for the master select query is created at runtime.
• No grid for the master record, only a single master record can be edited.
• The detail grid can be sorted by clicking on the TitleBar.
• The edit window is created on the fly and destroyed when closed.
• Several records can be edited at a time.



The only thing really different from the previous example is the way the detail records are handled. The detail records are actually held in a Nested Dataset. Rather than have the user double click to get the button to click to show the details, you can use a TClientDataSet. All you have to do is set the DataSetField to the nested tables name. It can then be edited in a custom grid. Caution : I have found that the poCascadeDelete and poCascadeUpdate fields have no bearing when editing data in this matter  that is why there is code to delete the details in the BeforeDelete event on the master.



There is also enhanced error checking. C++Builder and Delphi have a Reconcile Error dialog that can be added to any application. This is done by:

• Select File|New
• Select Dialogs
• Select Reconcile Error Dialog
• Add a line of code in the ClientDataSets OnReconcileError event :

Action := HandleReconcileError(DataSet, UpdateKind, E);

• Do not allow the form to be auto created with the Options dialog.

Instant error handling!

The best thing about this version of the application is the amount of SQL codes that are generated. Open the SQL monitor then run the application. We reduce the amount of SQL activity by trying to get the user to help us with the type of information they are looking for via the Select box. The smaller the result set, the faster the SQL can return the results. Once the user selects the record, we only have to go out and grab the full master record and its details. Very fast, very compact, very optimized.

Take a look at the rest of this application for more optimizing tips.

General Design Issues

1. REMBMER: If you are writing a lot of code to display data, you are probably doing something wrong.
   
2. If you do not have Delphi Client/Server or C++Builder Enterprise, get it. Dont pass go, dont try and save money. The MIDAS components are ONLY available in these versions, and they are a necessity for doing clean, well designed client/server and multi tier applications.
   
3. Normalize your data, but know when to break the rules. Normalization is beyond the scope of this session. There are several good references on Database normalization on the web and at most bookstores.
   
4. Know the deployment platform.
   
5. Know the locking mechanism. SQL 6.x / Oracle / and Interbase all handle locking differently (Interbase does not do any locking). It is possible to lock yourself out of any updates.
   

General Database Guidelines

1. Use a Main DataModule. It is much easier to reuse a default Main DataModule among different applications.
   
2. Use a TSession. Every application has a default TSession, but I prefer to define one anyway. If you do not define the NetFileDir and PrivateDir, it will use your current BDE settings. One of the reasons I like to use a TSession is that I do not have to guess where the Private directory is, and I can test different disaster results when the NetFileDir is not set to other workstations on the network. Never hope that the BDE is configured right  you never know what users may do. Additional TSessions can be defined at runtime for multi-threading. Make sure you remember to set the SessionName property on EVERY Dataset in the application.
   
3. Use a TDatabase. Once again, never rely on the BDE having the proper settings. BDE aliases are great for design time, but you should define the Database parameters at runtime. Quick Tip: Almost all of the TDatabase properties can be changed by double clicking on the TDatabase Component. Use the Defaults button to open a current Alias, then copy it. Next, set the Driver name to the right driver, then paste the Params back into the Parameter overrides.

Local Table Guidelines (Paradox and dBase) - Single Tier.

1. Put your TTables and TQueries on the form in which they are used instead of a separate DataModule. This gets around the problem of all forms pointing at the same data. There are programmatic ways around this, but too hard to test and debug.
2. Never use a TQuery except for reporting. There are too many performance problems with using a TQuery for anything other than reporting.
3. Learn to use indices judiciously on TTables  using too many or too few will slow down your app.
4. SetRange is your friend. With SetRange, you can restrict the data that is shown much faster than any query.
5. Avoid Filters unless the DataSet is small. A filter is done on the fly, and may or may not use the current index.
6. If you have the Client/Server or Enterprise versions, use the MIDAS TClientDataset / Providers. Indexing on the fly is impossible with TTables, but easy with TClientDatasets.
7. Use persistent fields when possible. They make assigning data much easier.

Client/Server Guidelines (SQL Based apps)  Single Tier.

1. Forget everything you learned about Local Tables. The techniques will most likely fail.
2. Use TQueries for everything. TTables are deceptive  they appear to work well until the DataSet gets large, or you have a few users.
3. Avoid cached updates. How? See below.
4. Use the TClientDataset/DataProvider combination to display data. Much easier to use than Cached Updates, and more flexible.
5. Copy lookup table data to local TClientDatasets. Use some sort of library technique for controlling updates to the lookups. Obviously this only works for small, static amounts of records.
6. Use the DataProviders OnUpdateData to control the posting of data back to the server.
7. Use Views whenever possible. Some backends allow for updateable Views.
8. Do as much processing on the server as possible. Use stored procedures to process updates/deletes and for reporting.
9. Use persistent fields when possible. They make assigning data much easier.
   
10. Use the SQL Monitor to monitor the SQL statements that the BDE is processing.

Conclusion

Hopefully you now understand the options available to a Delphi or C++Builder programmer when using the BDE. With MIDAS components, any type of Database application is possible  from briefcase models to complex client/server applications. By using sound development techniques, any application can be moved to the right-size.

Other references on Delphi, MIDAS, and Interbase:

Dan Miser (TeamB). He is the expert on doing ClientDataSet applications. www.execpc.com/~dmiser
Charlie Calverts book  Delphi 4 Unleashed
Borland Documentation
Borland Newsgroups news.Borland.com