Gaps in a DB

[meerkat]

I'm inserting orders into a DB with a order number (orderNum).
It not a big DB, with only about 12 million records.
If we add about 2000 to 3000 orders daily. The order number gets really big after a while.
To get around this we can reuse orders that have been deleted, and old numbers that have been purged because of age.

Here is a simple Insert command that reuses gaps or if no gaps adds to the end:

'-- Insert a new order with the next available orderNum or a unused orderNum
INSERT INTO orders (orderNum, customerNum, product, price)
VALUES (
   (SELECT COALESCE(MIN(orderNum), 1) FROM orders WHERE orderNum NOT IN (SELECT orderNum FROM orders))
);


1. When there are gaps:
- MIN(orderNum) finds the smallest available orderNum that is not in use (i.e., identifies the gaps).
- COALESCE(..., 1) returns the smallest available orderNum.
If there are gaps, it returns the smallest available one.
2. When there are no gaps:
- MIN(orderNum) returns NULL because there are no gaps.
- COALESCE(..., 1) defaults to 1, ensuring that if there are no gaps, it starts with the next available orderNum by taking the maximum existing orderNum and incrementing it by 1.

* In summary, this approach is designed to handle both scenarios seamlessly, providing a valid and available orderNum for a new order whether there are gaps or not.

[tsh73]

The order number gets really big after a while.
To get around this we can reuse orders that have been deleted, and old numbers that have been purged because of age.

Really, I think to have "continuous" document numbers is better then save some numbering space.
Better for humans, that is if numbers are supposed to be read by humans.
If it is machine-id, then one probably should use some auto increment field and forget about it.

[meerkat]

I've been through this a few times. The last system I woked on had a average about 2500 orders a day. So in one year 2500 x about 250 working days thats a number 625,000. And after about 5 years it would be about 3,125,000.
One of the big problems is that some invoices have limites space to print the number.
And no one uses the numbers anyway. They are just usable for lookup. All documents are client/server and all screens have multiple lookups and relations.

The above routine does auto increment if there are no gaps.
I've put up some fairly large systems, and never found much use for auto-increment. Some prople prefer rowid instead.

What happens is after a while you tend to reuse all numbers, and very seldome add new numbers.

[Rod]

I see benefits in both arguments. For transactions I favour tsh73 auto increment, indeed yyyymmddmmssnnn That is transactions within a second. All in order. Not necessarily sequential. But side by side.

However for large blocks of data, simplistically in RAF format, reusing space is an excellent technique.

Bottom line is you get continuous data instead of broken space.

[meerkat]

Here is a recent real life example.
I've been asked to develop a food bank system. Granted these are typically small systems.
They have only about 10,000 clients. Clients come in 1 to 4 times a month for assistance. So probably 2 a month on average. This usually involves giving them food, household items, bus tickets, phone credits, and money. So clients probably average a couple events a month with about 4 items. Or monthly 10,000 clients x 2 events = 20,000 events, and 20,000 events x 4 items = 80,000 items.
Over the 25 years thay have been around the numbers for clients, events, and items simply became a mess and almost not usable.

I immediately wrote a system to reorganize and eliminate blanks in the 3 tables. Now they are usable. To prevent this from happening again the system now reuses gaps in the tables. Personally I hate using numbers for input, so I'm implementing systems where they rely less on numbers.
 I've recently been asked to include other food banks.
I'd sure like to use LB for this, but client/server is not available.