Handling Heroku/ClearDB auto Increment primary key strategy

Question

With Heroku running ClearDB as the MySQL layer, primary keys are auto-incremented in multiples of 10. So, for example, the first insert could be 4 then 14, 24, 34 etc. I fully accept their reasoning for this, so that's not the issue.

My question is, how do you handle this within your code. For example, let's say I have a status table which consists of 4 rows,

     id | name
     1  | Active
     2  | Retired
     3  | Banned
     4  | Awaiting Mod

And then within my application I use:

   if($status['id'] == 1){
     //do something
   }else{
     // do something else
   }

Clearly this will break, due to the way the PK's are incremented. What is the best practice for handling situations like these? I cannot, for example, check for a 14 as there's nothing to say the numbering strategy won't change to 12, 22, 32, etc.

Should I be checking by name eg, if($status['name'] == 'Active') or do I add a new column to the table with the ints I require? I know querying by int in SQL is far quicker than by string.

So, what is the normal way to handle this?

score 3 · Accepted Answer · answered May 20 '17 at 20:23

There are basically two strategies to handle that

No auto-increment

Don't use the auto-increment. Simply add the id values yourself, when inserting data. For a table like 'status', that probably contains only static data, you don't dynamically change, that might be good option.

String constants

Check for the string values. And define those strings as class constants.

class YourClass {
  const ACTIVE = 'Active';
  const RETIRED = 'Retired';
  ...
}

And then write your checks as

if($status['name'] == self::ACTIVE){
  //do something
}

I'd recommend to use the second approach, mostly because it makes your code more semantic. Its much easier to see what $status['name'] == self::RETIRED means than $status['id'] == 2

And if you add an index on the name column on that table there won't be (almost) any difference in performance when you query by name instead of by primary key.

Great answer. I think I'll actually end up implementing both strategys. A non incrementing table, with a class which maps to a semantic code reference. Thank you. — Doug, May 21 '17 at 21:45

Handling Heroku/ClearDB auto Increment primary key strategy

1 Answers1

No auto-increment

String constants