0

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.

  • 2
    That is a horrible design, as you've pointed out. Surely there is another column (or two) that you can use to query for your data? – Phil N DeBlanc Feb 28 at 11:31
  • What precsiely is a "seeder for the development environment"? – Doc Brown Feb 28 at 12:30
  • @PhilNDeBlanc I wouldn't say horrible, as it worked with the system which was designed in the past, but with a more modernized system it seems incompatible. For some tables it's not a problem because they have some kind of identifier string, but 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 or similar. Because the name is subject to change potentially it would be foolish to set consts to those, but the id is fairly constant, so the id is definitely better than the name. – Ashton Spina Feb 28 at 13:50
  • @DocBrown By seeder for the development environment I mean a script which adds values to the local database used for development. Something like this: laravel.com/docs/5.7/seeding . Unfortunately the current design is making it difficult to do this. – Ashton Spina Feb 28 at 13:52
  • You could introduce a mapping table that has a primary key controlled by your software developers, and a foreign key that references the value deployed to the database being used. The seeding process will customize the contents of the mapping table based on the deployed database. – BobDalgleish Feb 28 at 18:57
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.

  • This seems like a reasonable solution. Maybe some kind of identifier string such that the name or id of these rows can be anything, as long as the identifier is known to be immutable? It does seem like a lot of work to migrate the existing data to this setup, but maybe it's the only way. As far as abstracting the data, I've definitely been trying to avoid the case that I need these IDs in the new development. – Ashton Spina Feb 28 at 13:55
1

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.