Conventions and Standards

Database

Auditing and history

• All tables must contain the following columns:
  • create_user_id (varchar(32))
  • create_utc_timestamp (timestamp)
  • update_user_id (varchar(32))
  • update_utc_timestamp (timestamp)
• All tables containing user entered data should have a corresponding history table name <table_name>_h.
• All tables containing user entered data should have an audit history trigger that ensures the audit columns are written and the history table is populated.
• All code/lookup tables should have an audit trigger that ensures the audit columns are written.

Code/Lookup Tables

• Must be suffixed with _code
• Should only contain the following columns, however one or two additional columns are required in order to support application logic (e.g. the manually_assignable_ind flag was added to the complaint_status_code table to indicate which status are only driven by application logic)
  • <table_name> (Primary Key) (varchar(10))
  • short_description (varchar(50))
  • long_description (varchar(250))
  • display_order (int(4))
  • active_ind (boolean(1)
  • standard audit columns
• Display order must be initially set in increments of 10 to allow for ease of adding subsequent data entries.

Cross Reference Tables

• Cross Reference Tables should be named <table_name>_<table_name>_xref. In some cases a shorter name can be used so long as it's still clear which table is being referred to.
• Should only contain the following columns, however additional columns are permitted if required;
  • <table_name>_guid (Primary key)
  • <foreign_key to table 1>
  • <foreign_key to table 2>
  • standard audit columns

Additional standards

• Primary keys for user entered data must be guids and named <table_name>_guid
• If a column is referencing a Primary Key in another schema or database the column name should be suffixed with _ref

Source Code

GQL Standards

• Functions (Queries and Mutations), types and inputs should all be in their own separate files
• Queries should be named using Nouns, singular form for queries that return a single record (e.g. Investigation) and plural for queries that return many records (e.g. Investigations).
• Mutations should be named (e.g. createInvestigation, addContravention)

Further Guidance

• NRM Data Modelling Standards