[LRUG] Scoping sequences to a parent

[Andrew Stewart]

Hello LRUG!

I have a Rails app using MySQL.  In the app I have a parent model with children and I would like to number the children scoped to the parent – much as GitHub issues are numbered by repo.

MySQL autoincrement sequences would be ideal if they could be scoped by parent but I don't think that's possible.

For the past 8 years each parent model has held a counter.  In an after_create callback in the child model, it calls a method on the parent which increments the counter and returns it; the child updates itself to store that value.

This generally works but I've noticed I get the odd deadlock and uniqueness violation (there's a uniqueness constraint on the child for [parent_id, number]).  These errors crop up when people are importing large numbers of children into the parent's collection.

An alternative might be to change the after_create callback to query the table for the maximum number among the parent's children, add one, then update itself with that value.  Then the parent table wouldn't be involved.  But it still feels a bit hacky.

This strikes me as the sort of thing for which there must be a standard solution.  Any hints would be appreciated!

Many thanks in advance,

Andy Stewart