Design and Naming Conventions for Databases

Table of Contents

1. Language
2. Project Prefix
3. Table Name Abbreviations
4. Column Name Abbreviations
5. General Naming Conventions
6. Tables
7. Views
8. Triggers
9. Sequences
10. Columns
11. Constraints
12. Indexes
13. Functions, Procedures, Types, Packages
14. Synonyms
15. Jobs

Language

[Rule R001]

The language used for naming schema objects is English. A different language may be used for source code and documentation comments.

Back to top

Project Prefix

[Rule R002]

A project prefix may be agreed. These prefixes are followed by an underscore ('_') and placed in front of every table or view name. A prefix comprises a maximum of 3 alphanumeric characters, starting with a letter.

Why? Project prefixes are useful for identifying the use of tables or views in the application source code, especially when using embedded SQL or dynamic SQL (JDBC) in the source code. Alternatively, the schema can be consistently used.

Back to top

Table Name Abbreviations

[Rule R003]

A unique table name abbreviation must be agreed for each table. All table name abbreviations and table names must be unique within their schema. A table name abbreviation comprises a maximum of 5 alphanumeric characters, starting with a letter. The table name abbreviation must be added to the comments for the table as follows:

comment on table employee is 'abbrev=emp; ...';

Alternatively:

comment on table employee is '...; abbrev=emp';

Why? Among other things, this enables the automatic assignment of a sequence to a table.

Back to top

Column Name Abbreviations

[Rule R004]

A unique column name abbreviation may be agreed for each column. A column name abbreviation comprises up to 6 alphanumeric characters, starting with a letter. The column name abbreviation may be included in the comment for the column as follows:

comment on column employee.last_name is 'abbrev=empnam; ...';

Alternatively:

comment on column employee.last_name is '...; abbrev=empnam';

Back to top

General Naming Conventions

[Rule R005]

All names comprise only the letters 'A'-'Z', the numbers '0'-'9', and an underscore '_'. All names start with a letter. All names are a maximum of 30 characters long. Objects must always be created case-insensitive. Names do not match reserved words or keywords.

Back to top

Tables

[Rule R006]

Object Type	Rule	Example
Tables	<ProblemDomainName>	employee
Journal tables	<ProblemDomainName>_JN	employee_jn, The table <ProblemDomainName> also exists as the basis of the journal.
Logging tables	<ProblemDomainName>_LOG	import_log
DML error logging tables	<ProblemDomainName>_ERR	debtor_err, The table <ProblemDomainName> also exists as the basis for the associated DML statement.
Backups/copies	<ProblemDomainName>_BAK	debtor_bak, The table <ProblemDomainName> also exists as the origin of the copy.

Back to top

Views

[Rule R007]

Object Type	Rule	Example
View	<ProblemDomainName>_V	employee_v, The table <ProblemDomainName> may also, but does not have to exist.

[Rule R008]

Views are not nested.

Why? Sooner or later, views nested within one another result in performance problems.

[Rule R009]

Views are not permitted as part of an application access layer.

[Rule R010]

Views are permitted:

• As a aid/convenience for developers or DBAs.
• To hide complexity (by denormalization) from external systems.
• To hide information in rows or columns (information hiding) from external systems.

Back to top

Triggers

[Rule R011]

There are 3 different types of triggers that may be used.

• Sequence triggers are used to populate the technical key column ID (before row insert).
• Auditing triggers are used to populate the auditing columns (before row insert/update).
• Journaling triggers are used to populate journaling tables (after row insert/update/delete).

[Rule R012]

Object Type	Rule	Example
Sequence trigger	<TableAbbreviation>_SEQ_TG	emp_seq_tg
Journaling trigger	<TableAbbreviation>_JN_TG	emp_jn_tg
Auditing trigger	<TableAbbreviation>_AUD_TG	emp_aud_tg

[Rule R013]

The creation of other triggers is not permitted.

[Rule R014]

As an alternative to triggers, an appropriate access layer must be implemented.

[Rule R015]

All or nothing principle:

• If there is a technical key column being populated by a sequence trigger, it applies to all technical key columns.
• If there is an auditing trigger populating auditing columns, all auditing columns are populated by an auditing trigger.
• If there is a journal table being populated by a journaling trigger, it applies to all journal tables.Alles-oder-Nichts-Prinzip:

[Rule R016]

All triggers should be created automatically, e.g. from a template.

Back to top

Sequences

This section applies to sequences used to populate technical key columns. In principle, other sequences are permitted, but not included here owing to their infrequency.

