0

Say I have a website/platform that allows people to submit business names in order to build a high quality business name database. You can apply this to any subject or topic, such as building a database of travel destinations, of movie titles, of quotes, etc. One of the main problems is how to guarantee accuracy and quality.

So what I'm thinking of doing is to sort of gamify it like StackOverflow and related sites. Essentially offering badges for contributing. You get some badges for adding content, cleaning up content, verifying content, etc.. But the problem then is how do you prevent people from cheating the system?

For example, say a person uploads a new business name. That's 1 creation point. But it was spelled wrong, so update business name. That's 1 update point. Now change it back to original incorrect spelling, 1 more update point. Change it back, another point, etc.. Back and forth and back and forth. Rack up the points.

So the initial question is, how do you prevent this? What I'm thinking about is somehow tracking the changes to a record. If someone notices a person doing this, then they get reported and then lose all their points related to the cheating attempts. Something like that.

But imagine recording the snip/snap/snip/snap of the changes to and from values. 1000 changes to one record, adding and removing random characters. If you had a database table with a "history" table attached to it for changes to a field, it would fill up quickly. So the main question is, how do you effectively do this? How do you keep a small long of changes, and prevent the cheating?

For example, say user A creates a business "Foo Bar", then user B changes it to "Foo Bar Baz", then user C changes it back to "Foo Bar", then user A to "Foo Bar Inc." (all caps) then finally several users verify it is "Foo Bar Inc.". Say someone else comes along now after it's been verified a few times, and they say it's "Foo Bar" again. We look in the history table and see this option has been tried before and it was marked invalid. That would be efficient in this case.

But what about the random case mentioned before (one user spams the system entering "asdfcda" then "cidajcoid" then "ciadojc" as the business name to rack up points). If the history table grows and grows to 10,000 items long for each of the 100,000 businesses, it's going to take a while to check every history list for each change. So somehow you need to optimize this. How can this be optimized?

In short, how do you effectively track the history of changes to a text field in the database on a content editing platform?

Has anyone elaborated on the problems and solutions before online somewhere? If not, what are the key points to address in an initial prototype?

Improve this question
4
  • Related: stackoverflow.com/questions/9484714/… Commented Dec 15, 2020 at 16:58
  • 1
    It sounds like you want to use change tracking as a way to mitigate spam submissions, but it's probably not the best tool. I would find a solution to the real problem, and if you can't trust the people entering the data then you've got a design issue. Commented Dec 15, 2020 at 17:00
  • What do you mean, fill up? If you want to store the history then you need enough disk space to store the history. If you have enough disk space then your table doesn't "fill up". Commented Dec 16, 2020 at 14:33
  • As for "optimization"... look up "database indexes" Commented Dec 16, 2020 at 14:34

2 Answers 2

Reset to default
5

From technical perspective

Some databases already implement what you are looking for. For instance, SQL Server has a feature called Change Data Capture, which makes it possible to track the changes to a table.

If the history table grows and grows to 10,000 items long for each of the 100,000 businesses, it's going to take a while to check every history list for each change.

It depends on what you mean by “a while.” One hundred thousand records is nothing for any database. Things could get weird if you're hosting the app on a Raspberry Pi 3, and not setting any indexes. But you aren't doing that, do you?

Additionally, I'm not exactly sure how would keeping the records of what happened three years ago would help you fighting people who abuse the system.

From business perspective

Beyond purely data storage considerations, you should ask yourself why would anyone try to cheat the system, and how can you make it less interesting to do it. Are users paid to make the modifications? What makes you think that a given person has nothing more interesting to do than to keep doing the same change back and forth to gain points, and keep doing it for... what? Hours? Days? Months?

You can take additional measures, such as keeping plain text logs of the modifications for a short period of time (such as a week) and, from time to time, go check the profiles of the users who contributed the most.

Or you can limit the number of submissions per period of time, say maximum 20 submissions per minute, and no more than 50 submissions per hour, and no more than 150 submissions per day. Beyond that point, your app thanks the user for his valuable contribution, and invites him to come back later.

Or you do pair review. This is for instance the system in place for Google Maps. When you do a modification, it is not published immediately. Instead, it goes in the review queue, and appears publicly only after being approved.

Improve this answer
2
  • Where can I find more info about the Google Maps review queue process? I'm wondering how you couldn't just cheat that, hire your friend to approve your submissions, etc. Commented Jan 12, 2021 at 22:47
  • @LancePollard: an approver doesn't choose what to approve—Google does. Play with Google Maps, and you'll see by yourself how it works. Commented Jan 12, 2021 at 23:40
4

Sounds like you need audit table to track the database operation histories. There are supports for such use case (e.g., temporal table as a system-versioning table implemented as a pair of a current table and a history table), or you can just create your own event tables that record something like "user A made operation B on C at time D", and this event update can be triggered by the actual table operations.

To prevent abusing some features, maybe it's better to limit the rate on operations (e.g., user A is only allowed to operate B every C hours, and max awards one can get from operation B is D points, similar with what you can maximumly get from editing posts here in Stackoverflow).

To avoid record size blowing up in database, you may consider expire the old records or migrate them to cold storage, so the business "Foo Bar" in your example is allowed again after X updates (similar with the use case in which your password is not to be reusing the previous 6 ones).

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.