Difference between revisions of "Database patterns"
| Line 36: | Line 36: | ||
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|DBT_ASCII_BIN]] or [[#DBT_UTF8_BIN|DBT_UTF8_BIN]]. | 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|DBT_ASCII_BIN]] or [[#DBT_UTF8_BIN|DBT_UTF8_BIN]]. | ||
| + | |||
| + | == particle == | ||
| + | |||
| + | The [[#particle|particle]] tables contain particles. A particle table maps a wide column to a narrow column. | ||
| + | |||
| + | A particle table is named 't_particle_{name}'. | ||
| + | |||
| + | A particle table must contain a 'a_{name}' column which is the wide value. When particle 'a_{name}' columns are strings they should be case-sensitive. If you want a case-insensitive string use a [[#name|name]] table instead. | ||
| + | |||
| + | A particle table must contain a 'a_{name}_id' column which is the narrow value. | ||
| + | |||
| + | == string == | ||
| + | |||
| + | The [[#string|string]] tables contain strings. A string table maps a wide column to a narrow column. | ||
| + | |||
| + | A string table is named 't_string_{name}'. | ||
| + | |||
| + | A string table must contain a 'a_{name}' column which is the wide value. The 'a_{name}' column should be case-insensitive. Case-sensitivity is ensured by the 'a_{name}_hash_bin' column, so the 'a_{name}' column can use a case-insensitive collation. | ||
| + | |||
| + | A string table must contain a 'a_{name}_hash_bin' column which contains the DBT_HASH_BIN binary hash of the string value. | ||
| + | |||
| + | A string table must contain a 'a_{name}_id' column which is the narrow value. | ||
= Database types = | = Database types = | ||
Revision as of 07:28, 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.
particle
The particle tables contain particles. A particle table maps a wide column to a narrow column.
A particle table is named 't_particle_{name}'.
A particle table must contain a 'a_{name}' column which is the wide value. When particle 'a_{name}' columns are strings they should be case-sensitive. If you want a case-insensitive string use a name table instead.
A particle table must contain a 'a_{name}_id' column which is the narrow value.
string
The string tables contain strings. A string table maps a wide column to a narrow column.
A string table is named 't_string_{name}'.
A string table must contain a 'a_{name}' column which is the wide value. The 'a_{name}' column should be case-insensitive. Case-sensitivity is ensured by the 'a_{name}_hash_bin' column, so the 'a_{name}' column can use a case-insensitive collation.
A string table must contain a 'a_{name}_hash_bin' column which contains the DBT_HASH_BIN binary hash of the string value.
A string table must contain a 'a_{name}_id' column which is the narrow value.
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.
