Generating unique tracking numbers

Rails
1

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

2

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

3

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

4

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

5

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

6

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

7

On 9/30/06, [email protected] [email protected] wrote:

from #{table_name}")
end

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