I am making a watchlist type application that will get all items being watched and then poll an api every ten minutes or so to check on its status.
I am trying to design the database portion of this project and wanted some advice on the best technology to use. I am very experienced in relational databases but have used MongoDB for this project.
What I am thinking of doing is having two tables one watch_list table and one item_status table. One application can get a distinct list of items from the watchlist table and then poll the api and then update the item_status table when it likes, regardless of what's going on with the watchlist, can also multithread with the API. If the API returns a true status some action needs to happen. The other app simply takes requests and puts items in the Watch_list table. THe problem I foresee with this is that we are storing info we don't really care about in the item_status table, i.e. once the status is true then some action happens and we don't care about it anymore job done, no one can put that item back in a watchlist until it is false again so you could even just delete those records.
This leads me to the idea that perhaps I only need one table, which includes a flag to if the status has been returned as true and the action has happened, as well as the time it was last checked.Only reason I don't like this however is that I will be hammering this table with updates from calling the API to check if the item status is true so I'm worried about writing new items into this table also I like the idea that I can have a separate entity for polling and that can just run in the background updating the item_status table.
As well as this I'm trying to understand which technology would be best for this data, postgres or mongodb are my preferred two choices, my data is incredibly relational so I am finding it difficult to think of how mongodb would provide a benefit. As I am going to be updating fields a huge amount I would like to know which technology would be more suited to this kind of project.