3

I'm developing a (PHP MySQL) Web App which will sell dynamically generated real time cell phone numbers for cellular companies. A company will ask for, say, a thousand numbers and this app will check for available numbers and provide them.

The number will be an 8 digit figure. The first two digits of the number will mostly be fixed as specific codes for a particular company, rest of the digits will be dynamically generated.

My question is whether

a) I should store all the sold numbers in database, if yes then:
   i)  Separate number in separate row or
   ii) All the numbers in a single row

b) Should store range of numbers i.e 32500001-32510000

Keeping performance and ease of handling the algorithm in mind, kindly suggest me your solution.

Improve this question
7
  • Does anyone else think that apart from the persistence aspect of this problem that something like an interval tree might be appropriate given an upper bound on number count and appropriate RAM? If so, could splitting the problem into 1) persistence via an entry log to rebuild the tree and 2) the tree itself - perhaps provide a viable solution? This might be too complex/too focused on performance for what the OP needs, though, even if it did work out.
    – J Trana
    Sep 2, 2014 at 4:49
  • too complex is the catch phrase here :D
    – Syntax Error
    Sep 2, 2014 at 8:34
  • How does any of these designs prevent race conditions?
    – Pieter B
    Sep 2, 2014 at 8:54
  • 1
    A possible race condition is when 2 (or more) processes work on the same data in parallel. Imagine, 2 processes at the same time querying which numbers are free, giving a range and then storing it in a database. Where it can go wrong is this: 1 asks free range(gets a range), 2 ask free range (gets same range as 1 because 1 hasn't written the range yet). 1 saves range. 2 saves range (and in the process overwrites the range of 1 because you only checked at the start which numbers were free)
    – Pieter B
    Sep 2, 2014 at 10:20
  • 1
    @OMI This point about race conditions is an important one. It would probably be good to study race conditions in general for a while to understand the pitfalls that can occur, because race conditions are one of the classic (and sometimes difficult) problems to overcome in computer science.
    – J Trana
    Sep 3, 2014 at 1:59

2 Answers 2

Reset to default
3

a) ii) is pure idiotic, evil stupidity. Don't.

a) is better if you want to code the simplest possible thing (which is usually a very big 'want').

b) is better if you're pressed for database space or absolutely have to minimize DB access time, and don't care about the additional logic your code needs to retrieve the numbers. (Cell phone numbers aren't that big a name space, so it's unlikely that you need to optimize that much, but that can't be decided from a distance.)

Improve this answer
2
  • There may be a case when the system will allot numbers like 32500001-32500155 then a gap of 5 numbers and again starts from 32500160-32510000. Also there may have been more than 1 gaps. In that situation I think a) i) will be suitable. What do you think?
    – Syntax Error
    Sep 2, 2014 at 8:39
  • The numbers will be allotted in bulk. So if a company purchases, say 100,000 numbers then there will be hundred thousand rows just against one purchase. Which is horrible i think, isn't it?
    – Syntax Error
    Sep 2, 2014 at 11:33
1

Depends on a few more details you don't give, eg expected size of data. But in general I would most likely go for a)i)

a)ii) would only be an option if you would talk about some database system that has special features to store and query this like some NoSQL databases (and maybe Postgres to some degree), having the option to actually query this data and not requiring to define thousands of columns which would be foolish.

a)i) has the slight advantage that you store the data as it is actually used and do not need to duplicate information that belongs to a "batch" (like owner) many times. If you store single numbers than there should at least be a "batch-header" table and the single number rows are in relation to this.

Query complexity should be mostly similar. It's really not that much a difference in the resulting SQL. (I would expect queries mostly to be of the kind if a certain range is still free or who owns a specific number.)

Improve this answer
6
  • If you found yourself trying to put all the numbers in a range in one column in Postgres, one should instead consider the range type that is native to the database.
    – user40980
    Sep 1, 2014 at 17:15
  • @thorsten - Depends on a few more details you don't give, eg expected size of data - Sir I have written that the numbers will consist of 8 digits
    – Syntax Error
    Sep 2, 2014 at 8:42
  • @OMI size of data: how many numbers do you expect to sell, how many rows will this table contain?
    – Pieter B
    Sep 2, 2014 at 9:06
  • @pieterB - Sir the rows will expand to hundred of thousands. Because we have 8 digits in the number and first two are fixed. For Example: xx xx xx xx the first two will be fixed so lets just say the number is AB XX XX XX
    – Syntax Error
    Sep 2, 2014 at 10:10
  • @OMI a modern DBMS can handle millions of rows with ease. For the sake of (over?)normalization you can create a prefix and a sufix table, any number ill be the join of both (nice opportunit to use a view)
    – jean
    Sep 2, 2014 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.