Proper application design - hardcoded vs dynamic

[Aegis_Kleais]

We have a table, 2 columns.  ID and NAME.  When our application installs, the script writes 3 records to it in the following sequence: 'Admins', 'Users', 'Visitors'.  So one would think, the table data looks like:

ID - NAME

1 - Admins

2 - Users

3 - Visitors

So, when it comes to coding the application, is it better practice to assume that "Admins will always have an ID of 1, so I can write a SQL statement that may reference:  WHERE ID = 1 -- These are admins

Or should I be dynamic, where I would build a function called: getIDByName() and you can pass in the name 'Admins' and it will perform a SQL run and return back the number 1.  This would mean that when I write the above SQL statement I would say:

.... WHERE ID = getIDByName( 'Admins' ) -- Admins

I have a feeling that dynamic is the better approach, even though it means I'll have to code in more functions to automate it.

[Carl Von Stetten · Accepted solution]

Aegis,

Is the "ID" column an auto-incrementing identity column that is controlled by the underlying database?  If so, I would err on the side of caution and not assume that the records will be written in the precise order you want.  What you might do is query that table once and cache the results in an application scope variable, then query that as needed to get the IDs as needed (saving multiple round trips to the database to retrieve the IDs over and over again.

HTH,

-Carl V.

[Aegis_Kleais]

Carl.

That's an amazingly good solution!  I've now modified my Security object to get the 3 most common role IDs dynamically (as well as the 8 global right IDs) and store them into its properties during its initialization.  Surprisingly, everything worked FLAWLESSLY on the first attempt. (love it when that happens).

So I'll continue to do things dynamically, but I need to remain vigilant whether commonly called information could be better optimized by storing the data in an appropriate scope.  Since the Security object exists in the APPLICATION scope, all the transient requests to it pretty much reference the same data; perfect!

I really appreciate it buddy; thanks!

[Aegis_Kleais]

On a side note, I'm kinda proud of myself.  I had all these getColNameByColName() methods everywhere and I got into a more OOP-mentality.  So I replaced them all with 1 function like:

public any getValueByReference(

     required string @tableName,

     required string @returnField,

     required struct @criteria

)

So I could say: getValueByReference( 'pages', 'id', { 'name' = 'welcome', 'module_id' = 3 } )

And it would look at the Pages table and find a record where the name column = 'welcome' and the 'module_id' column = 3, and then return me that record's 'id' field.   Love it!

[Carl Von Stetten]

Glad that worked out!

-Carl V.