Sahana Database Schema Convention

/**
* The central table on a person, with their associated names
* Modules: dvr, mpr, rms, or, cms
* Last changed: 27-OCT-2005 - chamindra@opensource.lk
*/
DROP TABLE IF EXISTS person_uuid;
CREATE TABLE person_uuid (       -- the list of universally unique IDs for all people in the system
    p_uuid VARCHAR(60) NOT NULL, -- universally unique person id
    full_name VARCHAR(100),      -- the full name (contains the family name)
    family_name VARCHAR(50),     -- the family name
    l10n_name VARCHAR(100),      -- localized version of name
    custom_name VARCHAR(50),     -- extra name field as required
    PRIMARY KEY(p_uuid)
);

• All table and field names in lower case
• table names for explicit use by modules have to be prefixed with the module abbreviation. e.g.:

CREATE TABLE cmp_details (  -- camp details table

• All core sahana tables to be prefixed by sys_ . e.g.:

CREATE TABLE sys_config (  -- Sahana base config table

• provide a description of the table, with modules that use it and when it was last changed (+ email of who)

/**
* The central table on a person, with their associated names
* Modules: dvr, mpr, rms, or, cms
* Last changed: 27-OCT-2005 - chamindra@opensource.lk
*/

• provide a description of each field

CREATE TABLE sys_person_uuid (   -- the list of universally unique IDs for all people in the system
    p_uuid VARCHAR(60) NOT NULL, -- universally unique person id
    full_name VARCHAR(100),      -- the full name (contains the family name)

• use UUID for tables that contain data that will be syncrosnized across multiple Sahana instances to avoid conflicts

• Guidelines naming tables and fields common
  • Keep it short
  • Not more than 3 words
  • Use underscore if it is necessary to break the words. e.g actionlog_level, user_logid
  • Avoid acronym as much as possible

• Define inside the create table statement.
• Write in a new line rather than with the field definition.

CREATE TABLE person_uuid (       -- the list of universally unique IDs for all people in the system
    p_uuid VARCHAR(60) NOT NULL, -- universally unique person id
    full_name VARCHAR(100),      -- the full name (contains the family name)
    family_name VARCHAR(50),     -- the family name
    l10n_name VARCHAR(100),      -- localized version of name
    custom_name VARCHAR(50),     -- extra name field as required
    PRIMARY KEY(p_uuid)
);

• Define inside the create table statement.
• Write in a new line rather than with the field definition.

CREATE TABLE sys_config(
    config_id BIGSERIAL,          -- configuration id
    module_id VARCHAR(20),        -- the directory name of the module e.g. dvr, or, mpr
    confkey VARCHAR(50) NOT NULL, -- the configuration key for the module
    VALUE VARCHAR(100),           -- the value 
    PRIMARY KEY(config_id),
    FOREIGN KEY (module_id) REFERENCES modules (module_id) ON DELETE CASCADE
);

• Indexes
  • Use CREATE INDEX statement to write indexes i.e. not inside the table definition
  • Write the statement immediately after the table definition
  • Index nameing should follow <table name>_<field>_idx

CREATE TABLE sys_config(
    config_id BIGSERIAL,          -- configuration id
    module_id VARCHAR(20),        -- the directory name of the module e.g. dvr, or, mpr
    confkey VARCHAR(50) NOT NULL, -- the configuration key for the module
    VALUE VARCHAR(100),           -- the value 
    PRIMARY KEY(config_id),
    FOREIGN KEY (module_id) REFERENCES modules (module_id) ON DELETE CASCADE
);
 
CREATE INDEX sys_config_confkey_idx ON sys_config(confkey);
CREATE INDEX sys_config_value_idx ON sys_config(VALUE);

The tables are used to generate the dynamic reports and in order to collect the table in formation , we go through all the dbcreate.sql files and collect table information such as the nice names of the table as well as fields. So if anybody needs not to add his or her table into the dynamic reporting, just add “no reporting……“ as follow

/*
* anything you want but keep the space between * and the words
 
*/
 
-- no reporting for this table
 
CREATE TABLE sys_config(
    config_id BIGSERIAL,          -- configuration id
    module_id VARCHAR(20),        -- the directory name of the module e.g. dvr, or, mpr
    confkey VARCHAR(50) NOT NULL, -- the configuration key for the module
    VALUE VARCHAR(100),           -- the value 
    PRIMARY KEY(config_id),
    FOREIGN KEY (module_id) REFERENCES modules (module_id) ON DELETE CASCADE
);
 
CREATE INDEX sys_config_confkey_idx ON sys_config(confkey);
CREATE INDEX sys_config_value_idx ON sys_config(VALUE);

It doesn't matter what you type after “no ” but make sure to keep the proper spacing between ”–” and “no” .Furthermore, it is much convenience to collect those mata date, if the developers keep proper spacing between each component (such as between words,“*” marks and words) of the comments. Finally, please try provide nice names to table and field names as far as you can.