Databases
StackOverflow2013
Postsdbo
POHow do you avoid adding timestamp fields to your tables?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do you avoid adding timestamp fields to your tables?
    text
    copied!<p>I have a question regarding the two additional columns (timeCreated, timeLastUpdated) for each record that we see in many solutions. My question: Is there a better alternative?</p> <p>Scenario: You have a huge DB (in terms of tables, not records), and then the customer comes and asks you to add "timestamping" to 80% of your tables.</p> <p>I believe this can be accomplished by using a separate table (TIMESTAMPS). This table would have, in addition to the obvious timestamp column, the table name and the primary key for the table being updated. (I'm assuming here that you use an int as primary key for most of your tables, but the table name would most likely have to be a string).</p> <p>To picture this suppose this basic scenario. We would have two tables:</p> <p>PAYMENT :- (your usual records)<br> TIMESTAMP :- {current timestamp} + {<code>TABLE_UPDATED</code>, <code>id_of_entry_updated</code>, <code>timestamp_type</code>}</p> <p>Note that in this design you don't need those two "extra" columns in your native payment object (which, by the way, might make it thru your ORM solution) because you are now indexing by <code>TABLE_UPDATED</code> and <code>id_of_entry_updated</code>. In addition, <code>timestamp_type</code> will tell you if the entry is for insertion (e.g "1"), update (e.g "2"), and anything else you may want to add, like "deletion".</p> <p>I would like to know what do you think about this design. I'm most interested in best practices, what works and scales over time. References, links, blog entries are more than welcome. I know of at least one patent (pending) that tries to address this problem, but it seems details are not public at this time.</p> <p>Cheers, Eduardo</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload