Hello, me and a colleague are trying to make a database where we have a table called "students" and one called "subjects", meaning many students can take many subjects. We've been researching for a bit and the way explained in the Appian Documentation is the one explained here, it says create both the parents and child CDTs, then create a third table where you have: a primary key, a foreign key that corresponds to the first table, a foreign key that corresponds to the second table, and an index. The problem is that by doing so, you can add duplicates of each, meaning that the same student can be assigned to the same subject twice (this screenshot is the Intermediary record type):
(I made 3 record types, Subjects, Students and Intermediary). That's why I wanted to ask all of you for any suggestions so that this table does not take duplicates, or if there's anything that we are doing wrong. Thank you!
Discussion posts and replies are publicly visible
Two options:
1) Low-Level: Define a unique key on the two foreign key fields in the database. Trying to insert duplicates will fail which is not easy to manage in Appian.
2) High-Level: Implement a check in Appian to prevent a user adding an already existing combination. Simple to implement and great UX, but not 100%, as the check and the insert are delayed.
Thank you! We just managed another way. You can actually create a table with 2 different primary keys, you can do so on the Cloud Database, then writing the proper SQL. After that, you can create a CDT instead of a Record Type, and marking the option "create it from a database table or view". Doing so this way doesn't give you an error.
Again, thanks for taking your time and answering!
trhrth
I don't think this will actually work - Appian only supports a single key as the primary key field, so you won't be able to insert or update data (regardless of whether you are using CDTs or Record Types).
If you are truly concerned about this case, I'd recommend doing both of what Stefan mentioned - that way it provides a good user experience for users and you can be certain there are no duplications.
You are right, it did allowed me to create the CDT with 2 primary keys but after that I couldn't do anything else, so I guess I'll have to stick with Stefan's answer. We were just thinking of complex scenarios that we could solve so it's really not a big deal. Thank you!
Hello again, I just read again your answer and realized that the screenshot that I shared would be the low-level option, right? I have 2 foreign keys, so as I result, I shouldn't be able to add duplicate values. If that happens, you mentioned that "inserting duplicates would fail", by that you mean that it is simply not possible (the user would not be able to do so, and a error message would be displayed or something similar) or that if it occurs, it would lead to an internal error?
Also, would the low-level option be wronger than the high-level option? I mean, would it be a viable option to implement in an application, or it's not correct enough?
Thank you and sorry in advance if I didn't explain good enough! I'm still learning a lot from Appian.
The database will refuse to insert that row and your process will be paused by exception. Appian has no way to make the user aware of this and resolve the issue.
The UI option is not fool-proof but helps the user, and the DB option is fool-proof but can only resolved by an admin.
Just pointing out the obvious: Both options can be combined,