Skip to main content
A
Aegis_Kleais
Inspiring
October 13, 2013
Answered

Proper application design - hardcoded vs dynamic

  • October 13, 2013
  • 1 reply
  • 981 views

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.

    This topic has been closed for replies.
    Correct answer Carl Von Stetten

    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.

    1 reply

    Carl Von Stetten
    Carl Von StettenCorrect answer
    Legend
    October 14, 2013

    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.

    A
    Aegis_KleaisAuthor
    Inspiring
    October 14, 2013

    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!

    A
    Aegis_KleaisAuthor
    Inspiring
    October 14, 2013

    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!

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices