Naming tables and columns
Singular or plural, id or user_id, is_ or has_ — the conventions worth holding to, and the ones that only matter because they are consistent.
Most of it only matters because it is consistent
Almost every naming argument has two defensible answers. Singular or plural table names, id or table_id for a primary key, created_at or created_date — each has a case, and none of them will slow down a query or lose data.
What costs real time is a schema where half the tables went one way and half the other. Someone writing a query has to look up which table uses which rule, every time. So the rule to hold is: pick one, write it down, and change it only with a migration that changes everything.
The sections below say which side to pick when you have no existing schema to match. If you do have one, match it — even where you disagree.
Tables
| Rule | Example | Why |
|---|---|---|
| Plural | users, orders, order_items | A table holds many rows; a row is the singular |
| snake_case | order_items | Unquoted identifiers fold to lower case in Postgres and Oracle anyway |
| No prefix | users, not tbl_users | The prefix repeats what the context already says |
| Junction: both, joined | post_tags, listing_amenities | Reads in the order the relationship is usually traversed |
| Name it when it grows | enrollments, not student_courses | A junction with columns of its own is an entity |
Plural is the majority convention and the one most ORMs assume. Singular has the better argument — a table is a set and the name describes its members — but it is a minority position and switching to it costs more than it gains.
Keys
| Column | Convention | Note |
|---|---|---|
| Primary key | id | Short, and unambiguous inside its own table |
| Foreign key | <singular>_id | user_id, listing_id — names the target table |
| Two FKs to one table | <role>_id | reviewer_id and reviewee_id, not user_id_1 and user_id_2 |
| Composite junction key | (a_id, b_id) | The pair is the key; a surrogate id adds nothing |
| Natural key | the thing itself | country_code, currency_code — when the value is stable and meaningful |
The argument for user_id over id as a primary key is that a join reads without aliases. The argument against is that every table then repeats its own name in its most-used column. id is the more common choice and it makes the foreign key convention — user_id points at users.id — obvious.
Columns that follow a pattern
| Kind | Convention | Example |
|---|---|---|
| Boolean | is_ or has_ | is_active, has_paid |
| Timestamp of an event | <verb>_at | created_at, published_at, deleted_at |
| Date without time | <verb>_date or <noun>_date | due_date, birth_date |
| Count | <noun>_count | follower_count, retry_count |
| Money | <noun>_amount or <noun>_total | discount_amount, line_total |
| Enumerated state | status or <noun>_status | status, payment_status |
| Ordering | position or sort_order | position within a parent; sort_order globally |
The _at suffix earns its place: it says the column is a moment rather than a flag or a duration, which is exactly the thing that gets confused. deleted_at reads as a soft delete without a comment; is_deleted plus deleted_at is two columns saying one thing.
Words to avoid
- Reserved words — order, user, group, table, key. They are legal if quoted, and quoting them forever is a tax. Use orders, users, groups.
- Abbreviations that are not universal — usr, dt, amt. The keystrokes saved are paid back the first time someone guesses wrong.
- Type in the name — name_varchar, is_flag. The type is already in the schema.
- Meaningless suffixes — user_data, order_info. Every table holds data.
- Reusing a word with two meanings — status meaning both 'account state' and 'payment state' in the same schema. Qualify one of them.
user is the one that catches people out, because it is reserved in PostgreSQL and works in MySQL. A schema that works on one and needs quoting on the other is a migration waiting to happen; users avoids the whole question.
Logical and physical names
A physical name is what the database sees — order_items. A logical name is what a person calls it — 주문 품목, Order Lines. Keeping both lets a diagram be read by people who do not write SQL, and lets a glossary exist without renaming columns.
The discipline that makes it work is one term per concept. If a diagram calls the same thing 'member' in one place and 'user' in another, the logical names have stopped being a glossary and started being a second source of confusion.
Frequently asked
- Singular or plural table names?
- Plural, unless you already have a schema that is singular. It is the majority convention and what most ORMs assume. The singular argument is coherent but it is a minority position and consistency beats it.
- Should the primary key be id or user_id?
- id. It keeps the column short where it is read most, and it makes the foreign key rule obvious: user_id points at users.id. The alternative reads slightly better in joins and costs a repeated table name everywhere else.
- What do I name a junction table?
- Both tables joined — post_tags, listing_amenities. If it grows columns of its own, it is an entity and deserves a real name: enrollments rather than student_courses.
- Why is naming a table 'user' a problem?
- It is a reserved word in PostgreSQL and has to be quoted, while MySQL accepts it. A schema that works in one and needs quoting in the other will eventually be migrated by someone who does not know. users sidesteps it.
- is_deleted or deleted_at?
- deleted_at alone. It answers both whether and when, and the flag adds a second column that can disagree with it. Filter on deleted_at is null.