Can you add a field to a CDM CI_{subject}_CONTACT_HIST table?

[pcapazzi]

We would like to add a field to a contact history table in our CDM. Will CIS break due to this? We expect it to be null after data is published and a nightly process will update it. 

Thanks. 

[Dmitry_Alergant · Accepted solution]

First of all, there are already two flexible "Additional_Info" fields in the table that you can use for that purpose. They were made there for that purpose. You can also change their length if it's too short.

If these two fields are not enough and you want to add a couple extra fields on top, it can be done - as far as I remember, no out-of-the-box code in SAS will break just because of that. An official SAS recommendation is to be considerate for performance and don't overload the table excessively with multiple extra fields due to performance concerns, as the contact history tends to have a lot of records. But at the end, it's up to your database to handle.

[pcapazzi]

Can the field types of EXTERNAL_CONTACT_INFO_ID1 and EXTERNAL_CONTACT_INFO_ID2 be changed from VARCHAR(32) to INT with no issues as well?

[Dmitry_Alergant]

Should be of no impact for SAS MA. 

If you have SAS RTDM or may get it in the future, it has a built-in capability to write user-supplied values into these fields - and it expects them to be Varchars.  Similarly, it would not be a big surprise to see a new built-in capability to store information into these fields in new future versions of SAS MA (i have zero information on that, just a guess) - and if that happens it will also expect them to be character.

So, i'd rather either store numeric information in these varchar2 fields - you can always apply to_number() when you are accessing the data, like through a view. Or just added two new numeric fields.

[pcapazzi]

Thanks Dmitry!