Database conventions and table descriptions
This section describes all of the available tables and standard conventions used in the SuperOffice database.
The explanations of the naming convention, the data types, and the key codes in use throughout the database are covered first.
Next, the available tables are listed. For each table, a short description is then provided against the table's name. Then each field (or column) in that table is explained in turn.
Conventions used throughout the SuperOffice Database
All tables have a primary key as their first column, called
<tablename>_id. This uniquely identifies each record entered in the table. This value will stay unchanged for the life of the database. There is one exception to this rule, and that is new records created in a Travel database. Their IDs will change when updating the parent database.
All fields referring to other records (foreign keys in relations) also have names ending with the extension _id.
All fields selecting an entry from any one of the special lists for business, company interest, and so on have names ending with the extension _idx (rather than _id). This used to be the list text table in earlier versions of SuperOffice.
Some other things to note – areas where consistency has suffered due to the way the schema was defined in earlier versions of the product.
- Email and URL have separate fields that point to
While on the other hand:
- Address and Phone have a general
owner_idand use a
type_idxto indicate table and role. There may be 2 different
type_idxvalues that indicate the same table.
Sometime in the future, this will probably be sorted out by a major upgrade to SuperOffice which creates a completely new database schema. For the time being, we are stuck with these little annoyances and inconsistencies.
Standard data types in use
|short||2||16-bit signed integer|
|long||4||32-bit signed integer|
|date_d||4||32-bit unsigned integer, number of seconds since January 1, 1970. Time part is = midnight.|
|date_t||4||32-bit unsigned integer, number of seconds since January 1, 1970|
|longid||4||32-bit unsigned integer, record ID|
|string||<256||Fixed-size array of bytes terminated by zero-byte, length includes zero-byte|
|lvstring||<4k||as string, length specifies maximum length.|
|float||8||floating-point, same as double|