Database Layer

The database will need to be an ACID compliant database. This means that the data is safe and will always be in a consistent form. The first version of the system will be written for MySQL using INNODB database engine (or it's derivatives such as Drizzle or MariaDB).

There will be a thin layer over the database calls. This thin layer will handle the peculiarities of each database driver, and will also handle the case where there is a database cache layer if that's present. This thin layer can be extended or inherited to handle the different database drivers.

The database will be a “traditional” relational model. As much as possible there will be foreign keys to define the relationships, and there will be indexes to support fast selects. There will be consistent types for each of the different pieces of data. There will be a strict naming convention that must be followed. Some of the details will be:

  • Tables: <module_name>_table_names – prefixed with the module name and then words separated by underscores. Table names will be plural.
  • Stored Procedures: spStoredProcedureName – prefixed with “sp” and then camel case for the real stored procedure name.
  • Views: vwViewName – prefixed with “vw” and then camel case for the real view name.
  • Functions: fnFunctionName – prefixed with “fn” and then camel case for the real function name.
  • Internal Primary Key: system_autoid  – this is the real table name with an “ID” suffixed on the end.
  • External Unique ID: table_uuid  – this is the real table name with an “UUID” suffixed on the end.
  • ColumnName: column_name – this is the the column name spearated by underscores. No contractions or abbreviations.

Internally the database will use long integers (64 bit integers, BIGINT) for all internal keys. The UUID will be used for synchronisation with other Butr systems. The UUID function in both Node.js, MySQL and PostgreSQL return compatible UUID strings. The format for a UUID is: 6ccd780c-baba-1026-9564-0040f4311e29. All references to identifiers will be through UUIDs.

There will be a lot of auditing. It will audit discreet before and after values, and a transaction identifier. This transaction identifier will be used to roll-back or undo. There will be a limit of a number of transactions that can be rolled back. This will be quite hard, and will require probably undo routines for all the modify/add/delete commands.

All interaction with the database will be done through Stored Procedures. This is mostly without exception (the Report Module, Document Module, and Export Module are the only exceptions). The database should be designed to be sharded into many servers. This will be divided up based on traffic and logical breaks in the system. This will be fleshed out as the system is developed.

Reporting would be done against a replica database. This would be done using hooks as the UUID does not replicate on statement based replication (but might with binary log replication).

Lookup tables will reside in the configuration module. They should follow the following guidelines: www.simple-talk.com/sql/t-sql-programming/look-up-tables-in-sql-/.

There are two placeholder tokens which are used to help with caching of database calls. If the parameter is a SessionUuid and you want the database call to be cached for all sessions then you prepend @i_SessionUuid= before the actual parameter. If the parameter is a TransactionUuid and you want the database call to be cached for all transactions in the given session then you prepend the @i_TransactionUuid before the actual parameter.