Re: finding reusable ids

Actually, I already have a resource table that stores the uid of the item in
question. The checkout table does double duty as a history mechanism and a
check-out mechanism. I think you are on the right track, I should seperate
these two tasks and possibly create another table. The actual design is a
bit more complicated as we actually don't have a a checked-in flag, but a
start and finish time where users can actually store multiple overlapping
records.

Kenji

On Tue, Aug 07, 2007 at 12:23:00PM +0200, Nis Jørgensen wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly