and Applications - Designing for Upsizing
- Design for ease in Upsizing or changing
- 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
- 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.
- 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
- 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
- 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
- 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",
- 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
- 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