Database Naming Conventions: Tables, Columns, Keys and Indexes

Use lower snake_case for everything, never quote identifiers, name foreign key columns after the table they point to, and give every constraint and index an explicit, predictable name. The rest of this article explains why each rule exists, where the engines disagree, and what your ORM will do if you do not decide.

Why naming deserves a decision#

Names are the only part of a schema that every layer touches: SQL in migrations, ORM mappings, query logs, error messages, BI dashboards, and the diagram someone prints for the onboarding session. A schema with Owners, pet_type, VisitDate and fk1 in it is not wrong, but every reader pays a small tax on every line. Conventions remove the tax, and they are far cheaper to adopt on day one than to retrofit after the first production release.

Rule 1: lower snake_case, unquoted#

Every major engine folds an unquoted identifier to a canonical case, and they do not agree on which one. PostgreSQL folds to lower case, Oracle to upper case, SQL Server preserves what you wrote but compares case-insensitively under its default collations, and MySQL preserves column names while table names follow the lower_case_table_names setting of the server, which in turn defaults to whatever the file system does. Lower snake_case is the one style that survives all of this unchanged: vet_specialties reads the same in a PostgreSQL log, an Oracle data dictionary view (as VET_SPECIALTIES) and a MySQL dump.

Quoting looks like a way to keep camelCase, but a quoted identifier must be quoted, with identical case, in every statement forever, including ad hoc queries at three in the morning. The details of the folding rules are in why Oracle shows your tables in upper case.

Rule 2: pick singular or plural, then never mix#

Both camps have good arguments. Plural (owners, pets) reads naturally as "a set of owners" and is what Rails, Django and Laravel generate by default. Singular (owner, pet) matches the entity name in code, avoids irregular plurals such as person and people, and is what many data warehouses and Doctrine or EF Core teams use. The cost of either choice is close to zero; the cost of mixing them is a lookup every time you write a query. Choose the one your primary framework produces so that you are not fighting its defaults, write it down, and enforce it in code review.

Join tables take both parent names in the order that reads best, or alphabetically when nothing reads best: vet_specialties, product_categories, order_coupons. More on that in designing join tables.

Rule 3: columns#

• The primary key is id. Prefixing it with the table name (owner_id in owners) makes joins read oddly and confuses it with foreign keys elsewhere.
• A foreign key column is the singular of the referenced table plus _id: owner_id, pet_id. When a table references the same target twice, name the role, not the target: shipping_address_id and billing_address_id.
• Booleans state a fact: is_active, has_microchip, is_default. Avoid negatives such as not_deleted.
• Timestamps end in _at (created_at, paid_at), dates in _date or _on (birth_date). Store money as price_cents or a NUMERIC column named for the unit.
• Do not encode the type in the name (name_str, dt_created). The type is one \d away and will change before the name does.
• Avoid reserved words. user, order, group, date and type are reserved or non-reserved keywords in at least one engine and will need quoting somewhere. users, orders, groups and pet_type sidestep the problem.

Rule 4: name every constraint and index#

If you do not name a constraint, the engine will, and each engine does it differently. The same foreign key becomes pets_owner_id_fkey in PostgreSQL, pets_ibfk_1 in MySQL and something like FK__pets__owner_id__3B75D760 in SQL Server. That matters the day you have to drop or alter it in a migration that must run on more than one engine, and it matters in error messages, where fk_pets_owners tells the reader what failed and pets_ibfk_1 does not.

Mind the length limits: PostgreSQL truncates identifiers at 63 bytes, MySQL stops at 64 characters, SQL Server allows 128, and Oracle allows 128 bytes since 12.2 but only 30 before that. A name such as ix_vet_specialties_specialty_id_vet_id fits everywhere; a longer one may be silently cut in PostgreSQL and rejected in older Oracle.

CREATE TABLE pets
(
    id       INT         NOT NULL,
    name     VARCHAR(50) NOT NULL,
    type_id  INT         NOT NULL,
    owner_id INT         NOT NULL,
    CONSTRAINT pk_pets PRIMARY KEY (id),
    CONSTRAINT fk_pets_types FOREIGN KEY (type_id) REFERENCES types (id),
    CONSTRAINT fk_pets_owners FOREIGN KEY (owner_id) REFERENCES owners (id)
);

CREATE INDEX ix_pets_owner_id ON pets (owner_id);

What the tools do when you do not decide#

Every ORM has a default, and you inherit it silently. Knowing it tells you which convention costs the least in your stack.

Migration tools that take plain SQL, such as Flyway, Liquibase, goose or dbmate, impose nothing, which makes the written convention the only thing standing between you and Owners next to pet_type.

A convention you can copy#

1. Lower snake_case for tables, columns, constraints and indexes. Never quote.
2. Tables plural (or singular; decide once). Join tables named after both parents.
3. id for the primary key, <singular>_id for foreign keys, roles when the target repeats.
4. is_ and has_ for booleans, _at for timestamps, units in money and measure columns.
5. Explicit names for every constraint and index, following pk_, fk_, uq_, ck_, ix_, kept under 63 characters.
6. No reserved words, no type suffixes, no abbreviations that are not universal (id and url yes, cust_nm no).

Where SchemaDrawer fits#

A convention is easiest to enforce when the whole schema is visible on one page. SchemaDrawer runs your migration files in Docker against the engine you choose and draws the tables, columns, types and foreign keys it finds in the catalog, with names exactly as the engine reports them. A stray PetType among snake_case tables, or a foreign key column called owner without an arrow next to it, stands out in a diagram in a way it never does in a directory of migration files. Attaching that diagram to every pull request with the GitHub Action turns the naming review into a glance.

Key takeaways#

• Lower snake_case, unquoted, is the only style every engine leaves alone.
• Singular versus plural matters less than consistency; follow your framework's default.
• Foreign keys are <singular>_id, or the role name when a table references the same target twice.
• Name constraints and indexes yourself; engine-generated names differ and tell the reader nothing.
• Know your ORM's defaults; they are your convention whether you wrote it down or not.