Difference between revisions of "Database patterns"

From ProgClub
Jump to: navigation, search
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.