Column naming conventions: make roles, units, and time visible
Review a schema with two address roles, monetary units, lifecycle timestamps, and examples of meaningful names in joins.
A consistent convention lowers the cost of reading and joining a schema. Use one case style, such as snake_case, and name primary keys predictably: customer_id, order_id. Foreign keys should reveal both their meaning and target, such as billing_address_id rather than a bare address_id when several addresses are possible.
For booleans, names that read as a question—is_active, has_paid—make conditions clear. For time values, distinguish an event from a period: created_at, paid_at, starts_at, and ends_at. Avoid encoding storage types in names; a type can change while the business meaning should remain stable.
Two foreign keys to one address table
An order can have a shipping address and a billing address. Names such as address_id1 and address_id2 force every query author to memorize a convention. Use role-specific foreign keys and aliases instead. The referenced parent is the same table, but the relationship has a different business meaning.
SELECT o.order_id,
shipping.city AS shipping_city,
billing.city AS billing_city
FROM orders o
JOIN addresses shipping
ON shipping.address_id = o.shipping_address_id
JOIN addresses billing
ON billing.address_id = o.billing_address_id;This query assumes both roles are required. If billing is optional, use a LEFT JOIN for billing so a missing billing address does not remove the order from the result. If an old invoice must retain its original address, store an order-time address snapshot; a role-specific name alone does not preserve history.
Review names against three concrete questions
| Question | Ambiguous name | More explicit choice |
|---|---|---|
| What unit is this amount in? | amount | total_amount_krw for integer KRW |
| Which event happened? | date | ordered_at or paid_at |
| Which parent role is meant? | address_id2 | billing_address_id |
| Which lifecycle owns the status? | status | payment_status in a payment context |
Record a currency rule even when it is not embedded in every column name. A multi-currency system may use amount_minor plus currency_code instead. Record whether timestamps are stored in UTC and how displays convert them. An is_paid flag can lose meaning when partial refunds exist; an explicit payment state or derived balance may describe the business more accurately.
Renaming is a compatibility change
Read a join without the application code and ask a teammate which address would go on the shipping label. If the answer is unclear, the naming convention has failed its purpose. Prefer a small documented vocabulary over abbreviations that only one developer understands.
When changing a name, inventory API fields, saved exports, reporting queries, and migrations. A database rename can break consumers even if the ERD looks cleaner. Keep logical names for the business vocabulary and physical names for the database convention; YourERD lets you display both.
Reference and next step
Examples use simplified business rules for teaching. Check the database documentation for the exact enforcement behavior of your chosen engine.
Open the ecommerce sample in YourERD · Explore the sample models