Relationship Table Anti-Pattern

Consider a table consisting only of foreign key columns. Each row in the table has an implicit meaning that is conveyed via the table’s name. Like table names, this meaning can not be changed easily.

For example, the table EditorRole with documentId and roleId columns specifies which user roles can edit a document.

This seems workable until another capability is added to the application for viewing documents. Controlling access to this new feature requires a new table, “ViewRole” with the same columns as EditorRole.

Tables are heavyweight objects. Tables are often represented as resources. When a new table is added, a new set of CRUD endpoints are usually needed. New tables also complicate reporting, ETL, and auditing features that are often neglected during the design process.

A better solution involves planning ahead. Create a general-purpose table, say DocumentRole, with a column for each capability. DocumentRole has the same documentId and roleId columns and two additional boolean columns: editor and viewer. Thus, when more capabilities are added to the application, lightweight columns can be added to DocumentRole instead of adding heavyweight tables.