Representing infinity or "no limit" in an integer field?

[Victor Hooi]

Hi,

I have a Django IntegerField that I'm using to store the purchase limit for a product.

purchase_limit = models.IntegerField()

I also need to represent no limit (i.e. infinity) as well in that field.

I was thinking of just using NULL to represent no limit.

purchase_limit = models.IntegerField(blank=True, null=True)

Zero would have a meaning for this field (you can't buy any), however negative numbers don't have any meaning.

Hence, another option is just to use say, -1 as the value to represent no limit.

Any thoughts on either option, or which one is more "correct"?

Cheers,

Victor

[Mike Dewhirst]

On 19/08/2013 5:39pm, Victor Hooi wrote:
> Hi,
>
> I have a Django IntegerField that I'm using to store the purchase limit
> for a product.
>
> purchase_limit = models.IntegerField()
>
>
> I also need to represent no limit (i.e. infinity) as well in that field.
>
> I was thinking of just using NULL to represent no limit.
>
> purchase_limit = models.IntegerField(blank=True, null=True)

If there is a purchase limit put it in. Otherwise there is no purchase
limit. For me, None/Null would be comfortable. But I might be tempted to
add functions in the model's clean() method which encapsulate the the
business rules for the app - whatever they are.

>
>
> Zero would have a meaning for this field (you can't buy any), however
> negative numbers don't have any meaning.
>
> Hence, another option is just to use say, -1 as the value to represent
> no limit.
>
> Any thoughts on either option, or which one is more "correct"?
>
> Cheers,
> Victor
>

> --
> You received this message because you are subscribed to the Google
> Groups "Django users" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to django-users...@googlegroups.com.
> To post to this group, send email to django...@googlegroups.com.
> Visit this group at http://groups.google.com/group/django-users.
> For more options, visit https://groups.google.com/groups/opt_out.

[Jani Tiainen]

From mathematical point integers forms an infinite (countable) set. Though in computer science interger is usually a finite set. So what you need is just define a logic. Note that this also makes impossible to enforce "unlimited" amount to buy so there will definitely be some maximum amount you can really buy.

NULL is usually interpreted as "no value defined" which would suit well in that sense. Problem is that usually databases aren't very fast to search NULL values so if you have to for example produce often list of products that you can buy "infinite amount", you would like to consider using value(s) that don't conflict from valid set of values.

I personally would pick max value while leaving NULL to mean "no value defined".

--

Jani Tiainen

[Jason Arnst-Goodrich]

Problem is that usually databases aren't very fast to search NULL values so if you have to for example produce often list of products that you can buy "infinite amount", you would like to consider using value(s) that don't conflict from valid set of values.

I'm pretty sure that's not true.

+1 to store as Null. 

[Jani Tiainen]

On Mon, 19 Aug 2013 11:14:54 -0700 (PDT)
Jason Arnst-Goodrich <good...@gmail.com> wrote:

>
> >
> > Problem is that usually databases aren't very fast to search NULL values
> > so if you have to for example produce often list of products that you can
> > buy "infinite amount", you would like to consider using value(s) that don't
> > conflict from valid set of values.
>
>
> I'm pretty sure that's not true.

My knowledge is limited per implementation but as far as I know, standard implementation is not to include NULL's (since NULL means "does not exist").

I think pretty latest PostgreSQL can use indexes to filter NULL values as well (doesn't have facility to store them though). Oracle since 11g has option to include null values in the index.

MySQL uses table statistics to determine are NULL values indexed or not.

For the rest I don't have knowledge about.

That doesn't remove the fact that there will be a limit for ordering one time - though I would be happy to know what "unlimited" amount of ordering would mean and how to actually it would be implemented and what's the real world use case...

[Simon Riggs]

On 20 August 2013 11:46, Jani Tiainen <red...@gmail.com> wrote:

> I think pretty latest PostgreSQL can use indexes to filter NULL values as well (doesn't have facility to store them though).

Postgres can use an index to find NULL values, so SELECT * FROM foo
WHERE col IS NULL can be indexed.

Not sure what you mean by "doesn't have facility to store them". NULL
values are stored in the database, they just don't occupy the same
space as non-NULL values. PostgreSQL's support of NULL values is full
and complete, in line with SQL Standard requirements.

> MySQL uses table statistics to determine are NULL values indexed or not.

Postgres stats include number of values NULL.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

[Jason Arnst-Goodrich]

To be honest, I've never dealt with huge data sets but in the cases I have dealt with it's never been a problem.

This might be relevant: http://dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html

Unless someone shows me pretty compelling stats otherwise, I'll always lean to implementing in a way that makes sense logically and worry about performance optimizations when they come up. Storing Nulls just make sense to achieve the goal so that's what I'd do until there's a reason for me not to.