Database patterns

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.

The 'a_{name}_enum' must be the primary key.

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 as PHPBOM will code from the min enum up.

token

The token tables contain tokens.

A token table is named 't_token_{name}'.

A token table must contain a 'a_{name}_id' column which contains the ID for the token. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

The 'a_{name}_id' column must be the primary key.

A token table must contain a 'a_{name}' column which contains the token. The 'a_{name}' column is usually a DBT_TOKEN.

There should be a unique index on the 'a_{name}' column.

name

The name tables contain names. Names are case-insensitive strings.

A name table is named 't_name_{name}'.

A name table must contain a 'a_{name}_id' column which contains the ID for the name. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

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}_id' column which contains the narrow ID for the particle. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

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.

string

The string tables contain strings. A string table maps a wide column to a narrow column. You would usually use a string table if a particle table is too small.

A string table is named 't_string_{name}'.

A string table must contain a 'a_{name}_id' column which contains the narrow ID for the string. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

A string table must contain a 'a_{name}' column which is the wide string 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.

jzon

The jzon tables contain compressed JSON.

A jzon table is named 't_jzon_{name}'.

A jzon table must contain a 'a_{name}_id' column which contains the narrow ID for the compressed data. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

A jzon table must contain a 'a_{name}' column which is the compressed binary data. The 'a_{name}' column is usually a DBT_MLOB.

A jzon table must contain a 'a_{name}_hash_bin' column which contains the DBT_HASH_BIN binary hash of the compressed data.

zlob

The zlob tables contain compressed blobs.

A zlob table is named 't_zlob_{name}'.

A zlob table must contain a 'a_{name}_id' column which contains the narrow ID for the compressed blob. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

A zlob table must contain a 'a_{name}' column which is the compressed binary data. The 'a_{name}' column is usually a DBT_MLOB.

A zlob table must contain a 'a_{name}_hash_bin' column which contains the DBT_HASH_BIN binary hash of the compressed blob.

structure

The structure tables contain structures. A structure table maps a wide column to a narrow column and constituent component columns.

A structure table is named 't_structure_{name}'.

A structure table must contain a 'a_{name}_id' column which contains the narrow ID for the structure. The 'a_{name}_id' column is usually a DBT_ID24 or DBT_ID32.

A structure table must contain a 'a_{name}' column which is the wide string 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 structure table must contain a 'a_{name}_hash_bin' column which contains the DBT_HASH_BIN binary hash of the wide string value.

Other component columns will exist. For example for a 't_structure_email_address' there will be 'a_email_address_mailbox' and 'a_email_address_host' columns.

upload

The upload tables contain data about uploaded files.

A upload table is named 't_upload_{name}'.

An upload table has 'a_{name}_id', 'a_{name}_hash_bin', 'a_{name}_file_name', 'a_{name}_file_type' and 'a_file_type_enum' columns. Usually the actual file will be stored in the file-system using the 'a_{name}_hash_hex' value for the file name.

ident

The ident tables allocate unique identifiers.

An ident table is named 't_ident_{name}'.

An ident table has a 'a_{name}_id' column which is usually a DBT_ID24 or DBT_ID32.

data

The data tables contain mutable application data. All data tables have a corresponding hist table.

A data table is named 't_data_{name}'.

A data table has a 'a_{name}_id' column which is is usually a DBT_ID24 or DBT_ID32.

A data table has a 'a_{name}_version' column which is usually a reference to t_ident_interaction.a_interaction_id.

A data table will have other columns too.

hist

The hist tables contain logs of changes to mutable application data stored in data tables. All hist tables have a corresponding data table.

A hist table is named 't_hist_{name}'.

A hist table has a 'a_{name}_hist_id' primary key which is is usually a DBT_ID32.

A hist table has a 'a_interaction_id' column, which is a reference to the t_ident_interaction.a_interaction_id in which the data was modified.

A hist table has a 'a_crud_enum' column which is a reference to the t_lookup_crud.a_crud_enum that indicates the CRUD operation which affected the data.

A hist table then contains a copy of all of the columns from its corresponding data table.

Database types

DBT_ID8

A DBT_ID8 is an auto generated integer from 1 to 255.

DBT_ID16

A DBT_ID16 is an auto generated integer from 1 to 65,535.

DBT_ID24

A DBT_ID24 is an auto generated integer from 1 to 16,777,215.

DBT_ID32

A DBT_ID32 is an auto generated integer from 1 to 4,294,967,295.

DBT_ID64

A DBT_ID64 is an auto generated integer from 1 to 2⁶⁴-1.

DBT_UINT8

A DBT_UINT8 is an integer from 0 to 255.

DBT_UINT16

A DBT_UINT16 is an integer from 1 to 65,535.

DBT_UINT24

A DBT_UINT24 is an integer from 1 to 16,777,215.

DBT_UINT32

A DBT_UINT32 is an integer from 1 to 4,294,967,295.

DBT_UINT64

A DBT_UINT64 is an integer from 1 to 2⁶⁴-1.

DBT_ASCII_CHAR_BIN

A DBT_ASCII_CHAR_BIN is a single case-sensitive ASCII character.

DBT_HASH_BIN

A DBT_HASH_BIN is a case-sensitive 28-byte binary string containing a SHA512/224 hash value.

DBT_HASH_HEX

A DBT_HASH_HEX is a case-insensitive 56-character hexadecimal string containing a SHA512/224 hash value.

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.