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

Question

I often have fields in my database which store "a number or infinite". Examples could be maxFileSize, maxUsers, etc. It shall be possible to set a maximum value or just don't restrict it at all. I currently sometimes use null to indicate "infinite / no restriction" and sometimes use -1.

I was wondering if there is any reason to use the one or the other. For the record, I work with PHP / MySQL / Doctrine / Symfony, if that matters.

Edit:

I am not asking for opinions but for facts (as you see in the answers already given) related to the usage of -1 or null. These might include speed, query complexity, database size, and so on.

Answer 1

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 null
where 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.