[Rule R017]

Object Type	Rule	Example
Sequence	<TableAbbreviation>_SEQ	emp_seq, For column ID of the table with the associated <TableAbbreviation>.

[Rule R018]

Sequences always start with 1.

[Rule R019]

Tables being populated by bulk inserts may use caching sequences.

[Rule R020]

Example of a non-caching sequence used to populate the column ID of the table using the abbreviation emp:

create sequence emp_seq nocycle nocache maxvalue 999999999999999999 minvalue 0 start with 1;

Back to top

Columns

[Rule R021]

Object Type	Rule	Example
Technical key column	ID	employee.id
Foreign key column	<TableAbbreviation>_ID, <TableAbbreviation>_<Qualification>_ID	employee.dep_id <TableAbbreviation> of the referenced table <Qualification> for multiple references.
Auditing column, creation date	CREATED_DATE	employee.created_date
Auditing column, modification date	MODIFIED_DATE	employee.modified_date
Other columns	not 'ID', do not end with '_ID', no auditing column	employee.last_name

Back to top

Technical Keys

[Rule R022]

Every table has a technical primary key. The table is referenced only by this primary key. Why? Domain keys may be subject to change. This way, they are uncoupled from the task of referential integrity.

[Rule R023]

The technical key column is always named id. Joins must therefore always be created via the id column, not via domain keys, which may be subject to change.

[Rule R024]

The technical key column is not nullable. Why? Data records can always be referenced this way.

[Rule R025]

The technical key column is of type NUMBER(18,0)

999.999.999.999.999.999 means:
999.999 days each with 999 billion entries created.
999.999 days corresponds to approx. 2,738 years

Why? The id can easily fit into common data types, 64-bit signed integer (Java: long, C: int64_t/signed long long).

[Rule R026]

The technical key column is always populated by a sequence. This preferably takes place in an access layer.

Back to top

Auditing Columns

[Rule R027]

Auditing columns are mandatory for all tables and must be maintained as well. This preferably takes place in an access layer.

[Rule R028]

Auditing columns are not nullable, i.e. initial creation counts as modification.

[Rule R029]

Auditing columns are usually of type Date.

Back to top

Constraints

[Rule R030]

Object Type	Rule	Example
Primary key constraint	<TableAbbreviation>_PK	emp_pk
Unique constraint	<TableAbbreviation>_<Qualification>_UK	emp_username_uk, Example of <Qualification>: Column name, column name abbreviation (multiple, if required), domain aspect.
Foreign key constraint	<TableAbbreviation>_<TableAbbreviation>_FK, <TableAbbreviation>_<TableAbbreviation>_<Qualification>_FK	emp_dep_fk, Example of : Column name, column name abbreviation (multiple, if required), domain aspect.
Check constraint	<TableAbbreviation>_<Qualification>_CK	emp_manager_ck, ... constraint emp_manager_ck check (manager in (0,1)) Example of : Column name, column name abbreviation (multiple, if required), domain aspect.
NOT NULL constraint	- (no name necessary)

Back to top

NOT NULL Constraints

[Rule R031]

NOT NULL constraints are not identified by name, because:

1. They can be modified via

   alter table ... modifiy ... [not] null;

2. Furthermore, the error message for constraint violation (ORA-01400) provides enough context to identify the problem.

Back to top

Indexes

[Rule R032]

Object Type	Rule	Example
Index	<TableAbbreviation>_<Qualification>_IX	emp_id_ix, Example of : Column name, column name abbreviation (multiple, if required), domain aspect.

Back to top

Functions, Procedures, Types, Packages

[Rule R033]

Functions, procedures and types should preferably be stored in packages.

[Rule R034]

Functions, procedures, types and packages may be assigned a project prefix.

Back to top

Synonyms

[Rule R035]

Synonyms are not permitted in owner schemas.

[Rule R036]

Synonyms are only allowed in a schema used by an external system to access an application schema ("access schema"). Drawbacks: If maintenance must be done in this external schema, this may mean further distribution of the access schema, depending on operating concept.

Back to top

Public Synonyms

[Rule R037]

Public synonyms are not permitted.

Back to top

Jobs

[Rule R038]

DBMS jobs are not permitted. Scheduler jobs must be used. Why? DBMS jobs have been replaced by scheduler jobs.

[Rule R039]

Scheduler jobs should preferably be created with the job type "STORED_PROCEDURE". Why? a stored procedure is already compiled. This does not apply to anonymous PL/SQL blocks. Thus there can not be any compile time errors.