Best Practices for Technical Columns in Database Design
Technical columns play a fundamental role in database design. They serve various purposes: from audit-related matters, via support in troubleshooting, to key impact on ETL/ELT process design. Yet, they are often overlooked or marginalized. In this article, I've gathered recommendations based on the experience I gained in various database-related projects – web applications, data warehouses, scientific projects, etc. Whether you're a seasoned data engineer, a developer refining database specifications, or an aspiring data architect, these insights will help you design and build more robust and reliable database systems.
Before going into details of specific technical columns, remember that it's a good practice to start by documenting them in a database specification. You can do it once for all tables in a dedicated part of the specification, instead of repeating the same descriptions for each table.
Remember to specify for each technical column:
- its name – should be used consequently in each applicable table and compliant with the defined naming convention,
- its data type – to make sure the data types for the same columns are coherent between tables,
- its description – so everyone knows how to fill the values (e.g., using what kind of formulas or data mappings) and how to interpret them.
My battle-tested practice is to prefix the name of each technical column with an underscore character (_). I've borrowed this technique from programming languages, where it's pretty common to use underscore prefixes for special types of attributes (like non-public objects in Python; see
