New table creation

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 table permissions Important
   1. Ensure that isikhnascore and smsd have appropriate permissions on the table, usually including SELECT, UPDATE and INSERT, for any user that needs to access the table:

GRANT SELECT, UPDATE, INSERT ON TABLE tablename TO isikhnascore;
GRANT SELECT, UPDATE, INSERT ON TABLE tablename TO smsd;
GRANT SELECT ON TABLE tablename TO champion;
GRANT SELECT ON TABLE tablename TO mailer;

1. Set sequence permissions Very important
   1. Ensure that the sequence has all privileges set for any user that may INSERT records into the data table (mainly isikhnascore and smsd). This is really easy to forget and causes permission errors.

GRANT ALL ON TABLE tablename_id_seq TO isikhnascore;
GRANT ALL ON TABLE tablename_id_seq TO smsd;

1. 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 (
  -- record identifier (primary key)
  id serial primary key,
  ...data fields here...
  -- if the table is used to store data submitted by SMS
  msgid bigint not null,
  -- standard system audit fields
  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.

If the table is used to store data submitted by SMS or IM, it should also include the msgid field, which stores the message ID used to link incoming and outgoing SMS data.

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 used, 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 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 schemas).

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 users 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
);

Standard names for reference table fields include:

• name - an array of type character varying. This is used to display the Indonesian name [1] and English name [2]
• code - character varying. A short abbreviation such as may be used in SMS messages.
• hiercode - character varying. Numbers in the form 3.4.2.3 representing a hierarchical coding system. As an example

