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@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
