Constants and the Database

In this post, I want to discuss a way of how to use values stored in constants in the database. The more specific use case is status values.

Now I know that when propagating a fully normalised data model, you would store status values in a reference table and just store these as a foreign key in your main table.

But software development with Appian can be made easier. And as soon as we try to keep all logic inside Appian, do we really need a fully normalised data model? Or, to ask this question differently, what is the benefit of going fully normalised? In an Appian context?

Status Values

In my main table, there is a text field called status. Then, I create a text constant in Appian for each value. A good name would be:

ABC_CASE_STATUS_OPEN

To make this even more expressive, I store the actual value OPEN in the description and as the value.

This way, I can use the individual constants for assignment and comparison.

List of Values

What do I do in situations where I need all these values in a list. You might say, let’s create another constant holding all values. Yeah, well, this means that you might end up with differences between the individual values and the list.

I solve this issue by creating an expression which returns all the values as a list.

{
  cons!ABC_CASE_STATUS_OPEN,
  cons!ABC_CASE_STATUS_APPROVAL,
  cons!ABC_CASE_STATUS_CLOSED,
  cons!ABC_CASE_STATUS_CANCELLED,
}

Summary

I know that this idea would probably send shivers down the spine of a database developer. However, I am convinced that we should allow ourselves to question traditional ways and to explore other avenues with Appian. I have always done well with this method, and maybe you will like it too.

Keep rocking!

6 thoughts on “Constants and the Database”

  1. What if there is a change in the value? Let’s say open is changed to something like Awaiting Action

    1. I typically create an expression or decision rule to turn technical strings into user visible strings and translate them if necessary. Then I can just change that and keep the values in the DB.

  2. Hi Stefan,
    Out of curiosity, what is the advantage of using constant over status reference table?
    What about maintainability and scalability?

    1. To be able to set and compare such values in interfaces, expressions or records, you need constants in Appian anyways. Then, a string like “WAITING_TO_REPLY” is much more expressive than “35”. And I never got into a situation in which I discovered any disadvantage. And I do not see any real benefit, so I decided to simplify things. I mean, isn’t this what low-code is about?

  3. What’s interesting is that even though we have database reference tables, we also wind up needing constants that point to the reference table primary keys. We can’t do workflow logic on the ‘status’ field without a constant of some sort. Seems like there’s room for some auto-generated constants somewhere in this problem, but the soft contracts / assumptions there would likely cause a bit of headache too.

    The main issue with lack of a reference table is that at enterprise scale, where the database is replicated or Appian is the source of truth that sends data to other systems via API, the Appian dev team can’t necessarily update downstream systems if a non-normalized label changes.

  4. I generally follow the approach suggested by Stefan by not having a catalog of statuses in the database. In fact, I’ve been doing this for several years whenever the client allows it. However, I’ve encountered a use case where the optimal solution is to have that status table, and that’s when there is a requirement for the status descriptions to be editable by administrative users through a table maintenance interface. In that case, it is necessary to have a status table with code and description fields, with the latter being the one that users can modify.

Leave a Reply