Autoincrement Primary Key For Constants

database-designprimary-key

We are having an issue that is probably due to some poor design choice or lack of understanding of how to use primary keys.

We have a simple constants table

CREATE TABLE IF NOT EXISTS `constants` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `category` varchar(20) NOT NULL,
  `subcategory` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
)

We reference the unique rows using the id column.

E.g. if(id == 30) do something

Now, our issue arises when we sync between dev and production. On dev, we are constantly trying our new things/testing features. We are adding and deleting constants. So our ID column doesn't map into our production database. So, if we tried the same code again on production:

e.g. if(id == 30) do something

It points to a different record.

My initial thought is that we just make the category/subcategory columns the primary key but I have read that you should almost always use a GUID for a primary key.

Best Answer

You comment that:

There has to be a way to use a auto-increment field as a key and have it sync between dev and production. The problem is when i use auto-increment id field when i am constantly adding and deleting constants the ids wont match on production. So when I port the code and db edits to production. the ID column is out of sync.

Why does there have to be a way to use an auto-increment field? You can still use an integer key, but you should drop the AUTO_INCREMENT from your table and not rely on it to make unique keys for you.

What your primary problem appears to be is not the structure of your tables, as such, but rather your development process. Specifically, you need to use migrations to populate your constants table.

You mentioned porting the db edits to production, which is likely at least a poor man's migration. This is where your creation of constants should go. Simply explicitly specify the ID value you wish to use for a given constant, rather than inserting directly into the table and then reading the value from there.

INSERT INTO tbl_constants
(constant_id,human_readable_string)
VALUES
(30, 'something cool')

This, of course, creates an issue with possible duplicate keys, from different developers. The uniqueness of the primary key constraint will prevent you from deploying in such cases (assuming you test everyone's work together before you deploy, which you should), so the primary issue is how to resolve such conflicts.

First off, if using a proper migration setup, you should have down migrations that undo changes you have made, for every change you make. Simply run the down migrations if you find a conflict during testing, change the value of one of the constants, and re-apply the migrations again.

(Sometimes a down migration is not feasible, and so you would have to start again with a fresh copy of what you started with. Make sure you have such a fresh copy available at all times. I personally keep a copy of the database with no set values, except for things added via migrations/db edits, like constants, which allows me to rebuild the database from scratch if I really mess things up, which is common during testing.)

You still need to update your application code, however. To speed that up, I suggest a technique I've found very useful: duplicate your constants and their values as actual constants in your code files.

define CONST_COMETHING_COOL = 30;

That way, all your code has things like:

if (ID==CONST_SOMETHING_COOL) do something

rather than a magic number like 30 scattered everywhere. The constant CONST_SOMETHING_COOL can be updated in a single place (the file where it is defined) in the event that you have a duplicate key found during testing. It is also distinct and makes it very easy to search your code base for, which makes it easier to refactor things in the future, potentially removing that constant and/or seeing what it affects in the event of debugging, etc.

Alternately you could load the constants from the database to avoid any mistakes in the copying of them to your code. At the very least you should add a test that loads them and asserts that the code's value matches the database's value.

With all the above, you resolve your problem of having constants out of sync between different environments. Everything that is referenced in your code needs to be handled in your migrations in this way; only user entered information (via forms, etc) should use tables with AUTO_INCREMENT keys.

Related Question