Data Deploy | Unique ID for parent table

rohitspathak123

Background

We are designing data deploy solution. Using TeamSite & Open Deploy. Our DCT has replicant field (just one) . For implementing data deploy we will have parent database table (which will store non-replicant field value) and child table (which will store replicant field values). There should be a relationship between parent & child table. Generally we make "DCR Path" (since it's always unique) as primary key in parent and reference the same in child table.

Query

What is the best attribute (DCR Path or Unique ID ) to maintain relationship between 2 tables ?

Approach 1 (DCR Path)
If we go with DCR Path, then we know beforehand the value of DCR path. In data deploy config file but parent & child table columns can refer to same value (using hidden field in DCT). This is simple, but making DCR Path as primary key in parent table is not good approach (performance issue while querying record on VARCHAR instead of NUMBER)

Approach 2 (Database Sequence)
Create a sequence in database. While saving DCR read this database sequence and update a hidden field in DCR with this information. During data deploy read this value and update the parent & child table accordingly. This will require a database interaction code to be written for DCT (extra overhead)

Approach 3 (Unique Number Generator)
Generate a unique number (say Current Time stamp string) onSave of DCR. Save this value in DCR hidden field. Use this number as primary key in parent table and in the child table. In this approach there is no overhead of writing database code on DCT side. But the unique number that will be generated is not sequential.

Is there any other approach to solve this problem ? I donot want to make DCR Path as primary key in parent table. My preference is to generate unique numbers which are sequential but at the same time I donot want to make database call from DCT to get the unique sequential number.

Please share your data deploy implementaion on similar lines

ISCBorisB

Background We are designing data deploy solution. Using TeamSite & Open Deploy. Our DCT has replicant field (just one) . For implementing data deploy we will have parent database table (which will store non-replicant field value) and child table (which will store replicant field values). There should be a relationship between parent & child table. Generally we make "DCR Path" (since it's always unique) as primary key in parent and reference the same in child table. Query What is the best attribute (DCR Path or Unique ID ) to maintain relationship between 2 tables ? Approach 1 (DCR Path) If we go with DCR Path, then we know beforehand the value of DCR path. In data deploy config file but parent & child table columns can refer to same value (using hidden field in DCT). This is simple, but making DCR Path as primary key in parent table is not good approach (performance issue while querying record on VARCHAR instead of NUMBER) Approach 2 (Database Sequence) Create a sequence in database. While saving DCR read this database sequence and update a hidden field in DCR with this information. During data deploy read this value and update the parent & child table accordingly. This will require a database interaction code to be written for DCT (extra overhead) Approach 3 (Unique Number Generator) Generate a unique number (say Current Time stamp string) onSave of DCR. Save this value in DCR hidden field. Use this number as primary key in parent table and in the child table. In this approach there is no overhead of writing database code on DCT side. But the unique number that will be generated is not sequential. Is there any other approach to solve this problem ? I donot want to make DCR Path as primary key in parent table. My preference is to generate unique numbers which are sequential but at the same time I donot want to make database call from DCT to get the unique sequential number. Please share your data deploy implementaion on similar lines

Architecturally #2 seems the cleanest approach. Sequence access overhead is in my experience negligent.

Depending on your schema you may try to further compartmentalize it so that "DB stuff stays in DB".
For example, declare "[BEFORE|AFTER] INSERT" trigger(s) with the Stored Procedure that queries your Sequence
and populates Key Entries, etc. It is not always possible but if you can do that CMS code will become "neutral" to DB
implementation details.

Note that this surrogate key approach does not guarantee uninterrupted key sequence either. Sequence is never rolled
back. So, if your transaction fails for whatever reason you'll have a unique but not sequential value returned by the next
Sequence query