I am designing a system which will include a history of edits made to user posts. I expect roughly 1/3 of the posts made by my users to include edits of some kind. Most of those will have only a handful of versions. Reaching the double digits will be very rare.
What is the best schema to represent this data in the database? What tables should I have, and what should they store?
The schema I envision is to have a Posts table and a History table. When someone first makes their initial post, it goes only into the Posts table. Then if they or another user later edits the post, two entries are made to the History table: one for the original version and a second for the edited version. Likewise, the Posts table will be updated with the latest information. Additional edits will produce a single new entry to the History table, and an update of the Posts table.
The one issue I see is that the current version is ultimately duplicated in both tables, but I think the ease of associating users to their edits makes this worthwhile.