We have requirement of storing multiple CDTs in one transaction, But id of the t

Question

We have requirement of storing multiple CDTs in one transaction, But id of the the cdt1 is foreign key in cdt 2 and id of the cdt2 is foreign is cdt3
e.g

Tables

Permit
-------------
permitId
permitName

PermitStatus
--------------
permitStatusId
permitId
PermitStatusTypeId

PermitStatusState
------------------
permitStatusStateId
permitStatusId
permitStateTypeid

The options I have thought are
1. Create a Nested CDT
Permit
--PermitStatus
--PermitStatusState
Cons:- Nested CDT structure is not recommended as per best practices

2. Use of store procedure
Cons:- Need to maintain extra CDT for store proc, will loose CDT benefits.

Is there any other best way so that i can achieve this.

amans · Answer

Not that I am aware of, I would have created a small step by step process where I would persist permit table, then use that id to persist permit status table and so on.