title: Representing Number Ranges in a Relational Database (MySQL)
body: <p>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.</p>
  
  <p>I shall explain the task that prompted the question for context.</p>
  
  <p>I'm currently in the process of designing a database which will model the allocation of a pool of Identifiers to Customers. </p>
  
  <p>The pool of potential Identifiers has a range from 0 to about 2^30</p>
  
  <p>A given customer could be allocated any number of Identifiers from a single Identifier to millions in multiple contiguous blocks.</p>
  
  <p>A given Identifier may only be allocated to a single customer (i.e. it is a one to many relationship)</p>
  
  <p>Clearly there will be a Customer table and and an Identifier table containing the Customer key.</p>
  
  <p>The complexity comes with how to model the Identifiers:</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>