Naming Junction, Audit and Archive Tables
Ordinary tables are easy to name. The trouble starts with the special ones: the junction tables that connect two entities, the audit tables that record history, and the archive tables that hold the past. This guide gives each category a naming rule so your schema explains itself.
Junction tables: name both sides
A junction table (also called a link table or associative entity) exists to model a many-to-many relationship — students and courses, orders and products, users and roles. Its name should contain both entity names so the relationship is visible without opening the table definition.
The convention that scales best is alphabetical order with a connecting word: course_student, order_product, role_user. Alphabetical ordering removes a whole class of arguments (is it student_course or course_student?) and keeps related tables adjacent in alphabetical listings.
Some teams prefer explicit link words: student_enrollment, order_line_item. This works well when the relationship itself has meaning beyond the link — an enrollment has a date and a grade; a line item has a quantity and a price. If the junction table carries its own attributes, naming the relationship rather than the two tables is often clearer.
Self-referencing junctions
When a table relates to itself — employees and their managers, users and their followers — the alphabetical rule produces a stutter like user_user. Here, name the roles instead: employee_manager, user_follower, account_membership. The role names describe the relationship's direction, which is exactly the information a reader needs.
Audit tables: suffix, don't prefix
Audit tables record the history of another table: every insert, update and delete, usually with a timestamp and the acting user. The most readable convention is a suffix on the base table name: order_audit, customer_history, invoice_log.
Pick one suffix for the whole schema and stick to it. _audit, _history and _log all work; what fails is using all three for the same purpose. Suffixes beat prefixes here because the audit table sorts next to its base table in alphabetical listings — order and order_audit sit together, while audit_order lands in a different neighborhood.
Audit tables deserve a few columns of their own beyond the mirrored data: the action taken, the timestamp, and who (or what process) performed it. The name promises "this is the history of X" — the columns should deliver it.
Archive tables: mark the lifecycle stage
Archive tables hold data that is no longer active but must be kept — old orders, expired sessions, soft-deleted records awaiting purge. Their names should make the lifecycle stage explicit: order_archive, session_expired, customer_deleted.
Two rules keep archives safe:
- Never let an archive name collide with an active table's pattern. If active tables are plural and the archive is
orders_archive, a careless query can't accidentally swap one for the other. - Document the retention rule in the name or a comment.
invoice_archive_7ytells the next engineer that seven years of retention is a requirement, not an accident.
Temporary and staging tables
ETL pipelines and migrations create transient tables. Name them so their disposability is obvious: tmp_customer_import, staging_order_recalc. The tmp_ or staging_ prefix is a promise that the table can be dropped without ceremony — and a prefix here (rather than a suffix) is deliberate: transient tables should sort together at the top of listings, visually separated from the permanent schema.
A complete naming map
Putting the rules together for a small ordering system:
customer,order,product— core entities, singular nounsorder_product— junction: alphabetical, both sides namedorder_audit— audit: suffix, sorts with its base tableorder_archive— archive: lifecycle stage explicittmp_order_migration— transient: prefix marks it disposable
Read that list aloud. Every table's purpose is guessable from its name alone, and no two categories can be confused. That is what a self-documenting schema feels like.
Frequently asked questions
Singular or plural for junction tables?
Follow whatever your schema uses for regular tables — consistency matters more than the choice. If your entities are singular (order, product), the junction should be singular too (order_product).
What if three tables are joined in one junction?
Rare, but it happens (a promotion applying to a product in a region). Name all three alphabetically: product_promotion_region. If the name gets unwieldy, that is a hint the relationship deserves its own entity name instead.
Should audit tables live in a separate schema?
Many teams put them in an audit schema, which simplifies permissions and backups. The table names stay the same either way — audit.order_audit is redundant but harmless, so some teams drop the suffix when a dedicated schema already says "audit".
How do I name a junction table in a legacy database I can't rename?
Leave the old names alone and apply the convention to new tables. Add a comment or a data dictionary entry mapping the legacy names to the convention, so new team members can translate.
Do these rules apply to NoSQL collections?
The spirit does. Document databases rarely need junction collections, but audit and archive collections benefit from the same suffix conventions — orders_archive reads just as clearly in MongoDB as in PostgreSQL.
Special tables earn their keep by being instantly recognizable. Give junctions both sides, audits a suffix, archives a lifecycle — and use TableTonic to generate candidates in the right casing and separator style before you commit them to a migration. Column names deserve the same treatment — see our guide on renaming columns safely — and for the fleet-level conventions, our database environment naming guide.