I'm looking for the correct way to model the following requirement:
I have a fantasy football league site with 16 teams that maintains a continuous waiver order. The season begins with the teams ordered as such: 1,2,3,4,5...14,15,16. If team 3 makes a waiver move, they go the back of the line: 1,2,4,5..14,15,3.
This sequence changes continuously throughout the season. I've been storing a team's waiver position as a column within the teams table.
'''id, teamName, waiverPos'''
Problem is, when I run an update that affects the waiver positions but then needs to be rolled back, I have no history with which to reconstruct the original sequence. Note: these updates are not always something I can wrap in a DB transaction. They sometimes require rolling back for non-technical reasons.
So the question: What's the best storage model for this kind of sequence so I can restore the sequence in previous states?
One option is an audit table which will record all the changes to the teams table. Use triggers for inserting audit data when the main table values change. Rolling back goes by copying the older values from the audit table.
Another option is to add an increment id or timestamp column to the teams table (and optionally status column) and never update it but always insert new rows describing the current situation. waiverPos won't be unique anymore. Rolling back goes by deleting the newest rows.