Difference between revisions of "Database patterns"
Line 50: | Line 50: | ||
A [[#DBT_TOKEN|DBT_TOKEN]] is a case-sensitive 32-character alphanumeric ASCII string. | A [[#DBT_TOKEN|DBT_TOKEN]] is a case-sensitive 32-character alphanumeric ASCII string. | ||
+ | |||
+ | == DBT_CREATED_ON == | ||
+ | |||
+ | A [[#DBT_CREATED_ON|DBT_CREATED_ON]] is the timestamp of when the record was created. | ||
+ | |||
+ | == DBT_UPDATED_ON == | ||
+ | |||
+ | A [[#DBT_UPDATED_ON|DBT_UPDATED_ON]] is the timestamp of when the record was last updated. | ||
+ | |||
+ | = Standards = | ||
+ | |||
+ | As standard all database tables should finish with DBT_CREATED_ON and DBT_UPDATED_ON columns. Even for insert-only data. The logic being that if insert-only data is modified then the DBT_CREATED_ON !== DBT_UPDATED_ON discrepancy might help reveal the issue. |
Revision as of 08:20, 18 March 2020
Patterns
about
The about tables contain database meta data.
An about table is named 't_about_{name}'.
lookup
The lookup tables contain hard-coded reference data.
A lookup table is named 't_lookup_{name}'.
A lookup table must contain a 'a_{name}_enum' column which identifies a row. The 'a_{name}_enum' column is usually be a DBT_UINT8.
If an optional 'a_{name}_char' column exists it must be of type DBT_ASCII_CHAR_BIN. The PHPBOM library will use lowercase char codes and applications can use uppercase char codes.
If you're extending a PHPBOM lookup table you should code from the max enum down.
token
The token tables contain tokens.
A token table is named 't_token_{name}'.
A token table must contain a 'a_{name}' column which contains the token. The 'a_{name}' column is usually a DBT_TOKEN.
name
The name tables contain names.
A name table is named 't_name_{name}'.
A name table must contain a 'a_{name}' column which contains the case-insensitive name. The 'a_{name}' column is usually a DBT_ASCII_CI or DBT_UTF8_CI.
A name table must contain a 'a_{name}_lower' column which contains the case-sensitive lowercase name. The 'a_{name}_lower' column is usually a DBT_ASCII_BIN or DBT_UTF8_BIN.
Database types
DBT_UINT8
A DBT_UINT8 is an integer from 0 to 255.
DBT_ASCII_CHAR_BIN
A DBT_ASCII_CHAR_BIN is a single case-sensitive ASCII character.
DBT_TOKEN
A DBT_TOKEN is a case-sensitive 32-character alphanumeric ASCII string.
DBT_CREATED_ON
A DBT_CREATED_ON is the timestamp of when the record was created.
DBT_UPDATED_ON
A DBT_UPDATED_ON is the timestamp of when the record was last updated.
Standards
As standard all database tables should finish with DBT_CREATED_ON and DBT_UPDATED_ON columns. Even for insert-only data. The logic being that if insert-only data is modified then the DBT_CREATED_ON !== DBT_UPDATED_ON discrepancy might help reveal the issue.