We are making a new database design where we are setting some rules upfront. I dunno how much it makes sense to have these rules in place,
Each table and column name should be meaningful. It should give information about the table or the column's purpose.
Table names should not exceed 27 characters and column names should not exceed 30 characters.
Table names are plural. For example:
Each table has a primary key as
NUMBER(15). table name is used as singular in the Primary Key column. Example: the
USER_REQUESTStable has a primary key as
If the table or column name exceeds the limits, a suitable abbreviation is used. Example:
CMS_EXEMPTIONSis the table to keep content management system exemptions.
Tables that keep transactional data should end with
COMPANY_TRANSis the table that keeps company related transaction data.
Each table has 4 WHO column as :
CREATE_USER NUMBER(15) CREATE_DATE DATE MODIFY_USER NUMBER(15) MODIFY_DATE DATE
Columns which are a foreign key relation are named as the primary key of the referenced table. Example: if a table has a reference to the
COUNTRIEStable, the referencing column is also named as
COUNTRY_IDas the primary key of the
If the meaning of the referencing column should be other than the 8th rule because of the first rule (meaningful names), meaningful name rule is applied. Ex: Both nationality and country columns have a relation to
COUNTRIEStable if it is used to keep a person nationality than it is named as
If the table has more than one related to the same table they are given different names like
If a table is a relation table between to tables, the table name contains both table names.
To give meaningful names were always our first rule in naming.