Wiki Sumber Informasi iSIKHNAS

New table creation

Technical guide

New tables are created manually by administrators or iSIKHNAS technical champions using either direct server shell access and psql, or PGAdmin on a remote computer.

In either case you will need access passwords and appropriate permissions for table creation. This example uses PGAdmin.

1. Compose your table creation statement using a text editor, based on the standard fields and naming conventions shown below.
2. Open PGAdmin and log on to the correct iSIKHNAS database. Possible databases are:
   1. Crashtest - this is the first one you should use. It is the development server and is reconfigured every night (so all changes are lost). Use this to make sure your table creation script is working
   2. Training (Sidecar) - the training server is reconfigured every week. Use this for longer term testing, once you think you've got it right
   3. Live - Once you are sure of the table structure, create it on the live system. it will then be automatically copied to Crashtest (every night) or Sidecar (every Monday).
3. Paste your table creation SQL script into an SQL window and run the script
4. Set permissions Important
   1. Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT.
5. Test the table

Standard fields

Every table in the database has a number of standard fields, which are used for consistency and for data auditing purposes. You may only omit or alter these standard fields if you have a very very good reason.

CREATE TABLE ages (
  id serial primary key,
  ...data fields here...
  createdby integer NOT NULL REFERENCES users,
  createdon timestamp NOT NULL DEFAULT now(),
  modifiedby integer REFERENCES users,
  modifiedon timestamp without time zone,
  del boolean NOT NULL DEFAULT false
);

The required standard fields are:

• id - the unique record id and primary key.
• createdby - the id of the user creating the record. This must be explicitly included in any INSERT statements
• createdon - time stamp for creation - automatically inserted
• modifiedby - user id for UPDATE - must be explicitly included
• modifiedon - timestamp for UPDATE - use now()
• del - the 'deleted' flag. iSIKHNAS never uses DELETE statements, but instead simply flags records as deleted, so they can be referenced for historical purposes or undeleted if required.

Table and field naming standards

Schemas

There are a number of schemas in the database for different purposes. Most tables should be created in the following schemas:

• data - all routine data tables (i.e. tables containing data submitted by users)
• reference - look up tables
• lab - data tables for the lab system

The other schemas are for specialised purposes

• backoffice - web and database administrative tables
• metadata - data defining table, field, spreadsheet, structures etc
• sms - SMS system management
• reports - output report definitions
• public - not use, except for functions from imported libraries

Table names

Tables may be named in English or Indonesian. Core tables developed by the original team have been named in English, but later tables developed by champions can be named in Indonesian.

Tables are named in all lowercase. Multiple words are combined without spaces or underscores. (This rule has been applied inconsistently by some developers less familiar with the standards but should be adhered to for future table creation)

The name of the table represents the elements contained in each record, expressed as a plural (in English). Link tables are use the combined names of the two tables they are linking.

Table names should be unique in the entire database (even though tables with the same name are permitted in different schema.

Fields

Fields are named all lowercase with no spaces, with the name reflecting concisely the content.

Reference fields

Reference fields are named based on the target table followed by 'id' with no space or underscore. So to reference the id field of the user table, the reference field would be userid.

All reference fields are of type integer except in unusual circumstances where a very large number of records are expected, when a bigint is used.

Examples

See the automated list of database tables for examples. Note the displayed format is not exactly the same as the recommended table definition format in all cases (e.g., the use of 'serial' and references).

Reference table=

The ageunits table is an example of a simple reference table:

CREATE TABLE ageunits (
  id serial PRIMARY KEY,
  code character varying NOT NULL,
  name character varying[] NOT NULL,
  createdby integer NOT NULL REFERENCES users,
  createdon timestamp NOT NULL DEFAULT now(),
  modifiedby integer REFERENCES users,
  modifiedon timestamp without time zone,
  del boolean NOT NULL DEFAULT false
);