What are the best practices for handling RDBMS table relationships. We usually c

If you have nested CDTs, I have seen issues when there is many-to-many relationship beyond 1 level. I prefer to keep the CDTs independent and then add foreign key in the tables to create relationship. Best practices : forum.appian.com/.../Database_Schema_Best_Practices.html

As you can see from the help pages fetch type is not a supported jpa configuration in Appian
forum.appian.com/.../Custom_Data_Types_from_XSD.html

Some guidelines on database design can be found here in the best practices sections
forum.appian.com/.../Appian_Best_Practices.html

I think it is best to create several CDTs mapped to the same tables tailored to a certain need. E.g. you need to fill a grid with many items but only need data from the main table, you create a flat CDT with only the needed data. For loading a single item you can create a full fledged CDT.

@tim.clarke I totally missed the fetch type annotation is not listed in supported annotation list.

Right now what we are doing is if object graph is not lengthy than we are using annotations to handle the mapping, in case there are several inter related objects than we handle the mapping ourselves by adding foreign keys as mentioned by @rajat.

Obviously in second case handling mapping manually, its pain in the neck because from database write,read to render the data on UI requires a whole lot of efforts as you need to write everything. Anyway we don't have any other option for it.


@stefan Sorry but I am not in full agreement of creating sub CDT (used to hold subset of all columns in master CDT) you have mentioned or it might be possible that the example is not the best fit here. For the projection on CDT columns we are using queryEntity as it provides more control also its easy to maintain in case we need to add or remove any column based on requirement from queryentity expression rather than modifying the sub CDT itself. Please let me know if I am on wrong track here.

Thanks all for your answers.

Yes, sure, it depends on how your data is designed etc. It is just one possible way to handle it.

As a general rule, we nest type lookups (eg product has product type nested) but outside of that, other than in some very specific use cases, we try to avoid nesting.