Hiercode	Code	Spesimen
0	TK	not available
1	UTH	Whole body
1.1	HHD	Live animal
1.1.1	BUD	Post larvae shrimp
1.2	HMT	Dead animal
1.2.1	BGK	Cadaver
1.2.3	FET	Foetus
10	AMPM	Kelainan AMPM
10.1	KRK	carcass
10.2	LID	Tongue
10.3	RBW	Lower Jaw
10.4	RAT	Upper Jaw
2	BTB	Body parts
2.1	KPL	Head
2.2	DAD	Chest
2.3	PH	thigh
3	ORG	organ
3.1	SPD	Circulatory
3.1.1	JTG	Heart
3.10	SEN	endocrine
3.10.1	ADR	Adrenal gland
3.2	SPN	Respiratory
3.2.1	HDG	Nose
3.2.2	SNS	Sinus
3.2.3	TRC	trachea
3.2.4	PRU	lung
3.3	SPC	Digestive
3.3.1	ESP	oesophagus
3.3.2	STO	Side
3.3.2.1	RUM	Rumen
3.3.2.2	RET	Retikulum
3.3.2.3	OMA	Omasum
3.3.2.4	ABO	Abomasum
3.3.3	USS	intestine
3.3.3.1	USK	small intestine
3.3.3.2	USB	colon
3.3.3.3	CAE	caecum
3.3.4	SHP	Hepatic
3.3.4.1	HTI	Liver
3.3.4.2	KEP	Gall bladder
3.3.5	KLO	cloaca
3.3.5.1	SKL	cloaca swab
3.3.6	PKR	Pancreas
3.3.7	AN	Anus
3.4	SKM	Urinary
3.4.1	GIN	Kidneys
3.4.2	KKM	bladder
3.5	INT	Integumentary
3.5.1	KLT	skin
3.5.1.1	KEK	Skin scraping
3.5.1.2	KRP	scab
3.5.1.3	EPLK	epitel luka
3.5.2	RBT	Hair
3.5.3	EAR	ear
3.5.4	BL	Feather
3.6	SRP	Reproductive
3.6.1	SRJ	Male reproductive
3.6.1.1	TES	testes
3.6.2	SRB	Female reproductive
3.6.2.1	VAG	Vagina
3.6.2.2	OVA	ovary
3.6.2.3	UTR	uterus
3.6.2.4	PLC	placenta
3.7	LMF	lymphatic
3.7.1	LMP	spleen
3.7.2	LND	Lymph nodes
3.7.2.1	LNM	mesenteric lymph node
3.7.2.2	LNP	periferal lymph node
3.7.2.3	LNF	femoral lymph node
3.7.2.4	LNS	scapular lymph node
3.7.3	TNS	tonsil
3.7.4	BFB	Bursa of fabricius
3.8	SSR	Nervous
3.8.1	OTK	brain
3.8.1.1	HPC	Hypocampus
3.8.1.2	OTB	Cerebrum
3.8.2	STB	spinal cord
3.9	SMK	Musculoskeletal
3.9.1	OTT	Muscle
3.9.2	TLG	Bone
3.9.2.1	STL	bone marrow
4	CTB	Body fluids
4.1	DRH	Blood
4.1.1	DRU	whole blood
4.1.2	SRM	serum
4.1.3	NNH	pus
4.10	URN	Urine
4.11	HCD	Pericardial Fluid
4.12	CHD	Nasal fluid
4.13	COD	edema fluid
4.14	CSC	Subcutan fluid
4.15	CVG	Vaginal fluid
4.16	FES	Faeces
4.17	MTH	Vomit
4.18	CFE	Cairan Fetus
4.2	CSTB	cerebrospinal fluid
4.3	CLT	synovial fluid
4.4	CVE	vesicular fluid
4.5	CPR	lung fluid
4.6	ISP	GIT materials
4.6.1	LR	Saliva
4.6.2	CEP	bile
4.6.3	ILB	Gut contents
4.6.3.1	IPR	Stomach contents
4.6.3.2	IRM	Rumen contents
4.6.3.3	IUS	Intestinal contents
4.6.3.5	CPR	Abdomen liquid
4.6.4	ITB	craw content
4.6.4.1	IPV	Proventriculus content
4.7	EKS	Exudates
4.7.1	EHD	nose exudate
4.7.2	EMT	eye exudate
4.7.3	EML	oral mucous
4.7.4	SWN	swab nasofaring
4.7.5	SWMK	food and mouth swab
4.7.6	SWM	swab mulut
4.7.7.	SWKK	swab kuku
4.7.9	SWLS	lesion swab
4.8	ASU	Milk
4.9	SPR	Semen
5	OGN	Organism
5.1	CAC	Worm
5.2	SRG	Insect
5.2.1	LLT	fly
5.2.1.1	LAY	chicken fly
5.2.2	NYM	mosquito
5.2.3	PIN	flea
5.2.4	CPL	louse
5.2.5	KLB	Bee colonies
5.3	ARK	Arachnids
5.3.1	KUT	tick
5.4	MOL	Mollusc
5.4.1	SPT	snail
5.5	DGI	fish
6	PRO	product
6.1	DGG	Meat
6.1.1	DGM	meat
6.1.1.1	DGS	Fresh Meat
6.1.1.1.1	DSK	Fresh meat carcass
6.1.1.1.2	DSTT	Fresh meat without bones
6.1.1.1.3	DSC	Chopped fresh meat
6.1.1.2	DGB	Frozen
6.1.1.2.1	DBK	Frozen meat carcasses
6.1.1.2.2	DBTT	Frozen boneless meat
6.1.1.2.3	DBC	Frozen minced meat
6.1.2	POD	processed meat
6.1.2.1	DUP	Meat
6.1.2.1.1	DDG	Steak
6.1.2.1.2	AB	Abon
6.1.2.1.3	KK	crackers skin
6.1.2.1.4	DK	lung crackers
6.1.2.1.5	KUAY	Chips chicken intestine
6.1.2.2	DH	Meat
6.1.2.2.1	BS	Meat ball
6.1.2.2.2	NG	Nuget
6.1.2.2.3	SOS	Sosis
6.1.2.2.4	BG	Burger
6.1.2.2.5	SMTK	Cooked Sausage
6.1.2.2.6	CBSK	Corned beef and sausage
6.2	SSG	milk
6.2.1	SSG	Fresh milk
6.2.1.1	SSP	Cows milk
6.2.1.2	DGK	Buffalo milk
6.2.2	SS	SS
6.2.3	SPT	pasteurized milk
6.2.4	SF	Fermented milk
6.2.5	SK	skim milk
6.2.6	SKR	Cream
6.2.7	KJ	Cheese
6.2.8	WH	Whey
6.3	TLR	egg
6.4	KKL	Kikil
7	PKN	animal feed
7.1	JGG	Corn
8	SLK	Environmental
8.1	AIR	Water
8.1.1	ASM	Ground water
8.1.2	ALB	waste water
8.10	DFT	defeathering machine
8.2	TNH	soil
8.3	LIT	Litter
8.3.1	SKM	rice husks
8.4	BT	Stone
8.5	TAB	Carcass Display Table
8.6	BSK	carcass baskets
8.7	WSB	waste bins
8.8	PRC	processing table
8.9	WCL	wet cloths
9	MCO	microorganisme
9.1	PRB	probiotic
9.2	ISO	Isolate

Data tables

An example of a data table (animals in laboratory submissions) is shown below:

CREATE TABLE animals (
  id serial PRIMARY KEY,
  labsubmissionid integer REFERENCES labsubmissions,
  animalident character varying NOT NULL,
  ownerid integer REFERENCES owners,
  animalid bigint REFERENCES hewan,
  speciesid integer NOT NULL REFERENCES species,
  sexid integer REFERENCES sex,
  age double precision,
  ageunitsid integer REFERENCES ageunits,
  msgid bigint,
  createdby integer NOT NULL DEFAULT 1 REFERENCES users,
  createdon timestamp without time zone NOT NULL DEFAULT now(),
  modifiedby integer REFERENCES users,
  modifiedon timestamp without time zone,
  del boolean NOT NULL DEFAULT false
);