I have the following tables in my database:
Config Table:
======================================
Start_Range | End Range | Config_id
10 | 15 | 1
======================================
Available_UserIDs
==========================
ID | UserID | Used_YN |
1 | 10 | t |
1 | 11 | f |
1 | 12 | f |
1 | 13 | f |
1 | 14 | f |
1 | 15 | f |
==========================
Users
==========================
UserId | FName | LName |
10 |John | Doe |
==========================
This is used in a reservation system of sorts... which lets an administrator specify a range of numbers that will be assigned to users in the config table. Once the range has been defined, the system then populates the Available_userIDs table with all the numbers in between the range, and sets the Used_YN flag to false
As users sign up, they grab the next user_id number that's not in use... and
reserve it. Then the system adds a record to the Users table.
Once the admin has specified a range, it is possible that they can change it.
For example, they can start with 10-15... and then when the range is used up, they should be able to specify another range like 16 - 99.
I've put a unique constraint on the Available_UserIDs table, as well as on the Users table - to ensure that UserIds can't be duplicated.
My questions are as follows:
What's the best way to prevent the admins from using a range that's already in use? I thought of the following options:
-- check either the Users table to see if the start range or ending range numbers are being used. If they are, assume that all the numbers in between are in use too, and reject the range.
-- let them specify whatever they want, try to populate the Available_UserIDs table. If there are duplicates, just ignore that specific error message from the database and continue on.
How do I find gaps in the number ranges? For example, if they specify 10-15, and then 20-25, it'd be nice to be able to somehow suggest on my web page that 16-19 is currently available.
I found this article: http://stackoverflow.com/questions/1312101/how-to-find-a-gap-in-running-counter-with-sql
But it only seems to return the first available number... so in my example above, it would only return the number 16.
I'm sure there's a simpler way to do things that I'm overlooking!
