Find first free number

[Marc Vanzegbroeck]

Hi,

I was wondering that there was a faster way to do what I want to do.

I have a database with records, and each record has a field with the item-number.
Each time the client ad an item, I set the index-order to that field, do to bottom, and take for new record the itemnumber + 1.

Sometimes the customer delete a record, so a item-number is available again.

To search for the first free itemnumber, I create a for-next loop from 1 to the last number and seek vor that number. If it's not found, I know the fisst availabe number. If this is a large database on a network , it can take a while to find it.
Is there a quicker method to find the first availabe number?

Thanks,
Marc

[Marcelo Via Giglio]

Hello,

idea only, if you have really a huge data, you can think to have an extra table with deleted record (numbers), then you can extract from there the free numbers

regards

Marcelo

[Marc Vanzegbroeck]

Mercelo,

Thanks for the idea. I can put an index on it with a FOR deleted() option and than change to that index to find the first free record an then recall the record. The only thing I can't do anymore is to PACK the database. Now if I reindex the database (if the client run a maintenance option), I PACK the files.

Regards,
Marc

[xProgrammer]

Hi Marc

I don't know how often item numbers are deleted but clearly your system can cope with item numbers that are not current. That raises the question why don't you just accept "holes" in the item number allocation and allocate a primary key. Obviously I don't know any of the issues surrounding this design but the re-use of item numbers is potentially risky and the effort involved to search for the first available item number less than ideal. Other benefits flow from getting away from using record numbers as part of the basis for identifying records, not the least being that you can PACK with confidence.

If you do want to consider such an approach I am sure many of us have little functions for allocating primary keys we would happily share. It might even make for an interesting discussion on this forum.

This is just a possible suggestion that may or may not be suitable for your situation.

Regards
xProgrammer

[James Bott]

I would never reuse an item number. What if there are documents referring to the old item number, then you reissue it to a new item? This would lead to lots of confusion. Not a good practice.

Also, generating a new item number by going to the last record of an indexed file is risky. If the index becomes currupted you can end up generating duplicate IDs; I know because this has happened to me before. Duplicate IDs are a potential nightmare.

I have a system file that contains all the last used IDs for each file--each file has a separate record containing the filename and the last ID. I use the LOCATE command to find the record so that indexes are not an issue. This is all part of a database class so no code needs to be written to add a new file--it is all automatic. Appending a new record automatically generates a new sequential ID.

I also have a class that automatically reuses deleted records so you never need to PACK a database.

Regards,
James

[xProgrammer]

Hi James

I thought you would be of a similar mind.

My approach is rather like yours except I use a defined int per table rather than a name for my primary key bucket and go to that record of my key allocation table to get the next allocated key. Arguably less of a bottleneck in a really high data throughput scenario but for most purposes it probably makes no difference. Like you this is handled automatically (in my case by my data base server query object) so once written (only a few lines of code anyway) no additional coding is required.

I mostly use character 16 fields as keys, essentially 0 filled numbers - not the most space efficient but I find it convenient and easy to enable user to input a number, convert it to the key format and return the required record. The allocation code does a similar conversion:

Code: Select all  Expand view

str_AllocatedKey := PadL( AllTrim( Str( int_Key ) ), 16, "0" )
 

There is a cost in doing that conversion but it represents a saving elsewhere in code and is not done whilst a lock is held.

Regards
Doug

[Marcelo Via Giglio]

Hello,

maybe I was not clear, if you have a table (DBF) with sequencial codes (numbers) 1,2,3,4,5,6,7,8,9...100,101,,,,
if you delete 3,6,100 you can put this codes in other DBF (for deleted codes), then when you need a new code you only search the first deleted code in the table (DBF) for deleled codes.

I think this is fast

or I lost

saludos

Marcelo

[James Bott]

Marcelo,

Yes, your idea works and is fast, but reusing ID's is not a good idea. See my last message for why.

Doug,

My approach is rather like yours except I use a defined int per table rather than a name for my primary key bucket and go to that record of my key allocation table to get the next allocated key.

I don't quite understand what you are saying. What do you mean by "use a defined int per table rather than a name for my primary key?" Do you mean that you are using a number for each table rather than a name? Are you using DBFs?

I also use a character field for primary keys, left padded with zeros as you do.

Regards,
James

[xProgrammer]

Hi James

Nice to "talk" to you again.

Sorry - what I wrote seemed to make sense at the time I wrote it, but on rereading it it isn't very clear at all.

I have a key allocation table with one record per table. That record holds the name of the table (which doesn't change) and the last key allocated (as a numeric value). However rather than use a LOCATE or SEEK using an index to find the appropriate record I use a GOTO ::int_KeyAllocationBucket (in other words the record number). This record number is fixed for any table and is part of the object that reads / writes to that table. Rather than use "meaningless" numbers in the code that sets this up I use defined constants eg:

#define KEY_BUCKET_PATIENT 1

etc. Pretty much doing the same thing as you a slightly different way. Possibly a little more efficient (but wouldn't matter in most cases). Actually a cross between the two might be even better - look up the record number during initialisation and then use the number from then on?

In my Patient class I would have (in its initialisation)

::int_KeyAllocationBucket := KEY_BUCKET_PATIENT

It's nice to know that you use a very similar format for your keys as I do.

Have to rush off now

Doug

[Marcelo Via Giglio]

James,

I complety agree with you, because I use the same approach to define the IDs, but we don't know what Marc really are doing, maybe he can explain a litle more or maybe he has some constrains to do in these way

Really this forum is usefull in many ways, thanks to all to share yours experiences and knowledge

saludos

Marcelo

[James Bott]

Doug,

Thanks for the clarification. It does seem we are doing the same thing.

Marcelo,

Yes, you are correct, we don't know exactly what Marc is doing. Perhaps I should have said "usually is it not a good idea."

James

[Marc Vanzegbroeck]

Hi,

I'm afread I use the wrong word 'Item' instead of a location

It was on a demand of a customer that need the first free item-number (location).
He has for example room to store 1000 items and put every product on a location. If he sell the product, the location is free again.
So he need to know the first free space again....

I'm just thinking that I also could create a database with a record for each location, and fill a record with the product-number.
If its free again, a delete the productnumber, and sort on the productnumber+location, and at the top of the database will be the free locations.

Regards,
Marc

[James Bott]

Marc,

OK, now I understand.

As you suggested, you can index on product number then you can set scopes to show only the free records with the blank product number field. You will never need to PACK this file.

James