Re: Snapshot Table -- over the long run

[Rick James]

* 2 tables -- one for live, one (or more) for history.
* Do not try to use exactly the same schema for both.
* Consider splitting the 'history' into two parts -- TEXT changes and meta changes. I suggest this because of the relative bulk. Let's call them TextHistory and MetaHistory
* The Live table will be accessed most of the time, right? (If it is more like a forum / message board / blog; some of my statements here are not quite optimal.)
* As a change is made, bump an id, and move the current stuff to the history table(s)
* Suggest compressing the TEXT field
* The id for the history is not the same as the id for the main table.
* Ponder whether to have each item's history id being 1,2,3, independently of other items. Or whether to have a single auto_increment id for all the history.
* TIMESTAMPs are useful. But they are not useful for sequencing things -- two items could come in during the same second.
* One approach to the History tables: The TEXT goes into one table, calculate the MD5 for dedupping; use the MD5 (or an id) as the id into that table. Then use that id in the MetaHistory table. Every edit causes another row in the MetaHistory table, but (by dedupping), it may not cause another row in the TextHistory table. TextHistory is useless without joining to MetaHistory.
* If you have lots of fields to 'blur', it would be a mess to try to store the changes field by field. I think it would be better to let the rows in MetaHistory be largely repetitive.
* Normalize some of the fields, such as 'user/author'.
* Ponder your SELECTs while designing your schema. Some will touch only Live. Some will look at the history for a single item. Think about how you will locate an item, or set of items.

Ponder those, then present a set of CREATE TABLE statements for critique.