Adding a new column or transition table to CDBHistoric

[bamador]

Hello Everyone,

I need to see the best way to add a way to track a tag or batch identifier for our internal use. But connect that to the historic record in CDBHistoric table. 

I was leaning towards a creating a transition table (Data Table Object) with my custom column (tag/batch), and link that to the record id within CDBHistoric. I worry that will cause a major slow down to the CDBHistoric performance when joining. 

Is there any better ideas or tips anyone could advise me on.

Best Regards,

Brandon

[BevanWeiss]

I think it will be tricky.  I've never tried it myself, but I think you would need to add a time constraint to the CDHistoric query also.

I think if you just try to query it (as either a top level or sub query reference) only tying it to the RecordId then I think it will return an error of an 'Unconstrained Historic Query'.

As such you would need to have an idea of what time range that RecordID would exist within, so you'd probably want to use the timestamp IN ADDITION to the RecordId.  So you could do:

SELECT xxxx

FROM DataTable T

LEFT OUTER JOIN CDBHistoric H

  ON H.TimeStamp = T.TimeStamp

  AND H.RecordId = T.HisRecordId

Adding such a record per historic entry would be quite expensive in regards to disk space (and disk IO).

[bamador]

@BevanWeiss Thanks for the reply, agree on your suggestions. I'm worried as well it will expensive on drive space. I would plan on using timestamp/recordTime as well if needed. Worried still about the performance cost while that additional custom data table grows, while fetching data.

Thanks for your thoughts.