Casing, naming, and data type guidelines for database objects.
SQL keywords (including the names of built-in functions) must be written in UPPERCASE. (In some SQL dialects, some multi-word keywords have underscores; these would be written in UPPER_SNAKE_CASE.)
All explicitly named persistent or transient SQL objects (tables, views, columns, aliases, indices, sequences, stored procedures, user-defined functions) must be named using lower_snake_case.
Even if the name of table can be generated automatically (e.g., by an ORM from the name of an entity class), an explicit name must be provided if the automatically generated name does not conform to the casing and naming rules stated here.
Explicitly named tables and views must be named with singular nouns or noun phrases. (This corresponds to the convention for Java class names.) If the table or view corresponds to an entity class, it must have the same name as the class, converted to lower_snake_case, unless that would conflict with a SQL keyword or the name of a predefined object in the database.
In table and view names, do not use unnecessary prefixes, such as tbl_ or table_. Similarly, do not use unnecessary suffixes (_tbl, _table, etc.).
Table and view aliases (aka correlations) are commonly defined in join expressions, and referenced in column selection lists, ON clauses, WHERE clauses, ORDER BY clauses, etc.
The rules below do not apply to SQL statements generated by an ORM. However, in explicitly specified SQL statements using table/view aliases, follow these rules:
Aliases should be constructed from initialisms (or other relatively intuitive abbreviations) for the correlated table or view name. For example, an alias for a table named user_profile would be named up, while an alias for building_location_detail would be bld.
If a given table is included 2 or more times in a join expression, all of its aliases must be numbered—e.g., bld1, bld2. (Underscores may be used between the initialism and the number: bld_1, bld_2; as usual, consistency is strongly advised.)
If using Hibernate (or a similar ORM), which automatically maps lowerCamelCase field names in an entity class to lower_snake_case column names (e.g., someData to some_data), do not specify a column name explicitly, unless one of the following conditions hold:
The ORM is being used to map a new Java data model to an existing database schema.
The ORM’s column name mapping scheme does not correctly translate a given field name to the name of the existing column.
Explicitly named columns of all types other than BOOLEAN must be named with singular nouns or noun phrases.
BOOLEAN columns must be named with adjectives or adjectival phrases.
With a few exceptions (e.g., columns that are used as primary keys, foreign keys, or unique keys), do not use a table name as a column name prefix. For example, in a table named account, use the column name balance instead of account_balance.
For BOOLEAN columns, do not use prefixes like is_, has_, contains_, etc. (All of these make the name a verb phrase, rather than an adjectival phrase.) For example, use enabled instead of is_enabled.
All columns used as non-compound (single-column) primary keys or foreign keys must be named with the _id suffix. No other columns are permitted to use that suffix.
All columns constituting non-compound (single-column) primary keys must be named with the {table}_id pattern. For example, the primary key column for an appointment table would be named appointment_id.
Foreign keys may be—but need not be—named to match the corresponding primary keys. An acceptable alternative approach is to name the foreign key according to the role played by the referenced table.
For example, assume we have the table contributor, with the primary key contributor_id. We also have another table, post, with a foreign key that references contributor.contributor_id. This foreign key column might also be named contributor_id—or it could be named something like author_id, since the referenced contributor record represents the author of any given post.
Columns constituting non-compound (single-column) unique keys (other than primary keys, which are implicitly unique) should be given names that indicate that values in the column uniquely identify rows. Suggested name endings that are commonly (but not exclusively) used for this purpose include _name, _code, and _key.
Stored procedures and triggers must be named using verbs or verb phrases.
Do not use unnecessary prefixes in stored procedure or trigger names, such as sp_, tr_, or proc_. These are similarly unnecessary in suffix form; don’t use them.
Functions may be defined to return scalar values, composite values (this may be thought of as a single row), or rowsets; the naming rules reflect these possibilities:
Scalar-valued functions must be named according to the same rules as columns.
Composite- and rowset-valued functions must be named according to the same rules as tables and view.
Do not use unnecessary prefixes, such as func_, or fn_. These are equally lacking in meaningful information in suffix form; don’t use them.
In most SQL dialects, index names can be generated automatically, and need not be specified explicitly. If that isn’t the case for the underlying RDBMS, but an ORM is used, the ORM virtually always generates index names automatically. Prefer the automatically generated name to an explicitly specified name.
If an index must be named explicitly, the name must follow the applicable pattern from the list below.
For an index backing a primary key comprised of a single column:
pk_{table}
Placeholders
{table}For an index backing a non-primary key (compound or otherwise), compound primary key, or unique constraint:
{prefix}_{table}_{column1}[_{column2}[…]]
Placeholders
{prefix}pk for primary key index, uq for unique constraint-backing index, ix for non-unique index.{table}{column1}{column2}_) .The square brackets enclose optional parts of the name, and must be replaced accordingly; the brackets themselves are not allowed in the actual index name.
Many RDBMSs use sequences to implement auto-numbered (aka auto-incremented) fields.
When using an ORM (and in some cases when an ORM is not used), sequences are defined implicitly, with an automatically generated name. When this is the case, the generated name must be used.
If a sequence name must be specified explicitly, the name must follow the applicable pattern from the list below.
For a sequence providing values for a non-compound primary key column:
{column}_seq
Placeholders
{column}For a sequence providing values for a column that is not a non-compound primary key:
{table}_{column}_seq
Placeholders
{table}{column}If an ORM is being used, and the ORM is capable of mapping a Java type to a SQL type automatically, the SQL type should be left as mapped, unless the resulting type violates one of the strict rules that follow.
Columns of character types (e.g., CHAR, VARCHAR, TEXT) may be used (individual or compounded with other columns) in unique constraints (and the supporting indices), but must not be used in primary keys.
Any table that is not a join table (used to effect a many-to-many relationship) must have a non-compound (single-column) primary key, with automatically generated values of one of these types:
UUID
For a primary key, a value of this type must be stored and indexed as a 16-byte (128-bit) binary value, not as text; in some RDBMSs, this requires that we declare the actual type as BINARY(16), CHAR(16) FOR BIT DATA, etc.
BIGINT
The UNSIGNED modifier must be used, if available.
INTEGER
This is the only integral type in SQLite, and must be used (without the UNSIGNED modifier, which is not available in SQLite) for primary keys in that database.
In a physical entity-relationship diagram (ERD), attributes must be specified using the predefined types in the SQL standard, or using more specific types supported by the underlying RDBMS.