Database standards

This page covers the standards for creating and maintaining database objects

Tables and unique constraints
When defining columns on a table:
 * All columns should have a comment explaining what the column represents.
 * Comments such as PK, FK are not helpful.
 * When a field is no longer in use, make sure to add "OBSOLETE, no longer in use" in its comment.
 * If the column is a primary key of another table (Patient number, Referral, SHA episode) a foreign key constraint should be added.

Triggers
Please note the following: When creating new triggers or updating existing ones:
 * The standards have now changed for naming triggers.
 * For audit triggers, see the Auditing objects section.
 * Triggers should be named after its table with a suffix representing what the trigger does or is related to.
 * Example: the REFERRAL_DETAILS_PBR, indicates it's located in the REFERRAL_DETAILS table and is used by the PbR functionality.
 *  Exceptions : if you trigger ends up with the same name as another database object (such as tables or procedures), please add the TRG suffix.
 * Obsolete conventions : old Maracis standards where triggers would have a prefix indicating types/events (AIUD_R_) have been retired.
 * If you decide to rename an existing trigger, make sure to update the TFS file name as well.

Auditing objects
Each table should have its a matching table in the TAB_AUDIT schema and a trigger to save the data into this table.
 * The audit table should have the same name as the original table.
 * The audit table can be automatically created using the SQL located in this link.
 * The audit table must have a timestamp field to log the date of the event, called AUDIT_DATE
 * The audit table must have an system ID of the user who performed the action, called AUDIT_USER_ID
 * The audit table must have an oracle user name of the user who performed the action, called AUDIT_USER_NAME
 * The audit table must have the event type which triggered the audit for D - delete, I - insert and U - update, called AUDIT_ACTION.
 * The audit trigger should be called AUDIT_
 * The trigger can be automatically created using the SQL located in this link . For tables with more than 20 fields, please use this link instead.
 * The trigger event must be triggerd AFTER the events.
 * The trigger must cover the following events: INSERT, DELETE, UPDATE
 * The audit table should have the same name as the original table.
 * Please make sure to create an entry on the audit control table. The code example can be found in this link.
 * The trigger should always check if the table audit control is set to on or off - unless the event is a DELETE.