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.

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
  • 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 Nov 4 '17 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.

  • 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 Nov 3 '17 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 Nov 3 '17 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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