generating unique tracking numbers

Archer1 · March 13, 2020, 10:19pm

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

snacktime · March 13, 2020, 11:59pm

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

Benjamin_Gorlick · March 13, 2020, 11:59pm

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

corporate_hippy · March 13, 2020, 11:59pm

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

Archer1 · March 13, 2020, 11:59pm

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

T_wenrich · March 13, 2020, 11:59pm

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

ruby5 · March 13, 2020, 11:59pm

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).