2

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... and reserve 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.

  1. 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.

  2. 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!

  • 1
    I'm sure there's a reason but why not simply use an auto-incrementing column? – Mike Oct 17 '13 at 16:21
1

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_end
from Users
where 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.

  • hey mike. thanks for the ideas. i definitely do need to have them specify a range, both the starting and ending values. but i will try the validation query you mention here. – dot Oct 17 '13 at 17:35
1

What's the best way to prevent the admins from using a range that's already in use?

Maybe you could put constraints on the config table? The first one would be to have a unique constraint on start_range and end_range. This would prevent one range from having the same start or end as another. It doesn't prevent you from having a range that is a super-range or sub-range of another range. That might be something better checked using an ON INSERT trigger, where you might have to call a procedure or run a query to ensure that the new range is not a super/sub -range of an existing range.

  • hmm... ok. good thought... – dot Oct 17 '13 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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