Skip to content

Database naming conventions

Naming conventions to help every contributor speak the same schema.

Info

Some of the naming conventions here are new, and some are revised from old/existing conventions. Existing code and existing database objects are not expected to follow these conventions perfectly. New code and new database objects should follow these conventions.

Databases

  • The name of a database is a hybrid of Pascal case and snake case. Partition_Environment_Application
  • Examples:
    • E00_Development_Engine
    • E02_Sandbox_Shift
    • E05_Production_Shift

Schemas

  • The name of a schema is lowercase and singular form.
  • A schema name should match a component (toolkit) name.
  • Examples:
    • billing
    • platform

Tables

  • T = The name of a base Table (i.e., a non-projection table) has a T prefix. A base table stores raw, unprojected data.
  • P = The name of a Projection table (also called a Query table) has an R prefix (or a Q prefix). A projection table stores a projection of data from a base table.
  • B = The name of a temporary Buffer table has a B prefix. A buffer table caches intermediate results for complex multi-stage algorithms, queries, and reports.
  • Z = The name of deprecated Zombie table has a Z prefix. A zombie table is a candidate for removal or replacement.
  • The name of a table name is singular Pascal case (i.e. the first letter of each word in a compound word is capitalized).
  • Examples:
    • Good: TUser, BComplexReportPreparation
    • Bad: User, Users, TUsers

Views

  • The name of a view has a V prefix.
  • The name of an indexed view has an X prefix.
  • The name of a view is singular Pascal case.
  • Examples:
    • VOrganizationDetail
    • XFastInventorySummary

Warning

This is a question for future discussion: should the name of a view follow the naming convention for a query?

Stored procedures

  • The name of a stored procedure follows the naming convention for queries and commands.
  • If it is not possible to follow the naming convention for queries and commands, then the name of a stored procedure has a P prefix.

Columns

  • The name of a column is Pascal case.
  • Ideally, a column name should be unique.
    • If two different tables contain a column with the same name, then both columns should have the same meaning.
    • If two different columns have different meanings then they should have different names.
    • For example, TUser.FullName and TPerson.FullName may be acceptable, but TUser.Name and TModule.Name is not recommended because a person's full name is not semantically equivalent to a module's name. Therefore, TUser.FullName and TModule.ModuleName are better column names.
  • The name of a column that stores an email notification message identifier should follow this convention:
    • When \<ChangeType> Notify \<RecipientDescriptor> MessageIdentifier
    • For example: WhenCrazyWidgetColorChangedNotifyMyImaginaryFriendsInGreenlandMessageIdentifier
    • Notice the terms When, Notify, and MessageIdentifier can be used (if needed) to parse the ChangeType and/or RecipientDescriptor from this type of a column name.

Data types

  • If a date/time column contains both a Date part and a Time part then the data type must be DATETIMEOFFSET to ensure the meaning of its values is unambiguous with regard to time zone.
  • If a date/time column contains a Date part only then the data type must be DATE.
  • If a date/time column contains a Time part only then the data type must be TIME.

Entity Framework

  • The name of an entity class should match the name of the database table (or view) to which it binds, with the suffix "Entity".
    • For example, if an entity class binds to a database table named TRole, then the C# entity class should be named TRoleEntity.
  • The name of an entity type configuration class should match the name of the database table (or view) to which it binds, with the suffix "Configuration".
    • For example, TRoleConfiguration.
  • The name of a DbSet<T> property within a DbContext class should exactly match the name of the database table (or view) to which it binds.
    • For example, if a DbContext contains a property of type DbSet<TOrganizationEntity>, which binds to the database table named QOrganization, then the name of the property should be QOrganization: internal DbSet<OrganizationEntity> QOrganization { get; set; }

SQL Server versus PostgreSQL

The naming conventions above are intended for SQL Server databases, and are not a good fit for PostgreSQL databases because PostgreSQL automatically applies lowercase to object names.

Therefore, upper case letters are strongly discouraged. For details, refer to this article: Do not use upper case table or column names in PostgreSQL.

Snake case is strongly recommended for all object names in PostgreSQL databases.

When you use PostgreSQL, follow the de-facto standards.

Timestamps (date + time):

  • created_at and updated_at — by far the most popular, especially in web applications and ORMs
  • created_on and updated_on — alternative that some prefer
  • created_when and updated_when — grammatically logical but rarely used in open-source projects

Date-only columns:

  • created_date, start_date, end_date
  • birth_date, due_date, etc.

Time-only columns:

  • created_time, start_time, end_time

User identity columns:

  • created_by and updated_by — storing the user ID (foreign key)
  • created_by_id and updated_by_id — more explicit about storing an ID
  • creator_id and updater_id — noun-based approach
  • created_by_user_id and updated_by_user_id — very explicit but verbose

The pattern created_by and updated_by is overwhelmingly the most popular in open-source projects, with the assumed understanding that "by" is an alias for "id".

updated_at is significantly more popular than modified_at, though both are grammatically correct. This is mainly a semantic preference, because developers tend to think of database operations as:

  • CREATE → created_at
  • UPDATE → updated_at
  • DELETE → deleted_at (for soft deletes)

This maps directly to CRUD operations, making updated_at feel more natural in a database context.