Manage user warnings

Question

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.