Manage user warnings

Question

Hi, I'm not used to work with values that should decrement every a timelapse like for a user warned, for example a warn which persist for 30 days that can reach a maximum value of 3 warns before the user get banned

I thought to design a user table like this but now I should work on it, I find it not useful on decrementing the values every 30 days:

table_user
- username
- email
- warnings (integer)
- last_warn (timestamp data type)

should I use some php timer?

does exist any standard tecnique on user warnings?

Answer 1

Normalize your tables, by breaking out the warnings from the user, like:

Table: Users
UserID  int auto generate PK
UserName
UserEmail

Table: UserWarnings
UserID
WarningDate

you can now write a query to determine if there are three warning in the last 30 days. Run this query when a "warn" happens, and if a row is returned, ban the user.

The query would look something like this:

SELECT
    COUNT(*)
    FROM UserWarnings
    WHERE UserID=...your user id... AND WarningDate>=...current date time...
    HAVING COUNT(*)>2

By making a warning table, you can keep a complete warning history, which may be useful.

Answer 2

You could create another table

User_warnings:
    user_id
    warn_timestamp

Whenever the user is warned, you first delete all entries older than 30 days, then you check if there still exist two or more warnings. Ban the user then.

If you want a history about all warnings, don't delete old warnings, but just query for warnings within the last 30 days.

This way you don't have to decrement every day, but just have to check when another warning appears.

Answer 3

There's really no standard design for user warning systems, I believe. The "three strikes and you're out" is a typical approach, but not always the best. For example, if I have N rules on my website, and we'll say that K of those rules are serious offenses, then the offenses that aren't so serious I would say give three strikes. But maybe the serious offenses are autoban or give two strikes?

If I had to set up something like this, I would create a table that looked like this:

user_warnings:
- warning_id
- user_id
- created_at
- offense_level

And then maybe have a query set up where you could find any users that had a sum offense level over the last T days that were greater than or equal to the value of the bannable offense level. And if their total offense level was over the recommended value, ban the user. I'd say set the offense level to be something like 5, and have tiered levels of offenses.

Never delete past offenses, though, in my opinion. You never know when it's important to remember the stuff that happened previously, and it's good to keep records of it. Just make sure this query only checks the dates that are less than 30 days old (or however many days old that the warnings you're wanting to set can be).