SQL – Naming Conventions

Consistent naming is the single highest-leverage investment in a database schema. A well-named table, column, or index communicates its purpose without needing documentation. A poorly named one forces every developer who touches it to either guess or dig through application code to understand what it represents.

These conventions apply to all SQL at Cygnus Dynamics — migrations, raw queries, stored procedures, and ORM schema definitions. The primary target database is PostgreSQL, but these standards apply to any SQL database in our stack.

PostgreSQL is case-insensitive for unquoted identifiers

PostgreSQL folds unquoted identifiers to lowercase. UserOrder, userorder, and USERORDER all refer to the same object. Always use snake_case — never mix cases or rely on quoting to enforce a particular case.

Quick Reference

Object	Convention	Example
Table	snake_case, plural noun	orders, order_items, user_profiles
Column	snake_case, descriptive	order_id, created_at, is_active
Primary key	id	id
Foreign key	<referenced_table_singular>_id	user_id, product_id
Boolean column	is_ / has_ / can_ prefix	is_active, has_paid, can_edit
Timestamp column	_at suffix	created_at, updated_at, deleted_at
Status column	_status suffix	payment_status, order_status
Index	<table>_<columns>_idx	orders_user_id_idx
Unique index	<table>_<columns>_key	users_email_key
Primary key constraint	<table>_pkey	orders_pkey
Unique constraint	<table>_<columns>_key	users_email_key
Foreign key constraint	<table>_<column>_fkey	orders_user_id_fkey
Check constraint	<table>_<column>_check	products_price_check
View	v_<descriptive_name>	v_active_orders
Materialised view	mv_<descriptive_name>	mv_daily_revenue
Stored function	<verb>_<noun>	calculate_order_total, get_user_by_email
Trigger	<timing>_<table>_<event>	before_orders_insert, after_users_update
Trigger function	trg_<table>_<description>	trg_orders_set_updated_at
Enum type	<domain>_status or <domain>_type	order_status, payment_method
Sequence	<table>_<column>_seq	orders_id_seq
Schema	snake_case, noun	public, billing, audit

Tables

Table names must be plural nouns in snake_case. They represent a collection of entities — plural is grammatically correct and consistent with how ORMs (Prisma, Sequelize, SQLAlchemy) generate table names by default.

-- ✅ Correct — plural, snake_case, descriptive
CREATE TABLE orders (...);
CREATE TABLE order_items (...);
CREATE TABLE user_profiles (...);
CREATE TABLE payment_methods (...);
CREATE TABLE audit_logs (...);

-- ❌ Incorrect
CREATE TABLE Order (...);           -- singular, capitalised
CREATE TABLE tblOrder (...);        -- tbl_ prefix — Hungarian notation, never use
CREATE TABLE UserProfile (...);     -- PascalCase — not used for table names
CREATE TABLE user_profile (...);    -- singular — use plural

Never prefix tables with tbl_, t_, or any type marker

The fact that it is a table is obvious from context. Prefixes clutter every query, waste characters, and add zero value. This applies to all objects — no sp_ for functions, no vw_ for views (use v_ prefix only, per the table above).

Columns

Column names use snake_case. They must be descriptive — a developer reading a query should understand the data without opening a schema diagram.

-- ✅ Descriptive, snake_case, full words
CREATE TABLE orders (
    id              BIGINT        PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id         BIGINT        NOT NULL REFERENCES users(id),
    total_amount    NUMERIC(12,2) NOT NULL,
    currency_code   CHAR(3)       NOT NULL DEFAULT 'USD',
    order_status    VARCHAR(50)   NOT NULL DEFAULT 'pending',
    is_gift         BOOLEAN       NOT NULL DEFAULT FALSE,
    shipping_notes  TEXT,
    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    deleted_at      TIMESTAMPTZ             -- NULL means not deleted
);

-- ❌ Abbreviated, vague, or wrong case
CREATE TABLE orders (
    OrderID  INT,       -- PascalCase
    amt      DECIMAL,   -- abbreviation — use total_amount
    stat     VARCHAR,   -- abbreviation — use order_status
    flg      BOOLEAN,   -- meaningless abbreviation
    dt       TIMESTAMP  -- ambiguous — created_at? updated_at?
);

Boolean Columns

Boolean columns must start with is_, has_, or can_ — they read as a natural yes/no question in WHERE clauses:

