Database Naming Conventions Cheat Sheet

Database Naming Conventions Cheat Sheet.md

Database Naming Conventions Cheat Sheet

Updated: 2025.07.17

Following a consistent style is crucial for database readability, maintainability, and portability.

General Principles

These conventions apply to all database objects, including tables, columns, views, and functions.

Convention	Recommendation	Good Example	Bad Example
Consistency	Pick a style and apply it uniformly.		Mixing snake_case and PascalCase.
Casing	Use snake_case for all identifiers. It is the most portable and safest choice, avoiding critical case-sensitivity issues between different database systems (e.g., PostgreSQL, MySQL) and operating systems (e.g., Linux, Windows).	policy_holder	PolicyHolder[^1], policyHolder [^1]But PascalCase is common for SQL Server
Reserved Words	Never use SQL reserved words. It forces quoting and causes ambiguity.	customer_order, app_user	order, user, group
Abbreviations and Acronyms	Should be avoided, but common ones specific to the insurance industry can be used.	cat, fnol, gwp, mga	cus or cust for customer

Table Naming

Convention	Recommendation	Good Example	Bad Example
Singular Nouns	Name tables after the single entity they represent.	user, policy_holder	users, policy_holders
Avoid Prefixes	Should avoid clutter like tbl_ or table_. Use schemas for grouping if needed.	claim	tbl_claim

Column (Field) Naming

Convention	Recommendation	Good Example	Bad Example
Primary Keys (PK)	Use table_name_id for clarity in joins.	policy_id	id, PK_Policy
Foreign Keys (FK)	Name it exactly the same as the primary key it references.	policy_holder_id	fk_policy_holder
Booleans	Prefix with is_, has_, can_ to read like a question.	is_active, has_dependents	active, dependents
Dates and Times	Suffix with _at for datetimes and _on or _date for dates.	created_at, approved_on	creationDate
Units	Include units in the name if the data type isn't explicit.	weight_kg, duration_seconds	weight, duration

Special Table Types

Table Type	Naming Convention	Primary Key	Purpose & Example
Linking Table (Many-to-Many)	Combine the two table names (e.g., table1_table2).	Composite PK of both foreign keys.	Connects two tables. Example: policy_coverage links the insurance_policy and coverage tables. Can hold data about the relationship itself (e.g., coverage_limit).
History/Audit Table	Use the original table name with a _history or _audit suffix.	A new, simple PK like history_id.	Tracks all changes (INSERT, UPDATE, DELETE) to a record for auditing. Example: insurance_policy_history.

---

Example ER Diagram

This diagram illustrates an example schema for insurance, applying the conventions listed above.

erDiagram
    policy_holder {
        int policy_holder_id PK "e.g., 12345"
        varchar first_name "e.g., John"
        varchar last_name "e.g., Doe"
        datetime created_at "e.g., 2022-01-01 12:00:00"
    }

    insurance_policy {
        int policy_id PK "e.g., 67890"
        int policy_holder_id FK "e.g., 12345, Links to policy_holder"
        varchar policy_number "e.g., IP-001"
        date effective_on "e.g., 2022-02-01"
        bool is_active "e.g., true"
        datetime updated_at "e.g., 2022-02-15 14:30:00"
    }

    coverage {
        int coverage_id PK "e.g., 1111"
        varchar name "e.g., Fire, Theft, Flood"
        text description "e.g., Coverage for damage caused by fire, theft, or flood"
    }

    policy_coverage {
        int policy_id PK, FK "e.g., 67890"
        int coverage_id PK, FK "e.g., 1111"
        decimal coverage_limit "e.g., 100000.00"
        decimal premium_amount "e.g., 500.00"
    }

    insurance_policy_history {
        int history_id PK "e.g., 1"
        int policy_id "e.g., 67890, FK to original policy"
        varchar action "e.g., UPDATE, INSERT, DELETE"
        varchar policy_number "e.g., IP-001, Old value"
        bool is_active "e.g., true, Old value"
        datetime changed_at "e.g., 2022-02-15 14:30:00"
        int changed_by_user_id "e.g., 2345"
    }

    policy_holder ||--o{ insurance_policy : "has"
    insurance_policy ||--|{ policy_coverage : "has"
    coverage         ||--|{ policy_coverage : "is part of"
    insurance_policy ||--|{ insurance_policy_history : "has history"

Loading

How the diagram follows the conventions:

• Casing & Naming: All tables and columns use snake_case. Table names are singular (policy_holder).
• Keys: Primary keys follow the table_name_id format (policy_holder_id). Foreign keys match the key they reference (policy_holder_id in insurance_policy).
• Linking Table: policy_coverage correctly links insurance_policy and coverage with a composite primary key and contains relationship-specific data (coverage_limit).
• History Table: insurance_policy_history shadows the insurance_policy table and includes metadata columns like history_id, action, and changed_at to track changes.
• Data Types: Column names are descriptive (is_active for a boolean, created_at for a datetime).