CODEWITHSUNDEEP
mysqltypes
seanbrant
seanbrant

Reputation: 1417

Representing unlimited in database with integer data type

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.

Upvotes: 26

Views: 26309

Answers (5)

barbolo
barbolo

Reputation: 3887

I don't think there's a perfect solution. So here goes my suggestion:

Create constants that represent the max values that can be stored in your database and consider them as "Infinity".

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html#integer-types

Upvotes: 0

Steven A. Lowe
Steven A. Lowe

Reputation: 61242

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

Upvotes: 40

tvanfosson
tvanfosson

Reputation: 532595

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.

Upvotes: 4

Drew Hall
Drew Hall

Reputation: 29055

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.

Upvotes: 4

Tor Haugen
Tor Haugen

Reputation: 19627

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

Upvotes: 4

Related Questions