Allowing negative values up to a specific value

Question

I have a situation where we store the value of credits a user has in the database:

`credits` DOUBLE(22,2) UNSIGNED NOT NULL DEFAULT '0.00',

The credits are numbers going from 1 to any positive value. But now there is a situation where we want to deduct credits monthly from users, and I see a situation where users may have 0 credits at the time of the SQL query.

What are the best practices to handle this? I know that allowing negative numbers can create lots of bugs and exploits but we really need to implement something this

So example: Every last day of month we will deduct -1 credit from all users. I use mariadb 10.6.11 but can upgrade to a later version if that helps

Answer 1

If you wish to prevent values below a certain negative value, then you could use a check constraint:

ALTER TABLE your_table
  ADD CONSTRAINT credits_greater CHECK (credits >= -1.0);

And if you try to insert a value below, it fails the check and throws an error:

MariaDB [test]> INSERT INTO your_table(id, credits) VALUES (1, -1.1);
ERROR 4025 (23000): CONSTRAINT `credits_greater` failed for `test`.`your_table`

You could write a trigger to do something similar, or write a trigger to dynamically fix the value being inserted or updated. However, I would argue that it's best practice to use a check constraint whenever that is an option rather than using a trigger. Then let your application handle any errors thrown from the database system whenever a user attempts to violate the constraint.

Triggers have some limitations and are also seen by some as maintenance nightmares. See also MariaDB's list of trigger limitations.

Answer 2

UPDATE ...
    SET foo = GREATEST(foo - 1, -10.00)

This decrements foo by 1 but prevents the result from going below -10. (Maybe this trivial code is what you are looking for?) (LEAST() is the counterpart to that.)

Do not use FLOAT or DOUBLE if you want storage and rounding to 2 decimal places. Instead, use DECIMAL(11,2) to, for example, allow values up to a billion and with 2 decimal places. (As with DOUBLE, DECIMAL is "signed".)

As for controlling limits -- that needs an application solution, not a database solution. However, see TRIGGER and/or STORED PROCEDURE as ways to encapsulate your business logic in SQL.

Every last day of month we will deduct -1 credit from all users

That UPDATE is possible, but it will be painfully slow if you have a billion users. (If you need to discuss that, write a Question focusing just on that.)