Referencing Database Primary Keys from Codebase

Question

I don't have a lot of experience with these kind of issues, but I feel I need to consult on this issue. The current codebase I'm working on is using what I consider to be a questionable technique to reference entries in Database tables.

We have a system where for certain rows the value in that row is referenced with a const in a format like const ID_ITEM_I_WANT = 1 and then to get the value of that row the query is made with the ORM to get the value.

My problem with this system is that if these tables are not static and can change. As well, it forces these tables to be manually assigned and updated in the seeders for the development environment and causes issues whenever they need to be changed. As well, if I want to add new entries I need to wait until the tables changes on the production server and then update the code and seeders to have a constant for whatever primary key has been assigned in production.

Is there a better way to handle these tables where I can lookup the values that are required from the DB, but not rely on any specific id in the code. That way the seeders can have more arbitrary data and the code can be decoupled from the values in the production database.

Answer 1

The easiest way to achieve this is to add a text column to that table with a value determined by a developer who write the SQL script to insert that data, then hard code that value as a constant in your application.

This solution starts to become cumbersome when you need to reference many rows in the same table.

Instead, you may need to analyze the use cases for this data and try to identify a missing data abstraction that could be accomplished by introducing a JOIN table between the table you are currently referencing with the ID_ITEM_I_WANT constant and other tables.

Answer 2

Hardcoded IDs refering to some special records in your database can only work in a reliable way if the creation of those records (including their IDs) is hardcoded in a comparable fashion, ideally with ID numbers from the same single-source-of-truth in your code.

Otherwise, you risk to create a system where the application works on one database (for example, on the developer database or the production db), but not on another, because their IDs do not match).

In a comment, you wrote:

the ones I'm concerned about are a lot of tables which just index types and these consist of just an id and a name [where ...] the name is subject to change

If you have the code which creates these records under your control, and the IDs are not generated by some external mechanism like auto-increments of the database, then this can work. If that's not the case, your tables need a secondary key column which is under your control (like the additional string column mentioned in Greg Burghardt's answer).