How to add the data to DB within a 2 tables 1-N relation?

Question

Hi, 
 When I have to add data within a 1-1 relation between 2 tables, I call the "Write to data store entity" Smart Service to add the new record in the parent table and then, from this component output, I retrieve the generated key Id, to set the Foreign Key in the child table. 
 
 How would you do for a 1-N relation? 
 
 Is there any way to add the data in the 2 tables (1 record in the parent table, and N records in the child table) in a simple way without doing any loop to set the FK ? 
 
 What is the better way to do it please ?

Dave Lewis · Answer

Two consecutive write to datastore nodes would be much safer. It ensures that you won't even start writing contacts with dangling pointers until the object of their pointer is fully built. This is also a good approach because you won't use Appian to get the primary key of the customer before writing it (which invites race conditions) but rather rely on the DB's sequencer to get you the next unused ID. 
 You can't possibly use that to populate the children with the right FK reference if they were written first, but you can extract it from the Stored Values output of the first Write to Datastore node. You can save that as a PV, then throw a script task in between the two Write to Datastore nodes to explicitly plop the FK reference into each of the contacts in a forEach, then write them.

Mike Schmitt · Answer

This doesn't seem so bad, to me. It's the sort of thing that only needs to be done the "first time", as it were. If you were in some crunch to reduce the total number of nodes to the absolute minimum, then I assume the "save IDs to FK" could be condensed either into the data outputs of "Write Customer", or the inputs of "Write Contacts", though either of these approaches would increase the complexity in the target node by a larger factor than what you'd be reducing the process flow by.

Mike Schmitt · Answer

It's fairly simple, all you need to do is set the parent table foreign key value in each member of the child table array when you're creating them (or just before writing to the DB if necessary). No "looping" per se is needed, though one of the easier ways to populate every member of an array with a certain value can sometimes be using a!forEach() ... but the exact implementation really kinda depends on the subtleties of your exact implementation so far.

Mike Schmitt · Answer

Just to confirm, this would be my default approach as well. 
 Personally, I only use Mutliple WTDS nodes when i'm dealing with already-existing rows and with no question as to whether they already exist and the CDTs i'm dealing with are populated or not. (And even then, really only when there's a tight crunch on the number of nodes I can use in that particular process flow.) Otherwise it's always single-WTDS nodes for me.

How to add the data to DB within a 2 tables 1-N relation?

Top Replies