Should running number be separated column from primary key in database

I currently work in this field. This is a case where a surrogate key should be used.

Our software probably deals with a much wider range of scenarios than yours, but that extra flexibility may mean a lot for you in the future.

• Student numbers (overall) may change quite frequently depending on how the numbers are assigned by the distribution authority.

  When a new student is enrolled, a number or unique identifier may have to be internally assigned to the student before the authority has actually generated a number for the student (asynchronously). In this case, it may be necessary to use more than one field in the database... which crushes the primary key idea immediately.

  Student numbers that change are just bad news for lots of reasons. Remember that the primary key in this table will be propagated to many parts of the system, and with many associated rows.

• Performance is rarely a concern for single-student data operations. Depending on how the indexes are set up, using the student number as an alternate key may have a slight performance hit. However, at least in my experience, it's pretty rare that this is a significant user experience issue. You're more likely to run into problems with performance of batch processes and reporting that looks at groups of students (by school, by grade, by class, report cards, etc.).

• The length and format of a student number varies by jurisdiction. Some may be numeric, some may be alphanumeric. Do you need to handle more than one format? If a numeric student number is used as the primary key now, you set yourself up for a world of hurt later if you need to change to alphanumeric (and not only from a database perspective!).

  Also, if that happens (or if you use alphanumeric to start), performance will be diminished, as string comparison is always going to be less efficient than integer comparison.

That you can guarantee the student number is unique is good. Make sure to enforce it using appropriate uniqueness and validation constructs.

Use the student number (alone, or in combination with other business keys) whenever it's necessary to integrate between your application and another.

Your software may fit a narrow box of a situation where it looks "okay" to use the student number as a primary key. If that's the case, I cannot offer many negatives about that approach. However, not using a surrogate now sets you up for pain down the road if you need to venture even an inch out of that box.

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.