I will have a database of products where each record will have a number associated with it, let's say, the number of "sales".
I will periodically update the database so each "sales" field for each record will be updated.
I need to sort these records based on the "sales" columns, descending. I need to track the "ranking" of each of those record at each database update. So if a record is number 1 when database is sorted by "sales" descending, I need to store this information. When I update this database and that records now is the 5th still when database is sorted by sales descending, I need to record this information. So then I can reconstruct the ranking evolution of each record.
Is this clear ?
I see 2 ways of doing so: For each record and on each database update, create a foreign key record with position and date. The database will grow a lot with each update since each record will be associated with a new foreign key.
Another way of doing so: do not create a foreign record but somehow store this information on the record itself, not sure exactly how.
How would you do this? What's the proper way of doing ? This system intends to use a postgres DB.