I have the following tables in my database:Configuration 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 configuration 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... andreserve it. Then the system adds a record to the Users table.Once the administrator 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.What's the best way to prevent the administrators 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 the article How do I 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!
Configurable tables in SQL database
database;web applications;sql
If new users always get the first available user ID, you don't really need the admin to specify the start of a new range. Just have the admin define the new range size, take the first user ID that hasn't yet been reserved as the start, and add the new range size to that to get the end.int range_start = max_reserved + 1;int range_size = user_defined;int range_end = range_start + range_size - 1;This would remove the need to identify range gaps, but if it's still desired, use the solution you linked to find the first missing ID, then query for the minimum ID greater than the missing ID. The end of your missing range would the result - 1.select min(UserId) - 1 as missing_range_endfrom Userswhere UserId > $known_missing_id;If you need your admins to be able to specify the range start, before accepting the new range I would run a validation query to make sure the new range does't intersect another:select count(*) from Available_UserIDs where UserID between $new_range_start and $new_range_end;If you get any results, reject the new range.