Naming Standards

Introduction

A dull but necessary topic if you're going to develop applications that are going to have a life longer than a year. If you don't have some standard way of naming things, there's no way you can hope to remember what things are called.

Table names

We follow the convention of prefixing each table name with an short identifier to indicate the application/business area. For example, tables in our project bidding application are prefixed "PRO_".

We try to keep table names as descriptive as possible and avoid the use of abbreviations: it's fairly obvious that a table called PRO_PROJECT_JUSTIFICATION will contain reasons for doing a project, while if it was called PRO_PRO_JUST it would not be as obvious.

Primary Keys

We try and give all our tables a numeric primary key with a a sequence. If the table is a lookup (we don't use generic lookup tables) then the number is usually a NUMBER(4), otherwise we use a NUMBER(10) convention. We don't have any tables with more than a few million rows, so that is ample for us.

We name these columns after the table name, so the primary key for our PRO_PROJECT table is PROJECT_ID. If the primary key is not a number, typically because a single letter is used for a status, we would end the column name _CODE rather than _ID, e.g. STATUS_CODE.

Sequences

We name the sequence used to populate the primary key after the table and simply add the suffix "_SEQ", e.g. PRO_PROJECT_SEQ.

Foriegn keys

We name these as [table_name]_[remote_table_name]_FK except we remove the redundant prefix from the remote table name, so a FK linking the PRO_PROJECT table to the PRO_STATUS table would be called PRO_PROJECT_STATUS_FK.

The column name in the detail table that relates to the master table is given the same name, so the PRO_PROJECT table would have a column called STATUS_CODE that related to the STATUS_CODE column in PRO_STATUS.

Lookup Tables

We try and store all lookups in their own tables. This makes it easier for future developers and report writers to follow as well as making the joins very simple and efficient. We always add a column called ACTIVE_FLAG with a simple VARCHAR2(1) 'Y' or 'N' value. Any list of values created against this table uses the clause WHERE ACTIVE_FLAG = 'Y' so that we can mark a value as inactive.

This has proved it's value so many times, I would recommend it to everyone. It so useful I think it should be built in.