-- ✅ Reads naturally in a WHERE clause
SELECT * FROM users
WHERE is_active          = TRUE
  AND has_verified_email = TRUE
  AND can_place_orders   = TRUE;

-- ❌ Ambiguous — is 'active' a boolean or a status string?
SELECT * FROM users
WHERE active = TRUE AND verified = TRUE;

Timestamp Columns

Always use TIMESTAMPTZ (timestamp with time zone). All timestamps are stored in UTC.

-- ✅ TIMESTAMPTZ — correct type and suffix
created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
deleted_at    TIMESTAMPTZ,          -- NULL = not deleted (soft delete pattern)
published_at  TIMESTAMPTZ,          -- NULL = not yet published
expires_at    TIMESTAMPTZ NOT NULL,

-- ❌ Wrong type or ambiguous name
create_date   TIMESTAMP,    -- no timezone — almost always wrong
modified      TIMESTAMP,    -- no _at suffix, no timezone
ts            TIMESTAMPTZ   -- meaningless abbreviation

Status Columns

-- ✅ _status suffix makes the column's role obvious
order_status    VARCHAR(50) NOT NULL DEFAULT 'pending',
payment_status  VARCHAR(50) NOT NULL DEFAULT 'unpaid',
kyc_status      VARCHAR(50) NOT NULL DEFAULT 'not_started',

-- ✅ Combine with a CHECK constraint to enforce valid values
order_status VARCHAR(50) NOT NULL DEFAULT 'pending'
    CONSTRAINT orders_order_status_check
    CHECK (order_status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'))

Primary Keys

Use id as the primary key on every table. Use BIGINT GENERATED ALWAYS AS IDENTITY for all new tables — this is the modern PostgreSQL standard, replacing the legacy SERIAL type.

-- ✅ Standard primary key
CREATE TABLE users (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    ...
);

-- ✅ UUID primary key for externally-exposed or distributed IDs
CREATE TABLE api_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    ...
);

-- ❌ Avoid these patterns
CREATE TABLE users (
    user_id    BIGINT PRIMARY KEY,  -- redundant prefix on the owning table
    UserID     INT,                 -- PascalCase
    pk_user_id INT                  -- pk_ prefix — unnecessary
);

Foreign Keys

Foreign key columns are named <referenced_table_singular>_id:

-- ✅ Consistent FK naming
CREATE TABLE order_items (
    id           BIGINT        PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    order_id     BIGINT        NOT NULL REFERENCES orders(id),      -- → orders
    product_id   BIGINT        NOT NULL REFERENCES products(id),    -- → products
    quantity     INTEGER       NOT NULL CHECK (quantity > 0),
    unit_price   NUMERIC(12,2) NOT NULL CHECK (unit_price >= 0)
);

-- ❌ Inconsistent or unclear FK names
CREATE TABLE order_items (
    orderId   BIGINT,    -- camelCase
    fk_order  BIGINT,    -- fk_ prefix — adds noise
    product   BIGINT     -- no _id suffix — unclear it's a foreign key
);

Indexes

Index names follow the pattern <table>_<column(s)>_idx. For unique indexes, use <table>_<column(s)>_key.

-- ✅ Named consistently
CREATE INDEX orders_user_id_idx
    ON orders(user_id);

CREATE INDEX orders_created_at_idx
    ON orders(created_at DESC);

CREATE INDEX orders_status_created_at_idx
    ON orders(order_status, created_at);       -- composite: both columns in name

CREATE UNIQUE INDEX users_email_key
    ON users(email);

-- ✅ Partial index — name reflects the filter condition
CREATE INDEX orders_pending_created_at_idx
    ON orders(created_at)
    WHERE order_status = 'pending';

-- ❌ Unnamed, meaningless, or abbreviated
CREATE INDEX ON orders(user_id);               -- auto-named — inconsistent
CREATE INDEX idx1 ON orders(user_id);          -- meaningless
CREATE INDEX i_usr_id ON orders(user_id);      -- abbreviated and cryptic

Constraints

Name all constraints explicitly. This makes error messages from the database meaningful and actionable.

