Re: DDL Files - Tables and keys

- If the table can have a primary key - assign one. Very few (with the
exception of perhaps a log table) can't.
- If the table references another table, like order header to order line,
use referential constraints.
- If the table has certain constraints, like active record code can only
be 'A' or 'I', then assign check constraints. Consider using '1' for
active and '0' for inactive for international reasons. You can still keep
the file a simple character field in case the list of possible values
expands.
- Use real date fields.
- If there's a summary field in a header file based on the sum of a field
in a related table, like on order in the header file is the sum of the on
order in the detail file, then consider putting a trigger on the detail
file to keep that in sync.
- If you're going to have a never ending program that checks for
new/changed records and performs an action then scrap that idea and use a
trigger.
- If there's a field for order number, that means the same, in more than
one file, then keep them the same name. For example, ORDERNO in the order
header and the order detail. Drop the silly prefixes in the field names.
Remember, RPG (if you're using that) supports PREFIX and QUALIFIED for
about the last decade or so. This will allow SYSCOLUMNS to be meaningful.
- Keep international stuff in mind. Don't call the field ORDER#. Use
ORDERNO instead. # often doesn't translate well.
- Don't use reserved words for field names. For example, don't call a
field DESC. That often means DESCending and when you use that field you
will have to put double quotes around it.
- Don't put periods in library, table or column names. That was an old
S/36 technique to group files and it confuses people when they have to put
double quotes around the names.
- SYSCOLUMNS and/or QADBIFLD is your FREF file. Granted, one might have
to create a different fref to initially populate those other files.

Rob Berendt