I'm trying to understand if there are any standard best practice approaches for modelling number ranges in a Relational Database (In this case MySQL) and if this is in fact a sensible thing to do.
I shall explain the task that prompted the question for context.
I'm currently in the process of designing a database which will model the allocation of a pool of Identifiers to Customers.
The pool of potential Identifiers has a range from 0 to about 2^30
A given customer could be allocated any number of Identifiers from a single Identifier to millions in multiple contiguous blocks.
A given Identifier may only be allocated to a single customer (i.e. it is a one to many relationship)
Clearly there will be a Customer table and and an Identifier table containing the Customer key.
The complexity comes with how to model the Identifiers:
Option one would be to have a row represent single identifier. This will result in a potentially huge number of rows in the table, but would make searching for who owns which identifier and if a given identifier is in use trivial.
The second (and I think more promising) option would be to have a row represent a range of values with a minimum and maximum value. This would make queries a bit more complex (I'm assuming the query for checking if an identifier was in use would be to query for ranges with "Minimum lower than X" and a "Maximum higher than X") but would result in far fewer rows and would likely be easier to manage and update.
I would welcome any views on if this is a good approach and if not if there is an obvious better approach that I am missing.
If the ranges do not intersect, then you may store them as pairs of
CREATE TABLE customer_range ( customerId INT, rgStart INT, rgEnd INT, PRIMARY KEY (customerId, rgStart), UNIQUE KEY (rgStart) )
To query the customer a number belongs to, use this:
SELECT customerId FROM customer_range WHERE rgStart <= $mynum AND rgEnd >= $mynum ORDER BY rgStart DESC LIMIT 1