database namiing convension

inf : https://wiki.kuali.org/display/KULRICE/Database+Table+and+Column+Name+Standards#DatabaseTableandColumnNameStandards-MaxIdentifierLengthsonCommonRDBMS:

Max Identifier Lengths on Common RDBMS:

RDMBS	Table Name Max Length	Column Name Max Length
Oracle	30	30
Sybase	30	30
PostgreSQL	31	31
Sap DB	32	32
MySQL	64	64
Derby	128	128
SQL Server	128	128
DB2	128	128

As can be seen there are a couple of databases (most notably Oracle) which restrict Table and Column name length to 30 characters. So our target should be 30 characters or less.

Tables, Views and Sequences

Because of the 30 character restriction, we need to design our database object name prefixes so that they take up the least amount of that space while still being descriptive enough.

We will use the following standard prefix:

<Application Acronym><2-letter Module Acronym>_

For Kuali Rice, this will be:

Rice Module	Prefix	Pre-Refactoring Notes
KSB	KRSB_	Quartz table names should begin with KRSB_ (in Rice standalone) as well Many of the current tables begin with EN_ EN_SERVICE_DEF_DEUX_T should be renamed to something like KRSB_SVC_DEF_T
KNS	KRNS_	Majority of tables currently prefixed with one of: FS_ SH_ FP_
KEW	KREW_	Tables are currently prefixed with EN_
KIM	KRIM_
KEN	KREN_	Most tables currently prefixed with NOTIFICATION_ KCB tables should be included under KREN_ as well

Table Naming Standards

1. Table names should start with <Application Acronym><2-letter Module Acronym>_
2. Table names should end in _T
3. Full table name should be no longer than 30 characters.
4. Table names should consist only of capital letters and underscores
5. Reasonable abbreviations should be used where possible
6. Separate words should be separated by underscores

View Naming Standards

Standards are the same as for Tables Names with the exception of:

• View names should end in _V

Sequence Naming Standards

Standards are the same as for Tables Names with the exception of:

• Sequence names should end in _S

Columns

Column names on tables and views should not contain prefixes or suffixes. Also, there are a few cases in our current column names where we are duplicating parts of the table names. This is a bit redundant and should be eliminated. For example, on EN_ACTN_RQST_T there are columns named ACTN_RQST_RECP_TYP_CD, ACTN_RQST_PRIO_NBR, etc. Where they could be named just RECP_TYP_CD, PRIO_NBR, etc. This would serve to make the column names as compact as possible

Besides what mentioned above, the rules for column are similar to those for table names.

Column Naming Standards

1. Column names should contain no standard prefix
2. Column names should contain no standard suffix
3. Full column name should be no longer than 30 characters
4. Column names should consist only of capital letters and underscores
5. Reasonable abbreviations should be used where possible
6. Separate words should be separated by underscores
7. Column names should not be prefixed with portions of the table name unless necessary. Examples from the KEW Action Request table:
   • use ID for a primary key identifier rather than ACTN_RQST_ID
   • use STAT_CD instead of ACTN_RQST_STAT_CD
   • use CRTE_DT instead of ACTN_RQST_CRTE_DT

Primary Keys, Foreign Keys, Indexes and Unique Constraints

Use lowercase characters for MySQL index and constraint names. On case-sensitive file systems such as those typically used on Linux and other Unix-like OSes (excluding MacOS), the index & constraint names are case sensitive even when you have lower_case_table_names=1 in your mysql configuration (due to the way MySQL stores them). Combining that with the fact that the sequence names from the exported Oracle master db will all be lowercase on a freshly impexed MySQL database, the result is that upper case index and constraint names will increase the frailty of the upgrade scripts.

The naming standards are very similar for PKs, FKs, indexes and unique constraints:

Key / Index / Constraint Naming Standards

• for primary keys:
  • <table_name w/out the trailing T>pk
• for foreign keys:
  • <table_name w/out the trailing T>fk<#>
• For indexes and constraints:
  • <table_name><type><#> where type is i for indexes and c for unique constraints.

numbering starts at 1, and increments for each additional element of the same type.

Examples using the table name krms_cntxt_t:

the primary key:
krms_cntxt_pk

indexes (indices?):
krms_cntxt_ti1
krms_cntxt_ti2
krms_cntxt_ti3

foreign keys:
krms_cntxt_fk1
krms_cntxt_fk2

unique constraints:
krms_cntxt_tc1
krms_cntxt_tc2

Exceptions to Standards

In some cases it is not possible to follow these naming standards. This is a particular problem if using vended libraries which have their own pre-defined table names.

Quartz is an example of this. However, Quartz does provide the ability to specify custom table prefixes. Such features should be taken advantage of when they are available.

See the table at the top of this document which indicates how the Quartz tables should be prefixed in the case of Kuali Rice.

Abbreviations

Before using any abbreviation in any of the database identifiers, an attempt should be made to establish if an abbreviation has already been used in other tables for that same word. Some examples, of common abbreviations seen throughout Rice are below:

Word	Abbreviation
action	ACTN
code	CD
date	DT
description	DESC_TXT
document	DOC
header	HDR
identifier	ID
indicator	IND
namespace	NMSPC
parameter	PARM
request	RQST
title	TTL
type	TYP
version number	VER_NBR
...	...

This is by no means exhaustive but demonstrates the general idea when choosing abbreviations.