Is it better to use `null` or `-1` to indicate "infinite" in integer columns of the database

If you are going to use an integer to store a value and you want to store a really large value, then use 2,147,483,647. This is the largest value you can put in the field, so it is as close to infinity as the field allows.

There are issues with both -1 and NULL. For instance, perhaps the value has not been set yet. That seems like a much more reasonable use for NULL than to represent some arbitrary value. And using a special value requires complicated logic:

where users < maxusers or maxusers is nullwhere users < maxusers or maxusers = -1

This specialized logic makes the query harder to optimize. This is not an issue if you use the maximum value that can be stored in the field.

By the way, I don't often encounter this problem with numbers, but it is very common with dates. Often "end dates" in tables for slowly changing dimensions will be something like: '9999-01-01' or '4195-01-01'. These represent some arbitrary point far into the future. And, they are usually easier for optimization purposes than using NULL.

Just imho

null - is expected by any framework or query as empty or not defined value in many cases. but if you try to use -1 or anything else. some query functions SUM(), AVG() etc could be broken by this value.

So I see no reason to get bad habit. null is null and everybody knows that null can exists in table and means empty or not defined.

In case you will place -1 that means not empty and defined so for me that is absolutely opposite values.

Can you ask if we can use 1 instead 0, true instead false?

if your value is defined and not empty - you can use whatever you want.

and if your value is not defined and/or context is value is empy I would suggest use nothing that equal null.

You may also choose to use two fields in your database to achieve the result:

file_quota int NOT NULL DEFAULT 50000,file_quote_is_limited boolean NOT NULL DEFAULT true

There are a couple of ways to store boolean values in MySQL which can of course impact database size depending on your scale compared to simply storing unlimited as NULL. However, there are some advantages of this technique:

• Even if you do not document that NULL means unlimited, when seeing the mapping it is more clear what is meant. (Consider new developers, etc.)
• It is impossible to find if (null == $obj->getFileQuota()) { or similar constructs in your code. Those snippets are generally smelly and not maintenance friendly.
• Query complexity depends on what you are trying to achieve, but the two fields do not add unforeseen complexity. In fact, it makes it easier to understand what is happening:
  where file_quota < 50000 or file_quota is null
  vs.
  where file_quota < 50000 or ! file_quota_is_limited (the negation depends on your chosen datatype to map booleans, but Doctrine should take care of that)
• If you happen to have $usage = $usedFiles / $user->getFileQuota(); in your code, you can even prevent division by zero from occurring by constraining the database column appropriately.
• You can further extend your logic to map even more states.