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?
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..
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
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()…
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).