Best practice for storing record metadata 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:


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

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 

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?
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.