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.

No Longer SetMike Wolfe

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.

No Longer SetMike Wolfe

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.

No Longer SetMike Wolfe

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)