Database Standards & Guidelines

July 20, 2010
 |  No Comments

General

  • Primary keys should not be specified for tables, unique indexes should be used instead
  • For partitioned tables, unique indexes must include the partition key
  • Type tables should be suffixed with _TYPE
  • Objects should be prefixed with their schema in DDL statements (table, index, sequence, etc)
  • Use of CLOB/BLOB datatypes is discouraged
  • Use of functions/procedures/packages is discouraged
  • Always use semicolons (;) to separate statements used in scripts
  • If you're not sure of something, ask. Unwanted database changes can haunt you forever.

Table Creation/Alter

  • UUID columns should always be CHAR(36), not VARCHAR2(36) or similar
  • Sample Create: 
    	CREATE TABLE QBW.DISPOSITION(  ID            CHAR(36 BYTE)                   NOT NULL,  DISP_LINK_ID  CHAR(36 BYTE)                   NOT NULL,  PROC_DATE     DATE                            NOT NULL,  DISP_DATA     VARCHAR2(4000 BYTE),  DISP_DATE     DATE                            NOT NULL,  LINK_TYPE_ID  INTEGER                         NOT NULL,  DISP_TYPE_ID  INTEGER                         NOT NULL);
  • Sample Alter (column add):
    • ALTER TABLE QBW.disposition ADD correlation_id CHAR(36);
  • Sample Alter (column drop):
    • ALTER TABLE QBW.disposition DROP COLUMN correlation_id;

Index Creation

  • For partitioned tables, the partition key is generally unnecessary in normal indexes.
  • Unique Index Sample:
    • CREATE UNIQUE INDEX QBW.DISP_UNIQUE_INDX ON QBW.DISPOSITION (ID, PROC_DATE);
  • Normal Index Sample: 
    	CREATE INDEX QBW.disp_link_type_date_idx ON QBW.disposition(disp_link_id, disp_type_id, proc_date);

Synonym Creation

  • Create public, not private, synonyms
  • Sample:
    • CREATE OR REPLACE PUBLIC SYNONYM DISPOSITION FOR QBW.DISPOSITION;

Sequence Creation

  • Generally try to avoid the use of sequences as it is vendor specific functionality (It's better to use GUID)
  • Sequences normally only need the SELECT grant. However, if allowed, ALTER is also handy
  • When used, sequences in DR databases should be created at an offset (i.e. production uses even sequences, dr uses odd)
  • Sample: 
    	CREATE SEQUENCE QBW.SOME_SEQ  START WITH 1  INCREMENT BY 2  MAXVALUE 9999999999  MINVALUE 1  CYCLE  CACHE 1000  NOORDER;

Grants

  • SELECT, INSERT, UPDATE, DELETE privileges should be granted to QBW_ROLE
  • SELECT privileges should be granted to QBW_RONLY
  • Sample
    • GRANT SELECT, INSERT, UPDATE, DELETE ON QBW.DISPOSITION TO QBW_ROLE;
    • GRANT SELECT ON DISPOSITION TO QBW_RONLY;