In this post, I want to write about data modelling in the specific context of Appian and low-code. When using any low-code environment, the development of complex software can become less complicated. When it comes to designing a data model and mapping it to a relational database, I sometimes get the feeling that some Appian practitioners stick to their usual methods instead of taking a path appropriate to the low-code paradigm there as well.

I am very well aware that there is an extremely broad range of reasons to make a data model as simple as a single table, or to create several 10,000 tables like in a SAP system.

Let’s see, how we can create a data model that meets our requirements, but does not add any unnecessary bloat.

Process-First

My approach to data modelling is, to focus on the to-be implemented business process. The data model’s job is, to support this process, not the other way around!

Think about the database like you would think about any other integrated system. From an Appian perspective, it is just some data storage.

Logic vs. Data Persistence

I try to keep all my logic in Appian, and use the database as a dumb data persistence layer. Distributed but interdependent logic is a pain to understand, debug and maintain.

Lookup Tables

When designing a data model focusing on the database, practitioners tend to implement lookup tables holding id/value pairs for fields in other tables. Think of a table for your main case. It has a field “status” of type integer, which is a foreign key to another table holding all the possible status values for the case table.

I never do this, with one exception I will explain in a second. The reason, you ask? For what purpose? My approach is, to make the field “status” a string and keep all the possible values in constants in Appian. This way, my database table says something like “WAITING_FOR_FEEDBACK” instead of “17”. And I can do alle the required aggregations or filtering in the same way.

I small hint: You might need all the possible values in a list. Do NOT put all values in a constant of a list type. This way, you would need to reference individual values by a number. The result is that you cannot change the order of values in that list anymore. Create an individual constant for each value. Then create an expression which returns all the constants in a list. Problem solved!

Now about the exception, I appreciate your patience. In a situation in which this kind of values needs to be maintained by the end user, I consider a lookup table as the right choice. Keep in mind to implement a soft-delete logic to not break any existing references to this table.

Foreign-Keys & Constraints

As I try to keep my logic inside Appian, I do not see the need for defining foreign-key relationships inside the database.

But, to support the database in filtering data, make sure to add indexes to fields referencing other tables. Not doing so, will introduce a performance penalty as your data volume increases.

My Approach

Now, I want to briefly introduce my approach. In my experience, this is the least complicated way of creating a data model in the context of Appian.

Thinking about the process, I document the data model showing all entities and their relations. Then, I create all the data entities as CDTs.

Next, I adjust the XSD files to define a different data type for fields storing larger text. Appian uses the data type VARCHAR only, which can only hold a maximum of 4000 characters. And a database row can only contain a maximum of 16384 characters in VARCHAR fields in total. I also change the encoding and collation type on all fields that store text entered by the user, to be able to store 4-byte unicode characters like emojis.

@Column(columnDefinition="TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci")

Next, I create a data store, add all the created entities and publish it.

I complete that step with creating a SQL script file which adds any required indexes. This script can run at any given time, so I need to make sure it creates only non-existing objects, or deletes them first. Appian does not support creating indexes via an JPA annotation in the XSD.

At last, I create Records for each entity and define record relationships. Appian’s capability to query and aggregate data across multiple records makes database views obsolete for the most cases. In case I still need a database view, I add the SQL statement to the above script. This way, I can let Appian do the schema management. Appian does not know about views, so it creates a table instead. Therefore, I add a statement to drop an existing table having the same name before each “CREATE VIEW”.

Summary

So much for my thoughts on simplified data modelling with Appian. I am curious about your experiences and your approach. Please let me know in the comments below.