Barcode UID for database lookup: generate IDs without collisions

A report barcode that carries the record itself stops working the day the record grows: the symbol stretches across the page, decoders time out, and the layout no longer fits. The fix is to print a pointer instead of the data — a random UID that keys exactly one row in your database, so a scan returns the full report in a single lookup. This guide gives you the ID length your record count justifies, collision arithmetic you can check by hand, a generator that keeps all 64 bits random, and the label arithmetic that decides between Code 128 and DataMatrix. It closes with reader hardware for a bench check or a line-side station.

Pick the ID length before you generate anything

Use 64 fresh random bits as the barcode UID for database lookup, store it in a UNIQUE-indexed column, and regenerate on a duplicate key. At 100 million report records the chance that any two rows ever share a UID is about 1 in 3,700, and the UNIQUE index turns that rare event into one automatic retry instead of two reports answering to the same record. Stay at 64 bits up to a few hundred million rows in one key space. Past about a billion rows, or once several site databases will merge into one, move to 128 bits or give each site its own prefix so the random parts never compete.

What you need:

• A cryptographically strong random number generator, the same class of source a type-4 UUID uses
• A report generator or label printer that emits Code 128 or DataMatrix
• A database column with a UNIQUE index, and a scan handler that runs one indexed lookup per scan
• A reader: a fixed code reader at the station or a handheld at the bench

If the barcode keys a part or recipe record rather than a report, the identical UID pattern drives barcode part number parameter lookup: PLC memory or PC database.

The collision arithmetic, shown step by step

Random IDs collide the way birthdays do. With n records and b random bits per UID, the n(n−1)/2 possible pairs each collide with probability 1/2^b, so the expected number of colliding pairs is n(n−1)/2^(b+1) — the same counting that birthday-attack estimates apply to hash functions.

Worked example, 64-bit UIDs across 100 million reports:

• n(n−1) ≈ 10^8 × 10^8 = 10^16
• 2^65 = 3.689 × 10^19
• 10^16 / 3.689 × 10^19 = 2.7 × 10^−4
• P ≈ 1 − e^(−2.7 × 10^−4) ≈ 0.027%, about 1 in 3,700

Scaling is steep: multiply the records by 10 and the odds rise roughly 100-fold. The same 64-bit space is 1 in 370,000 at 10 million records and about 2.7% at 1 billion. A single insert looks safer still — one new UID lands on any existing row with probability 10^8 / 1.84 × 10^19 ≈ 5.4 × 10^−12, about 1 in 184 billion — but size the system by the birthday number, because it counts every pair the table will ever hold.

Why a sliced UUID gives you 60 bits, not 64

A type-4 UUID packs 122 random bits into 128: the documented UUID layout fixes four bits to mark version 4 and two more for the variant. Cut the value in half and the fixed bits stay behind. java.util.UUID.randomUUID() produces such a UUID, and getMostSignificantBits() returns the upper 64 bits, which include those four version bits, so that half carries 60 random bits; the lower half carries 62. The halves are close but not equivalent.

At 100 million records the difference is measurable: a 60-bit space gives 10^16 / 2^61 = 10^16 / 2.31 × 10^18 ≈ 4.3 × 10^−3, about 1 in 230; the 62-bit half lands near 1 in 920; a full fresh draw sits at 1 in 3,700. All three stay workable behind the UNIQUE-retry path, and the table shows why the direct draw is worth the extra line of code.

The fix is one line: draw all 64 bits straight from the strong generator. If the script already holds the UUID object, take the least significant half instead (getLeastSignificantBits() on the same class).

What 20 digits cost on the label

A 64-bit unsigned integer prints as 19 or 20 digits; the largest possible value is 18,446,744,073,709,551,615. Code 128 packs digits two per symbol character in its numeric subset, which encoders select automatically on a digit run, so a 20-digit UID needs 10 data characters. Add the start character, the mandatory check character and the stop pattern: 12 characters at 11 modules plus a 13-module stop gives 145 modules, and the required 10-module quiet zones on each side bring the symbol to 165 modules. At a 0.25 mm module that is about 41 mm of label; at 0.5 mm, about 83 mm. The 36-character UUID string costs roughly three times that length in Code 128 — DataMatrix recovers the space as a compact square, and generate a DataMatrix code as PNG bytes or pixel data in .NET shows the render step.

Can the reader resolve the module?

The other limit is optics: a module needs roughly 2, ideally 3, pixels across in the image to decode reliably. Check it from the lens equation:

