I'm trying to figure out the correct way to atomically increment a counter in one table and use that incremented value as an pseudo display-only ID for a record in another.

What I have is a companies table and a jobs table. I want each company to have it's own set of job_numbers. I do have an auto increment job_id, but those numbers are shared across all companies. ie: the job numbers should generally increment without gaps for each company.

ie:

• companies(company_id, next_job_number)
• jobs(company_id, job_id, job_number)

Currently I'm doing this (as a method on the partial job class):

public void SaveJob()
{
    using (var scope = new System.Transactions.TransactionScope())
    {
        if (job_id == 0)
        {
            _db.Update<company>()
                .SetExpression("next_job_number").EqualTo("next_job_number+1")
                .Where<company>(x => x.company_id == company_id)
                .Execute();

            company c = _db.companies.SingleOrDefault(x => x.company_id == company_id);

            job_number = c.next_job_number;
        }

        // Save the job
        this.Save();

        scope.Complete();
    }
}

It seems to work, but I'm not sure if there are pitfalls here? It just feels wrong, but I'm not sure how else to do it.

Any advice appreciated.