I am attempting to understand if you will find any standard best practice processes for modelling number ranges inside a Relational Database (Within this situation MySQL) and when this is actually a smart factor to complete.
I shall explain the job that motivated the question for context.
I am presently along the way of creating a database that will model the allocation of the pool of Identifiers to Clients.
The pool of potential Identifiers includes a vary from to around 2^30
Confirmed customer might be allotted a variety of Identifiers from one Identifier to millions in multiple contiguous blocks.
Confirmed Identifier may be allotted to some single customer (i.e. it's a someone to many relationship)
Clearly you will see a person table and as well as an Identifier table that contains the client key.
The complexness includes how you can model the Identifiers:
Option one is always to possess a row represent single identifier. This can lead to a potentially large numbers of rows within the table, but will make trying to find who is the owner of which identifier and when confirmed identifier is being used trivial.
The 2nd (and i believe more promising) option is to possess a row represent a variety of values having a minimum and maximum value. This could make queries a little more complicated (I am presuming the query for checking if the identifier was being used is always to query for ranges with "Minimum less than X" along with a "Maximum greater than X") but would lead to far less rows and would probably be simpler to handle increase.
I'd welcome any sights on if this is an excellent approach and when not if there's an apparent better approach that i'm missing.
When the ranges don't intersect, you might store them as pairs of
CREATE TABLE customer_range ( customerId INT, rgStart INT, rgEnd INT, PRIMARY KEY (customerId, rgStart), UNIQUE KEY (rgStart) )
To question the client several goes to, make use of this:
SELECT customerId FROM customer_range WHERE rgStart <= $mynum AND rgEnd >= $mynum ORDER BY rgStart DESC LIMIT 1
Normally, I would not attempt to reduce the amount of rows just with regard to it - in principle, a properly-indexed table having a billion rows ought to be just like quick like a table with 100 rows, as lengthy as the queries hit the index.
I'd work more around the actual queries you'll probably wish to run, and design the answer on that basis. For example, would you need to list all of the IDs owed to some single customer? Would you need to check which customer is the owner of several IDs? Would you need to find the number of IDs a person is the owner of?
The second is a touch tricky for those who have "range" tables - rather than doing
"select count(*) from ranges where customer = 1", you'd need to calculate the amount Insolvency practitioners in every range for that customer, and add them up. Not brain surgery, but may be reduced within the real life...
If one makes a table like so
id_start not null unsigned integer /*not autoincrement!*/ id_end not null unsigned integer customer_id unsigned integer not null foreign key FK_customer (customer_id) REFERENCES customer.id primary key (id_start, id_end) key id_end (id_end)
You can now simply look into the free key by doing
SELECT count(*) as occupied FROM ids WHERE 100 between id_start and id_end;
To check on a totally free range do
SELECT count(*) as occupied FROM ids WHERE NOT ('$low' > id_end) AND NOT ('$high' < id_start)