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?
Best Answer
The columns you are talking about occupy 20 bytes (if aligned without padding):
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:
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.