I have an app where you can select a person from a list to view more details. Now each time a person is selected to view more details, I want to record that in a recent history table. I'm not sure the best way to approach this to have say the latest 10 person selections.
I know I need to create the history table but am wondering if I should just do an insert for each person click and select only 10 results with the most recent dates or if I should worry about just updating only 10 records to keep the row count low?
Any input would be appreciated.
Best How To :
I would update, otherwise you keep adding more and more data which you are not going to use. Maybe you won't run into problems with this specific case (because people won't select tens of thousands of persons a day), but in general you should be careful with just adding data without cleaning it up.
That said, the first step is the same anyway:
Adding or updating the person
So, if someone selects 'Bob', I would try to update 'Bob' in the history and set his lastselecteddate to now. If that fails, then insert 'Bob', again with the current timestamp.
Cleaning up the older history
After that you can decide whether or not to clean up old history. You could delete all rows but the newest ten, or you can keep a hundred, or not clean them at all. Also, instead of cleaning them up right away, you could make a job that does this once every day. If you clean up right away, you can decide to not do that when you updated a person. After all, if you didn't insert one, you shouldn't have to clean-up one.