The 13 Kinds of Business Application Tables

Database tables in a line-of-business application generally fall into one of these thirteen categories.

The 13 Kinds of Business Application Tables

I've spent 15 years designing, developing, and maintaining dozens of business applications.

During that time, I've noticed the database tables in a business application typically fall into one of the following categories:

  1. Main tables: these are the tables that sit in the middle of a database diagram with multiple connections to detail tables, lookup tables, bridge tables, and large-field tables (these tables get Tabbed Master-Detail Forms in my applications)
    e.g.: Invoice table
  2. Detail tables: think of these as "sub-tables" of the Main tables (these tables populate subforms on the tabs to the right of a Master-Detail form in my apps)
    e.g.: InvoiceItem table
  3. Lookup tables: in my apps, these tables typically have three fields: (1) an autonumber primary key, (2) an abbreviated 2-5 character code, and (3) a full description; they are usually represented with combo boxes
    e.g.: luProductCategory table
  4. Bridge tables: the tables that sit in the middle of a many-to-many relationship
    e.g.: Product_Supplier table
  5. Large field tables: one-to-one tables that hold things like BLOBs (varbinary(max)) and non-indexable nvarchar(max) fields; these generally have only two fields: the primary key of the associated table and the large object/text
    e.g.: Dwelling-Sketch table
  6. Temporary tables: used for performance reasons or as a workaround for non-updateable query errors
    e.g.: tempInvoiceAgingReport table
  7. Denormalized tables: these are tables whose values have a dependency on data in other tables; may be used in rare situations for performance reasons; they require special care to maintain, so should be avoided if at all possible
    e.g.: CurrentInventory table
  8. Settings tables: single-row tables used to hold global client settings, such as network folder locations
    e.g.: GlobalParams table
  9. Staging tables: temporary tables specifically used to support data validation and cleanup processes when importing from external sources;
    e.g.: StripeTransactionImport table
  10. System tables: local tables whose data impact program logic; I export the contents of these tables into version control
    e.g.: eSyncTables table (note: the "e" prefix stands for "embedded" in my table naming convention)
  11. Audit tables: tables that track changes to data over time for auditing purposes; these generally include a date/timestamp of when the change was made and by whom
    e.g.: VendorHistory table
  12. Log tables: "used to maintain a log of imported files so the user can see what they imported over time" -Laurie Almoslino
    e.g.: FileImportLog table
  13. Version/Changelog tables: a table to track changes made to the front-end application over time; change notes may be user- and/or developer-centric
    e.g.: Changelog table

Special thanks to Joakim Dalby, Ben Clothier, and Laurie Almoslino for contributing additional categories beyond my original list of seven.

What did I miss?  Let me know in the comments below.

Referenced articles

Tabbed Master-Detail Form
The tabbed master-detail form is the heart of my Access applications. The first tab has record details, while the other tabs provide subforms for one-to-many relationships.
Abbreviations in Lookup Tables
One way to boost the signal-to-noise ratio on your continuous forms is to use abbreviations rather than full descriptions for lookup tables.
Enforcing a Single-Row Table in MS Access and SQL Server
There are many uses for a table with one--and only one--row in it. Using such a table is simpler if you can rely on the sanctity of its one-row-ness.

Image by Lubos Houska from Pixabay

UPDATED [2022-05-27]: Added category 8, "Settings tables."  (h/t Joakim Dalby on LinkedIn)

UPDATED [2022-05-29]: Added category 9, "Staging tables." (h/t Ben Clothier in comments below)

UPDATED [2022-05-29]: Added categories 10 - 13, "System, Audit, Log, Changelog tables." (h/t Laurie Almoslino in comments below)

All original code samples by Mike Wolfe are licensed under CC BY 4.0