How to design a position tracking DB?
Whats the best practice for designing a system to monitor and track changes in an ordered list?
That probably sounds a little obscure, but I'm thinking of the scenario where you monitor over time changes in league rank positions for example - i.e. storing top 100 products over time. For now its best to assume this is a seperate DB entity, storing only this 'league'.
It obviously makes no sense to store the entire league over and over and then compare across different dates, thus I assume you just want to store changes as they occur? New entrants, change in position/rank, changes in composition maybe (new title?).
I've attempted searching but not found anything, any suggestions on how to describe this problem?
I'm a little out of my depth with this. Any advice would be much appreciated.