generating unique tracking numbers

Hello,

I'm interested in learning what folks in the Rails community do when they need to generate permanent unique numbers for tracking objects such as packing slips, inventory items, customers, employees, etc.

It's tempting to use the autogenerated id, but they're not pretty enough for human consumption and it can be problematic having assigned ids if tables ever need to be reogranized.

I think it would be preferrable to use a sequence, but haven't found a way of doing this with MySQL, so I just increment the max value of the id column. Here's how I generate a customer number:

application.rb:   CUSTOMER_BASE = "10000"

  def next_id(table_name, column)     ActiveRecord::Base.connection.select_value("select max(#{column})+1 from #{table_name}")   end

customer_controller.rb: def create      @customer.number = CUSTOMER_BASE.to_i + next_id('customers', 'id').to_i end

Have I simply postponed the problem to the day when ids have been re-organized beyond the customer_base and I start generating collisions?

So what do you do?? I'm all ears... Dave

If the id is just an integer use a sequence. If you want something that is alphanumeric you can create a table for the id and do something as follows. If your id is A1, the next iteration would be A2, etc..

id = Myid.find(:first) id.id.succ! id.save

If you are getting back a fixnum though youll want to do id = id.succ

Maybe the Uses Guid Plugin can help.

"This plugin for ActiveRecord makes the "ID" field into a 22 character URL-safe GUID."

http://wiki.rubyonrails.org/rails/pages/Uses+Guid+Plugin

Thanks! I've used your suggestions and am using a sequence with the successor method. When I have a little more time, I'll create a table that manages all my 'numbers' I'm assuming number is a string, so I just trigger the method.

  def next_number(table_name)     # assumes number column exists and is string     ActiveRecord::Base.connection.select_value("select max(number) from #{table_name}").succ   end

Dave

In my opinion it’s very bad practice to to generate IDs using max(), so the sequence isn’t “preferrable”, it’s an requirement.

This approach using max() introduces nasty, hard-to-track bugs when two users/processes try to create records at almost the same time and both get the same max() value.

However, you could lock the table (for READ operations!) before you obtain the max()…

Regards,

Thomas

I second T wenrich. This is not in a Rails solution, but I use a single column, single row table. I lock the table, get the value and update it. This has generated millions of unique values over the years and the only time it failed is when a DBA removed the lock key word (they thought the database would manage it automatically).