Display Referance Data

Note: I am not keen to keep reference data in constant as I don't want to maintain 2 copies of reference data

View is my general approach so that I can tune view without making changes in Appian.

However, yours is a simple call and only two tables.

I have tested previously that this simple of a query across two tables is straight forward for hibernate. If you create a CDt that holds designation as nested, the query across two tables in hibernate will be very similar in execution time to using a view.

So, you really need to look at what all you want to do with the data. If you are going to filter by designation a lot, a view might make sense.

Just my thoughts. Hope that helps.

You could just do a nested CDT given the simplicity of this scenario.

As per my understanding using nested CDT is not a best practice, so generally if we do not go for Nested CDT then in this case, we need to hit the DB for twice, if i am not wrong, try using Query Database Smart Service where we can execute Join Queries, hence by hitting the DB for once, we can get the needed data

But also, in order to reduce number of calls it's not a good idea of going for Query Database Smart Service as well as Nested CDT, however these are the several optional available for us as @christineh explain above in brief.

An another option could be, creating a Appian Expression Function in java, which can execute any SQL Queries (including Join) into respective Database schema based on parameters provided to it through Appian, Dynamically, and returning JSON as response.

@ajinkyab fn!joinCdt() in the 'CDT Manipulation' at http://bit.ly/28MEhax might be another option.

I would prefer to use view in this case as you are just relating one item but if there are many properties like designation, location...more than 5 or 6 (lookups)and number of records in lookup/s are less (if less than 10-15 for each type ) and you are planning to show/bring 20-50 transnational records at once out of 10000 records (employees in your case) then one of the option is to fetch all lookups into local variables on load and do mapping local variables to Ids on currently displaying list in the grid dynamically (within with). In that case you are just bringing small set of lookup data only once and applying it to transaction data just before when you are showing it which is better than executing a view every time. However if lookup records are more than 100 then it is better to go with view as rules/forms need more memory to execute.

I have followed below approach
1. Fetch the reference data only for input designation id list
2. Create key value pair designation and designation description
3. Fetch the description from Key value pair list one by one for input designation id list
----------------------------------------------

load(
/*Get the Designation data from database only for input designation ids*/
local!designationList: rule!APP_GetDesignationList(
selectColumns: {
"designationId",
"description"
},
designationIdList: ri!designationIdList
).data,

/*Create Key Value Pair for designation ids and description*/
local!designationKeyValueList: apply(
rule!mo_getKeyValuePairCDT,
merge(
local!designationList.designationId,
local!designationList.description
)
),

/*Pass the input designation id one by one and get the description so that description will be in sorted order of input designationIdList*/
apply(
rule!APP_GetValueForKey,
ri!designationIdList,
local!designationKeyValueList
)
)

I think view is the best approach for this scenario. One do not have to bother about Appian Limitations at all while using a view.

Every approach has some tradeoffs.. A View can be used - the tradeoff is that, it reduces portability. You have to create the View manually. And it becomes problematic if you want to port the application to different databases - say from MySQL to SQl Server/Oracle - because the syntax is DB vendor specific. So, although Views can help - they reduce code portability - you will need to maintain multiple DB scripts.

Nested CDT can also be used. Note that Nested CDTs are not a totally bad idea. Nesting upto one level is just fine - and the Nested CDT models the Database model closely. Nesting more than 2 levels, will increase the query prepare and query execute time.

As @christineh mentions above, if you have nested CDT, the query built by queryEntity is same as the query that will get built for a DB View.

In case of nested CDT, you wont have to worry about code portability - since no View is involved. For simple entity relationships like this, nested CDT is fine.
That said- you can go with DB View at the cost of maintaining multiple DB scripts for each DB vendor.