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
);
Diperoleh dari "http://wiki.isikhnas.com/index.php?title=New_table_creation&oldid=39424"