Best practice for storing record metadata

Question

What is the best practice for storing metadata of individual records in a database?

I need to store common meta data such as creation time and time of last update for many tables in my database. I found a few different solutions:

1. Store the meta data directly in the tables.

   Pros:

   • Meta data is directly linked to records
   • No joins are required to retrieve meta data

   Cons:

   • A lot of duplicate columns are required (unless inheritance is used)
   • Meta data and business data are not separated

2. Create a general meta data table with and use soft foreign keys to link data to the correct tables and records.

   Pros:

   • No duplication of columns
   • Meta data is separated from business data

   Cons:

   • No direct links between meta data and data (FK's can't be used)
   • Joins require an additional condition

3. Create individual meta data tables for each table requiring meta data.

   Pros:

   • Meta data is directly linked to records
   • Meta data is separated from business data

   Cons:

   • A lot of extra tables are required
   • A lot of duplicate columns are required (unless inheritance is used)

Are there more options, pros or cons than the ones I mentioned here? And what is the best practice for storing this meta data?

Answer 1

The columns you are talking about occupy 20 bytes (if aligned without padding):

creation time, update time and creation source

timestamp .. 8 bytes
timestamp .. 8 bytes
integer .. 4 bytes

The tuple header and item identifier for a separate row in a separate table alone would occupy 23 + 1 + 4 = 28 bytes plus the 20 bytes of actual data, plus 4 bytes of padding at the end. Makes 52 bytes per row. See:

• Configuring PostgreSQL for read performance

Concerning storage you have nothing to gain. Concerning performance you hardly lose anything with just 16 - 24 bytes more per row.

The columns also directly belong to the row, so it makes sense to keep them together. I make it a habit to add exactly such columns (plus separate source for the last update) to all relevant tables.

It's also easier to write a TRIGGER ON INSERT OR UPDATE to keep them current.

Long story short: a strong vote for your option 1.

Where I would go for option 3:
If the metadata is updated often, while the core row is not. Then it might pay to keep a separate 1:1 table to make UPDATEs cheaper and reduce bloat on the main table - or even go for option 2.

Where I would go for option 2:
If the set of metadata columns is highly repetitive. You could have a FK column to the set of metadata in the main table(s). Does not save much for three small columns like in your example.