Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Representing unlimited in database with integer data type

Tags:

types

mysql

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.

like image 661
seanbrant Avatar asked Nov 06 '08 02:11

seanbrant


3 Answers

  • 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'.

like image 81
Steven A. Lowe Avatar answered Oct 14 '22 05:10

Steven A. Lowe


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.

like image 5
tvanfosson Avatar answered Oct 14 '22 05:10

tvanfosson


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

like image 4
Tor Haugen Avatar answered Oct 14 '22 05:10

Tor Haugen