I am doing a work in which I am stuck in deciding in between different techniques to follow a simple rule of my project. The problem is that I have to check if the user is perform an insertion task for the first time or not. because if the user is doing it for the first time I have to give the user a bonus point. So, the thing is that every time when a user performs the insertion task "Do I have to check the whole table against that user if the user is posting for the first time or not? Or I should add a column/ a flag in my table, for this purpose. I am stuck in the pros and corns that which is fastest and memory efficient if in case the user base increases to thousands.
Thanks in advance, :-)
It sounds like the design decision you are going back and forth on is a special case of what is called "materializing a view". A view stores a query, and makes the result available as a table. A materialized view stores the result of the query. The pros and cons you have discovered are real.
In the case of a single flag, that's extremely space efficient, and therefore time efficient as well. The question you really want to ask yourself is this: is the speed up when the flag is used worth the slowdown when the flag is updated? There is no universal answer.
You may also want to write some extra code that will reconcile the flags with reality by scanning the entire database. Whenever the same fact is stored more than once, the possibility of contradiction arises. Theoretically, this can't get screwed up. In reality, Murphy's law prevails.