Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
What kind of metadata are we talking about? Maybe using a hstore or JSON column could solve your problem? –  a_horse_with_no_name Jun 12 '13 at 11:15
    
@a_horse_with_no_name - Right now I only need creation time, update time and creation source. The fields are fixed so I don't need key-value like storage. I'm only worried about where I should store the data. –  Tiddo Jun 12 '13 at 12:42
1  
Then I don't see any reason not to add those three columns to the base table. –  a_horse_with_no_name Jun 12 '13 at 12:55
add comment

1 Answer

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 index pointer for a separate row in a separate table alone would occupy 28 bytes plus the 20 bytes of actual data. Read more here:
Configuring PostgreSQL for read performance
Concerning storage you have nothing to gain. Concerning performance you hardly lose anything with just 20 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.

share|improve this answer
    
Thanks, this was a way better answer than I expected! –  Tiddo Jun 14 '13 at 11:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.