Take an 8 mm lens at 100 mm working distance, a 1/1.7-inch sensor about 7.5 mm wide, and 2560 horizontal pixels. Magnification m = 8 / (100 − 8) = 0.087, so the field of view is 7.5 / 0.087 ≈ 86 mm, the pixel pitch is 86 / 2560 ≈ 34 µm, and a 0.254 mm module spans 0.254 / 0.034 ≈ 7.5 pixels — comfortable margin over the 3-pixel floor. Turned around, a 2560-pixel reader holds that floor out to a field of view of 2560 × 0.254 / 3 ≈ 217 mm, so a label this size reads across a wide range of standoffs. The 7.5 mm width is the typical value for the 1/1.7-inch optical format, and the equation assumes the thin-lens model.

Readers in this class sit in the fixed code readers category, and the camera finder filters them by resolution, shutter and mount.

Generate, store, print, scan in six steps

1. Set the width from the record count: 64 bits up to a few hundred million rows in one key space, 128 bits or per-site prefixes beyond.
2. Draw one unsigned 64-bit value from the strong generator and print it as plain decimal with no padding and no separators, so the symbol carries 19 or 20 digits and nothing else. From a type-4 UUID, take the least significant half.
3. Insert the row with the UID under the UNIQUE index. Catch the duplicate-key error, redraw, and retry twice; the expected retry count is effectively zero.
4. Print the symbol: Code 128 at a 0.25 mm module or larger with the full quiet zones, or DataMatrix when the label is tight. On a 300 dpi printer, 0.254 mm is exactly 3 dots per module — workable but with no slack — so prefer 600 dpi, which lays down 6 dots per 0.25 mm module.
5. Scan and resolve with one parameterized lookup: select the row where uid equals the scanned string. Treat a decode failure, no string, as rescan, and a no-match, string but no row, as a record that never made it into the database. The PLC side of that handshake — passing the scan to a SQL lookup and acting on the returned record — is laid out in integrating barcode scanners with PLCs via SQL database lookup. The lookup-table pattern itself is in verify scanned barcodes against a PLC lookup table.
6. Log the UID with the record write, so a duplicate can be traced back to its print run if one ever surfaces.

Verify it before the first report ships

Two tests prove the whole chain. Force the failure first: try to insert the same UID a second time and confirm the UNIQUE index rejects it while the retry path produces a different number. Then round-trip the label: print 20 UIDs, scan each at the station, and confirm 20 single-row lookups, plus one deliberately smeared symbol to watch the no-read path respond. If a scan feeds a pass/fail decision rather than a lookup, the handling pattern is the one in barcode match verification: build a pass/fail check station.

Common mistakes that cost a weekend

• Slicing the most significant UUID half and calling it 64 random bits — that half carries 60.
• Drawing from a plain pseudo-random generator, where the birthday math no longer holds because the draws are not independent uniform bits.
• Shipping without the UNIQUE index, which turns the first collision into silent data corruption.
• Sharing one key space between test and production, so a test row answers a production scan. Separate databases or per-site prefixes fix it.
• Shrinking the ID to 32 bits to fit a small label: at 100,000 records, 10^10 / 8.59 × 10^9 ≈ 1.16 expected collisions means a 69% chance of at least one. Cut the module size or switch symbology instead.
• Running the scan lookup on an unindexed column, which turns every scan into a full-table scan.

Readers for a bench check or a line-side station

The decode lives in the reader; the lookup lives in your handler, so any imager that resolves the module size works. At a station, the LS-CRD-0028 reads 5 MP frames through an 8 mm lens at up to 60 codes/s over Fast Ethernet, quick enough for a print-and-verify loop on a report stream.

Where the label is small and the standoff is fixed, the MV-ID3013PM-12M-WBN pairs a 1.3 MP global-shutter sensor with a 12 mm lens on an M12 mount and Fast Ethernet.

For desk-side verification, compact USB hardware is enough: the LS-CRD-0047 reads at 60-120 mm over USB 2.0, and the LS-HCR-0001 pairs a 1.0 MP sensor with USB 2.0 for handheld passes.

More handheld models sit in the handheld code readers category. Pick the reader by module size and standoff with the optics numbers above, then size the rest of the station in the configurator below — it starts at the camera step — or reach us through the contact page if the lookup runs inside a PLC. The decode-side fixes that keep reads clean live on the code-reading topic page.