Databases and Applications - Designing for Upsizing

• Introduction
• The Database
• The Delphi Application
• see also:
  • Delphi Programming Main Index
  • Relational database management systems
  • Database systems
  • Delphi database programming 1
  • Delphi database programming 2
  • Database access solutions
  • Client Server middleware tools
  • Two-tier client server
  • Multi-tiered client server
  • Database systems
  • Delphi data validation

Introduction:

• Design for ease in Upsizing or changing databases:
  • If there is any chance at all that your new application may have its database converted to a different one or if the application itself may need to be converted from single tier to multi-tier, then you should strive to ensure that there will be minimal incompatibilities and minimal recoding necessary.
• Design for understandability:
  • In addition, if future changes need to be made, then it is wise to ensure your naming and coding is logical and easily understood, and hopefully well documented with adequate commenting of what the code does.
• Design for speed and minimal database size:
  • careful database structuring to ensure the appropriate size fields are used and that the database is appropriately normalised will assist greatly in ensuring maximum speed and minimum size requirements.
  • clever programming of the application can also facilitate this but it cannot be understated how important the database structure design stage is!

Here are a few pointers to achieving this.

The Database:

• Table names:
  • If you are using a file-based database like Paradox or dBase, then ensure the Tablename property of your Tdataset components do not include the tablename's extension (ie. the .db or .dbf part) and likewise don't include these in code when assigning the property. Databases such as Access or SQL servers do not allow such naming. If you do this and you use a TTable, you need to set the TableType property to ttParadox or ttDbase as appropriate, but when using an SQL statement the extension is not needed anyway.
• Index names:
  • All indexes on an SQL server must be named unlike the primary key for a Paradox table.
  • Thus always specify a Paradox primary key as a null string for its indexName in your Delphi app.
• Column names:
  • avoid spaces and punctuation characters, only use letters, numbers & the underscore character
    • NB. in addition, hyphenated names cause problems in QuickReport expressions!
  • avoid key words such as "Date"
  • try using a uniform naming convention such as:
    • 1st 2 letters are an abbreviation for the tablename, then underscore
    • if field is primary key then consider a name such as "SEQU" if it is an integer field
• Field types:
  • Note that Access date or time fields are regarded as DateTime by Delphi!
  • By default, when adding a string field to an Access database it makes it 255 chars, ensure this is changed to the largest that you will actually need to avoid making the table excessively large and slow!
  • Auto-incrementing fields are not always supported so may be best to have primary key as a long integer and manually determine the next highest value when adding a record.
  • Avoid other not well supported data types such as:
    • SQL Anywhere does not support NChar whereas Sybase System 11 does
    •  
• SQL server features:
  • consider using a manual more compatible method rather than using stored procedures, triggers, foreign keys, etc where their implementation may differ between the SQL servers.
  • try to use ANSI SQL where possible, avoiding proprietary syntax such as:
    • SQL Server : "CUBE", "ROLLUP"
    • ACCESS: "CROSSTAB", uses * as wildcard instead of usual SQL wildcard % - can't avoid this!
    • SYBASE: "ROWCNT()", uses *= syntax for left outer joins
  • user names:
    • Sybase System 11 insists all objects are owned by user "dbo"

The Delphi Application:

• Use a Tdatabase component in your application:
  • this will make migration to other databases much easier, just by changing its aliasname and its parameters, as well as providing a few very useful methods such as transaction control, password handling etc.
  • use the TDatabase.databasename as your Tdataset.database property rather than a BDE alias.
• Place your non-visual dataset components (apart from TDatasource) on datamodules:
  • if using TClientDatasets place these and their TdatasetProviders on a different datamodule to the Tdatasets and the TDatabase components which they connect to. It is then easy to create a server application and a client application later if need be.
  • don't use TProvider as they are not supported in Delphi5
•