Autoincrement Primary Key For Constants

Question

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.

Answer 1

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.

Answer 2

Primary keys is a key concept of the relational model. From a pure practical way, in general you have 2 options:

• Choose as a primary key one of the candidate keys: that is, a column or combination of columns that unequivocally identifies the row. For example, in Twitter, we could think that the user name or the user email in a users tables is a candidate key, so both could be used for the primary key
• Create an arbitrary identifier that is created on insertion time (MySQL's AUTO_INCREMENT is a paradigmatic example, which we could generate on the same users table)

There are some reasons to choose one over the other (some good and some bad): For example, setting meaningful columns as primary key makes later harder to change those values, as many other tables or logic could depend on it. That could be a reason for or against it. For example, most sites like Twitter actually use an internal id so that you can easily change your username and email. Some ORMs require, or make the logic easier if you all your tables have an auto_increment int or bigint called id. Numeric ids are shorter, which in some specific cases may make the foreign keys or other parts shorter, and maybe more efficient (that is the case for MySQL's InnoDB in many cases due to its internal structure). On the other side, having and arbitrary id as a primary key may not be adequate for your logic, as there is not direct connection between it and the actual row.

Having said that, in my opinion -and that is without knowing your internals- you should give a human-recognizable identifier to your constants and query though that column:

if (name == "kill_all_humans"))
  do something

Whether name should be a primary key or a secondary key (and having a SERIAL in addition) is probably going to be of lesser importance, assuming your table has just a few values (not millions). That way, you can set and read the value independently of it having an arbitrary identifier. That way your code is 100% portable.

As side notes:

• If identifiers are important to you, make sure you import them correctly, those may be important in the event of a later update
• I do not think int(5) means what you think it means

Answer 3

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.

A few things to note here:

1. I don't think you meant GUID as
   • They are definitely not good for Primary Keys
   • They are generally not auto-incrementing
2. You might have heard that it is good practice to always have an INT (or differently sized integer type, as in TINYINT, SMALLINT, BIGINT, etc). The way I look at it is:
   • IF you have a natural key (a unique value that exists either out in the world or already in your model, such as a Composite Key made up of other keys in the table) then use it (note: SSNs are not valid Natural Keys).
   • ELSE, use a surrogate key, whether auto-incrementing or manually-set.
3. When using Surrogate Keys:
   • Data that is added through the natural operation of the system (e.g. added by end-users and/or back-end processes), should be auto-incrementing.
   • Data that is part of the application itself, and most often has a representation in the app code, usually as enums, should be manually entered/controlled and not auto-incrementing. These values get assigned via rollout scripts and have no need to be auto-incrementing.

4. It is generally best to not use a single table to hold app constants that are spread across different "categories". It is a very understandable and common design pattern, one that I used to advocate myself years ago, but in the end it is mostly not good. Each "category" of values should have its own lookup table. This allows for proper relationships via Foreign Keys to be established to the appropriate entities. The catch-all constants table has no real meaning if Foreign Keyed as not all values are valid in all entities it is related to. And in order to overcome the issue of allowing for invalid values, you then need to have a composite key of CategoryID, SubCategoryID and place both of those in all related entities, and that can get messy. Save yourself the headache down the road and have 1 lookup table per each enum / "category".

   And please do not discount the idea of needing to FK the constant ID values to their respective entities. I know some people think that FKs are optional but they not only help reduce bad / invalid / orphaned data (that can cause errors in the app that will suck the life out of you in debugging and fixing until for as long as you work on this system) but in some, or maybe many?, databases they are used at times by the database engine to achieve better performance.