0

I have a model object in my Rails application, let's call it Picture.

The pictures table in database has a primary key column called id because Rails create them automatically according to convention, this column is just 1, 2, 3, 4.....

But I need another column called custom_id to record its custom ID, I want its value to be

  • PIC2015-0001, PIC2015-0002, PIC2015-0003 for year 2015
  • PIC2018-0001, PIC2018-0002, PIC2018-0003 for year 2018
  • and so on.

Basically PICYYYY-NUM_FOR_YEAR, where YYYY is the year it is created, and NUM_FOR_YEAR is the number this record is created (first record created in a year will have 0001, second will have 0002, and so on... This number will restart for new record each new year)

I want to implement this by using the after_create hook and look up its created_at time stamp to get the year YYYY, and infer the NUM_FOR_YEAR by looking at last Picture with non-empty custom_id. But I'm worried about synchronisation issue. What if the below happen (or will it happen at all?):

The below events happen in the time order listed

  • Picture 1 created, after_create called and finished
  • Picture 2 created
  • Picture 3 created
  • Picture 2 after_create called - fetch Picture 1's custom_id
  • Picture 3 after_create called - fetch Picture 1's custom_id
  • Picture 2 after_created finished
  • Picture 3 after_created finished - this will have duplicate ID, which is wrong

What is the simplest way to solve this problem?

Henry Yang
  • 2,283
  • 3
  • 21
  • 38
  • Why do you need to "fetch Picture 1's custom_id"?... Maybe you can use the callback `after_create_commit`, in there get the ID generated from your DB, and from there update the `custom_id` [Read here](https://guides.rubyonrails.org/active_record_callbacks.html#transaction-callbacks) – gasc Jul 31 '18 at 01:53
  • Wouldn't using `after_create_commit` cause the same synchronisation problem? (Or it won't?) Also you said use the ID generated from your DB, but I want to restart from 0001 every year. Can you clarify your implementation so I can understand why it might address the problem I have? – Henry Yang Jul 31 '18 at 02:27
  • 1
    I'm sorry, you're right, I forgot about that (restarting the count every year). Maybe, use a unique index at the DB level, at least this way you guarantee not to repeat a custom_id, the DB will raise an error if you try to duplicate the value. As far as preventing the sync problem, at the application level, maybe with a `unique` validation in your model (then again this actually don't prevent the sync problem, but at least you are safe with the DB constraint) – gasc Jul 31 '18 at 02:39
  • Thank you, the DB level unique constraint is really helpful – Henry Yang Jul 31 '18 at 03:12
  • Do you know how I can add DB level unique constraint to a column in a table in Rails? I googled but those answer seems to involve index, and I don't want to touch index because I'm not sure if it will have bad side effect. – Henry Yang Jul 31 '18 at 03:33
  • What if a record gets destroyed? How do you want to manage this case? Soft delete? – iGian Jul 31 '18 at 03:55
  • I assume records won't be deleted – Henry Yang Jul 31 '18 at 04:40
  • 1
    In this case and assuming also no concurrent create, just pick the year from `created_at` and count records where created_at this_year, build the string and save to `custom_id`. How fast is the data saving? You reserved 4 digits for a whole year, so it is 10000 records = 1,15 per hour. I wouldn't worry of concurrency, but better set unique constraint as validation. – iGian Jul 31 '18 at 06:42
  • If your column will be an id, why don't use index? I don't see a reason why not to do it. [Check this SO answer](https://stackoverflow.com/a/2955470/8267417) – gasc Jul 31 '18 at 15:29
  • The reason is detailed in this question https://stackoverflow.com/q/51605107/6359753 – Henry Yang Jul 31 '18 at 23:59

0 Answers0