Representing unlimited in database with integer data type

Question

I have a table that saves some account limits like users. For most rows this value will be an integer. However I will also need to support storing unlimited (infinite) as a value. This might seem silly but is there a common integer value that represents unlimited (infinite)? I'd like to keep storing the values as integers.

Answer 1

• use zero
• use -1
• use null

I prefer null, unless the field is not nullable, then I prefer zero, unless zero is a valid limit, then I prefer -1, unless -1 is a valid value, in which case you need another field (say, a bit field) to signify 'unlimited'.

Answer 2

Well, if you're accessing your database from .NET, the integer types have constant members MaxValue and MinValue. We sometimes use them to represent infinity. Of course, these values have no special meaning in your RDBMS...

Answer 3

As an integer, infinity will be hard. A few options:

1) -1, with corresponding program logic that treats it as infinite (and assuming that negative values aren't actually valid).

2) An extra column holding a flag indicating finite/infinite.

Answer 4

You can't really have an infinite number of accounts since there aren't an infinite number of people. In fact, you really are limited in the number of accounts that you can have (if you choose to store the value in a single database column) since any finite storage mechanism for countable things has a limit. I'd suggest, with @Tor Haugen, that the best way to do this is to use the limit imposed by the system (whatever maximum integer or long your language provides). That way you won't have to worry about any special cases for your comparisons. Additionally, I'd use a database constraint to prevent storing negative values in the field. A 64-bit integer will certainly hold a number big enough to be practically unlimited for your application.