Re: finding reusable ids

Kenji Morishige skrev:
> I have a table that creates "check-out" records that stores information when
> a particular resource is being utilized. I want to maintain a friendly
> shortened ID so people can reference these check outs.
>
> At any given time, there should not be more than 999999 or so check-outs, so
> as the check-outs get checked in, the old IDs would become available. What
> is the best method to query for these resusable IDs that would not be
> assigned to checked out items? It seems that it would be quite inefficient
> to look at the entire table to see which ids exist, then increment
> accordingly. For some reason, I feel that there would be something already
> available to solve this.
>
> example set:
>
> uid co-id checked-in?
> 1 1 n
> 2 2 n
> 3 3 y
> 4 4 n
> 5 3 n
>
> obviously, this is a small sample set, but the id 3 can be reused, so I'd
> like to reuse it without using a external tracking mechansm. My table has
> 1,000,000+ records.

Do you need the co-id once the item is checked in? If not, I would split
this into two tables:

resources
uid <more data>
1
2
3
4
5

checked_out
uid co_id
1 1
2 2
4 4
5 3

Where the existence of the row in the second table doubles as the
checked-in flag.

This doesn't solve your problem, but might simplify the query to find a
new id - something like this (untested):

SELECT min(q.co_id) +1
FROM (
SELECT (co_id + 1) as co_id FROM checked_out
EXCEPT
SELECT co_id FROM checked_out
) q;

(you need a special case when the table is empty)

The same method can of course be used with your original table layout.

Nis