One page, not a manual
The convention nobody reads is the one that runs to ten pages.
A naming convention has only one goal: someone discovering the database should understand what a table or a column contains without opening the documentation. A few rules are enough to get there, as long as they are applied everywhere.
The rules we apply
- A single language. All English or all French, but never a mix: customer_orders or commandes_clients, not a hybrid such as customer_commandes.
- Lowercase and underscores. purchase_orders, not PurchaseOrders or PURCHASE-ORDERS. Database engines do not all handle case the same way; lowercase avoids any surprises.
- Plural names for tables. A table contains rows: customers, invoices. The opposite convention can be defended too; what matters is choosing one and sticking to it.
- A primary key named id, and foreign keys named after the table they point to: customer_id points to customers. You can read the relationship without checking the schema.
- No home-made abbreviations. qty_rcv_dt saves ten characters and costs every reader ten minutes. received_at explains itself.
- Suffixes that state the type. _at for a date and time (created_at), _on for a date only, is_ or has_ for a boolean (is_active), _count for a number, _amount for an amount.
- Join tables named after both tables, in the singular and in alphabetical order: product_supplier.
The details that make the difference
- Indexes and constraints have explicit names: idx_invoices_customer_id, uq_users_email. The day a constraint blocks an insert, the error message tells you immediately which one.
- Status columns have readable values: pending, paid, cancelled rather than 1, 2, 3. A numeric code forces everyone to keep a lookup table in their head.
- Units appear in the name when they are not obvious: weight_kg, duration_minutes, amount_gnf. Nobody has to guess whether an amount is in Guinean francs or in dollars.
Why it pays off
Each rule seems trivial. Together, they change day-to-day work. Queries can be written without checking the schema. Join errors are spotted just by reading. Visualization tools and frameworks guess the relationships correctly. And a newcomer to the project becomes autonomous in a few days rather than a few weeks.
Conversely, a database without a convention piles up variants: client, clients, tbl_customer, CustomerData, sometimes for the same thing. Each variant is a question asked of a colleague or, worse, a guess.
What about the existing database?
There is no point renaming everything at once: it is risky and rarely a priority. We apply the convention to everything new, and rename existing objects as things evolve, when the table in question is being touched anyway. Properly named views can also serve as a clean front over an old schema while the migration takes place.
Name a table the way you name a variable in well-written code: for the next reader, not for yourself.
The full convention fits on one page. Print it, pin it up, and have it checked during code reviews: that is the simplest way to see it respected.