Barcode Inventory Database

[Circuit]

I know that some of you have worked with SQL, so maybe you can help me.

I'm designing an inventory database which will use barcodes to track items.  First I'll use it to sell some junk on eBay, and after that, if it works well, I'll try to convince a local business owner to let me build a similar system for him.

My DB design uses a big pre-generated list of barcodes stored in a Barcode table.  This table has no foreign key relationships with other tables.  It exists to be a source of barcodes, and to record which of the barcodes have been used and which ones are free.  It's intentionally non-relational.

This is a choice I made after going through several relational designs and finding easy ways to destroy all of them.  Ultimately, it comes down to the fact that row IDs can change after a row is deleted (due to DB vacuuming/optimization), and therefore, the Barcode-Item pairing process must not involve row IDs.  An ID-based pairing would eventually lead to barcode mismatches, destroying a big chunk of the barcode system like a game of Jenga.  And if it can't involve row IDs, then it can't involve foreign key relationships because the only unique column in an Item is its row ID/primary key.

My solution: When a new Item is inserted, an SQL script will grab the next "unused" barcode, copy it to the new Item, and mark the barcode as "used" in the Barcode table.  For safety, Barcodes will remain "used" after an Item is deleted, even if it is deleted immediately after creation.  This will result in a growing block of "used" barcodes at the beginning of the Barcode table - one solid chunk, not scattered.

Assuming this is a good idea... what's the best way to get the next available Barcode?  I guess I could count the number of used barcodes, or I could use a counter variable... the first idea is definitely bad, and the second feels like a sin against SQL... what do you think?

[Gan]

This is an interesting problem.

If I'm understanding this right, the amount of unused barcodes is very large?

Here's a shot at a possible solution, correct me if I'm way off:
What if you made a table that tracked barcode ranges of used and free barcodes? It'd make it fairly quick to see if a barcode is used.

Barcode # | State
123 | Used
124 | Used
125 | Used
126 | Free
127 | Used
128 | Used

Could turn into
Start Barcode # | End Barcode # | State
123 | 125 | Used
126 | 126 | Free
127 | 128 | Used

I figure when adding a new item, it could look through the ranges for a free chunk(It could be a very fast lookup assuming the range chunks are large) and when it finds a free range, it can grab the first free one, shorten the free range, make the previous used range one larger and boom, done.

[Circuit]

Thanks for the suggestion, but I want it to be simpler than that.  I want it to be one contiguous range of "used" at the beginning, followed by one contiguous range of "unused"... just two solid blocks, with the "used" continually expanding as more barcodes are used.  Currently there's no reason to partition the barcodes into different ranges.

This is nice because, in theory, if the used barcodes are perfectly contiguous, then the row ID of the next free barcode is equal to the number of used barcodes plus 1.  Super simple.

But you bring up a very important point.  If, for some reason, the used barcodes are non-contiguous, then the task of finding a free barcode is more complicated.  And although this shouldn't happen in my design, it might happen if another DBA takes over and modifies the design or implementation.  So this is something I need to think about.  My solution needs to be idiot-proof... which is hard because I'm still an idiot about most of this. 

BTW, there are about 1 million barcodes - it's a random shuffling of all the 20-bit integers, each stored as a 5-character hex string.

[Circuit]

After having thought about this some more, I realize that this barcoding scheme was a bad idea.  It disobeys the rules of DB normalization, and it generally reflects my C-oriented brain trying to do SQL-oriented stuff.  I'm not ready to build a DB for a business yet.

I hope I can wrap my head around this stuff because I really want to be a DB admin someday.  The money is good, the job security is good, and in some ways it seems simpler than other types of programming.  The kind of stuff I've been focusing on for the past few years has always made me feel inadequate... I'll never know enough languages, or frameworks, or data structures, or whatever.  But DB administration?  Oddly, that seems feasible.  Maybe someday.