-- ✅ Explicitly named constraints
CREATE TABLE products (
    id          BIGINT        PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    sku         VARCHAR(100)  NOT NULL,
    name        VARCHAR(255)  NOT NULL,
    price       NUMERIC(12,2) NOT NULL,
    category_id BIGINT        NOT NULL,

    CONSTRAINT products_sku_key        UNIQUE (sku),
    CONSTRAINT products_price_check    CHECK (price >= 0),
    CONSTRAINT products_category_fkey  FOREIGN KEY (category_id)
                                       REFERENCES categories(id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

Enum Types

PostgreSQL enum types use snake_case with a descriptive domain prefix:

-- ✅ Named enums — reusable across tables
CREATE TYPE order_status AS ENUM (
    'pending',
    'confirmed',
    'shipped',
    'delivered',
    'cancelled'
);

CREATE TYPE payment_method AS ENUM (
    'credit_card',
    'debit_card',
    'bank_transfer',
    'wallet'
);

-- ✅ Used in a table definition
CREATE TABLE orders (
    id           BIGINT        PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    order_status order_status  NOT NULL DEFAULT 'pending',
    ...
);

-- ❌ Adding values to an enum requires a migration and cannot be done in a transaction
-- Prefer VARCHAR with a CHECK constraint for values that may grow over time

Enum vs VARCHAR + CHECK

Use a PostgreSQL ENUM type when the set of values is fixed and unlikely to change (e.g. weekdays). Use VARCHAR with a CHECK constraint when values may expand over time — adding a value to a CHECK constraint is a simple ALTER TABLE, while adding to an ENUM is more complex and cannot be done inside a transaction.

Stored Functions and Triggers

-- ✅ Functions — verb_noun in snake_case
CREATE FUNCTION calculate_order_total(p_order_id BIGINT)
RETURNS NUMERIC(12,2)
LANGUAGE plpgsql AS $$
DECLARE
    v_total NUMERIC(12,2);
BEGIN
    SELECT SUM(unit_price * quantity)
    INTO   v_total
    FROM   order_items
    WHERE  order_id = p_order_id;

    RETURN COALESCE(v_total, 0);
END;
$$;

-- ✅ Trigger function — trg_<table>_<description>
CREATE FUNCTION trg_orders_set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$;

-- ✅ Trigger — <timing>_<table>_<event>
CREATE TRIGGER before_orders_update
    BEFORE UPDATE ON orders
    FOR EACH ROW
    EXECUTE FUNCTION trg_orders_set_updated_at();

-- Parameters use p_ prefix; local variables use v_ prefix
-- This avoids ambiguity with column names in queries inside functions

Schemas (Namespaces)

For large databases, use PostgreSQL schemas to group related tables:

-- ✅ Feature-based schema organisation
CREATE SCHEMA billing;
CREATE SCHEMA audit;
CREATE SCHEMA reporting;

-- Tables in non-public schemas are always qualified
CREATE TABLE billing.invoices (...);
CREATE TABLE billing.payment_transactions (...);
CREATE TABLE audit.change_log (...);

-- ✅ Views in the public schema can expose joined data from multiple schemas
CREATE VIEW public.v_invoice_summary AS
    SELECT i.id, i.total, pt.status
    FROM   billing.invoices             i
    JOIN   billing.payment_transactions pt ON pt.invoice_id = i.id;

Views and Materialised Views

-- ✅ Views — v_ prefix
CREATE VIEW v_active_orders AS
    SELECT
        o.id,
        o.user_id,
        o.total_amount,
        o.order_status,
        u.email AS user_email
    FROM  orders o
    JOIN  users  u ON u.id = o.user_id
    WHERE o.order_status NOT IN ('cancelled')
      AND o.deleted_at IS NULL;

-- ✅ Materialised views — mv_ prefix
CREATE MATERIALIZED VIEW mv_daily_revenue AS
    SELECT
        DATE_TRUNC('day', created_at) AS revenue_date,
        currency_code,
        SUM(total_amount)             AS total_revenue,
        COUNT(*)                      AS order_count
    FROM  orders
    WHERE order_status = 'delivered'
    GROUP BY 1, 2
WITH DATA;

-- Refresh on a schedule — use pg_cron or application-level scheduling
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;

Reserved Words

Never use SQL reserved words as identifiers. If a desired name conflicts with a keyword, rename it to something more specific:

-- ❌ Reserved words as identifiers — cause parsing errors or confusing behaviour
CREATE TABLE order (...);   -- ORDER is a reserved keyword
column: date                -- DATE is a type keyword
column: user                -- USER is a reserved keyword
column: value               -- VALUE is reserved in some contexts

-- ✅ Rename descriptively and avoid conflicts
CREATE TABLE orders (...);
column: order_date
column: requester_id        -- or customer_id, buyer_id
column: total_value         -- or amount, total_amount