1

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.

Improve this question

2 Answers 2

Reset to default
0

Some suggestions:

  • You do not need to create new records for all products every time, you only need to create for those that change their ranking position
  • I did a quick search and found out Postgres has array and json type. I am not sure if you can have array of json in a table. If yes, you can have an array of json having ranking position and timestamp within your record. Push new json to this array every time ranking position is changed
  • You also need to check the size limit of the array field. If it does not match your system expectations you will need to create new records of primitive types
  • Or still using array of json, but inside new records. Check the array size every time you update, if the size is at limit create a new one with same foreign key, push the json to its array, use an index/timestamp field to know the order of records with same foreign key
Improve this answer
1
  • 1
    Thanks for sharing your suggestions. There are 7K records and they change everyday which would lead to millions of records/year. I went for the postgres array solution: I create a single record per product (1/1 relation) in another table and use the pg array there to push new ranking positions and their timestamp. There is no particular limitation on array size: it is subject to the global pg column size which is more than enough. I will see how practical this is on a day to day basis, I can always revert to the traditional one to many relation shall my current solution doesn't prove good.
    – Robert Brax
    Commented Nov 4, 2017 at 9:26
1

On each update or insert create a database trigger. Inside that trigger search for the top sales item and store its ID in a separate table or whereever you want to store it with date and position. Read about triggers here.

Improve this answer
2
  • 1
    so your approach is to create another table, and use the foreign key method with one new entry for each record on each update.
    – Robert Brax
    Commented Nov 3, 2017 at 11:37
  • 1
    On each update your trigger would run and then you can add all changed rankings at once with date time
    – Farrukh Subhani
    Commented Nov 3, 2017 at 11:43

Not the answer you're looking for? Browse other questions tagged or ask your own question.