What are the database schema changes in WebCATS build 424 released December 2008?

The changes to the database structure for WebCATS 4.1 build 424, WinCATS 4.1 build 128, and eCenter 1.2 build 808 are the most extensive changes that Softshare has implemented over the last couple of years. Centers that access the database directly and run custom scripts will need to be aware of these changes as part of the upgrade process.

What follows is an overview of some of the more significant changes. One note of interest: in some of the following bullet points we have said that we have "renamed” a table. Technically, the scripting can't simply rename a table, but we use this term to indicate the process of creating a new table with a new name and transferring data to the new table. We could have dropped the old table but, in many cases, we have chosen not to drop the old table just in case something goes wrong with the conversion. Therefore, you may notice that there is still a custTblClientAttachments table in the database, even though the notes below indicate that it has been "renamed" to custTblAttachments. Here's a complete listing of those obsolete tables that are left behind by the conversion process: custTblClientSurveyQuestions, custTblClientSurveyQuestionSets, custTblClientAttachments, custTblCompanies, custTblCompanyProductCodes, custTblPOCs.

The custTblConfiguration.ECENTER bit ("checkbox") column has been replaced with the custTblConfiguration.ECENTER_VERSION varchar ("string") field. Future builds of WebCATS will need to support eCenter 1.3, eCenter 2.0, and non-eCenter customers, so we need a field that indicates the eCenter version, not simply an eCenter/non-eCenter flag (and ECENTER_VERSION of null signifies a non-eCenter customer).
The following columns have been removed from custTblConfiguration and been replaced with rows in the custTblConfigData table. These are needed for eCenter 1.3, but will be deleted for eCenter 2.0 (and non-eCenter) customers: SRSET_ID, ECTR_ASK_SURVEYRESPONSES, ECTR_ASK_AWARDS, ECTR_ASK_INVESTMENTS, ECTR_ASK_JOBS, ECTR_TEXT_AWARDS, ECTR_TEXT_INVESTMENTS.
The existing custTblSurveyQuestions table has been renamed to custTblConfSurveyQuestions to more properly reflect the table's usage. This table contains the survey questions for the old five-category numeric surveys appearing at the bottom of conference records.
The existing custTblClientSurveyQuestionSets table has been renamed to custTblSurveys to reflect the fact that, in eCenter 2.0, it will contain more than just client survey definitions (there are also many more columns in this table, mainly for use by eCenter 2.0). Similarly, the existing custTblClientSurveyQuestions table has been renamed to custTblSurveyQuestions.
The custTblConferences table contains several new fields, but it is important that integrators know about the DEADLINE field. Upon conversion, this defaults to the value of the START_DATE, but may diverge once users start making conference edits with WebCATS 4.1 build 424. WebCATS sites that are publishing 'CATS conference records on other Web sites should start using the DEADLINE field rather than the START_DATE field to determine which conferences are available for sign-up.
The custTblClientAttachments table has been renamed to custTblAttachments to reflect the fact that it contains more than just client attachments.
The custTblSessSurveyResponses table has lost the DataValue01, DataValue02, ..., DataValue15 fields. In order to support an unlimited number of questions per survey, this data has been moved into the custTblUserData table (alongside the data for other user-defined fields). These records have FormID=39 and KEY_ID equal to the custTblSurveyResponses.ID that they belong to.
Conferences will support multiple instructors and there is a new table, custTblConferenceInstructors, that contains these associations. Note that "instructors" are really just counselor records, so the real content of the custTblConferenceInstructors record is the (conference) ID and the (instructor) COUNSEL_ID. The existing custTblConferences.COUNSEL_ID is still present, but should now be interpreted as the conference's primary instructor rather than the conference's sole instructor (similar to a primary contact for a client). There are PREP/TRAVEL/CONTACT time columns in the custTblConferenceInstructors table, but these are for future expansion and are unused in WebCATS 4.1 build 424.
The custTblConfiguration.TextFor641 text column has been removed and replaced with a row in the new custTblMessageTemplates table. custTblMessageTemplates is primarily intended to contain customizable e-mail messages and HTML text for eCenter, but the "SBA641 Agreement" text for the 641 Part I also logically belongs in this table. This is the only use of the custTblMessageTemplates table for WebCATS 4.1 build 424.
The custTblListMembers.ATTENDED bit column has been replaced with the custTblListMembers.STATUS varchar column. The new status column supports choices for provisional (P), waitlisted (W), registered (R), and attended (A) list members. On conversion, list members marked as attended have been converted to the "A" (attended) code, and those not marked as attended have been marked as "R" (registered).
The values of custTblMilestoneTypes.ShortDescription field have been changed for the system-defined impact milestones. This makes the client/counselor activity listings nicer and hopefully the change will not negatively concern anyone.
The custTblCompanies table has been eliminated and all of its fields have been transferred into custTblClients. The custTblPOCs table has been eliminated and all of its fields have been transferred into custTblIndividuals. There is no such thing as a COMPANY_ID anymore; custTblClients.INDIV_ID indicates the primary contact for the client/pre-client and custTblIndividuals.CLIENT_ID indicates the client/pre-client that the contact belongs to. This may sound a little confusing at first, the fact that client and contact now have links to each other, but keep in mind that that choice of joining the tables on INDIV_ID or CLIENT_ID is very significant.

Examples:

SELECT custTblIndividuals.* FROM custTblIndividuals INNER JOIN custTblClients ON custTblIndividuals.CLIENT_ID = custTblClients.CLIENT_ID returns all contacts who are associated with clients.
SELECT custTblIndividuals.* FROM custTblIndividuals INNER JOIN custTblClients ON custTblIndividuals.INDIV_ID = custTblClients.INDIV_ID returns all contacts who are primary contacts.
SELECT * FROM custTblIndividuals WHERE CLIENT_ID is Null returns all "standalone" (non-associated) contacts

Selecting contacts that are non-primary contacts of a client is slightly more involved, but still a lot simpler than it was in prior builds. These changes to clients and contacts are probably the single biggest thing to happen in this new release.

Also note that upon database conversion, we are modifying the audit log so that all previous company edits now show up as edits of the appropriate client record. Mandatory field configuration has been appropriately converted as well. Unfortunately, most client subsets and custom reports have probably been broken and will have to be manually repaired or deleted and recreated with Softshare’s assistance if desired. Some, but not all, contact subsets and custom reports may have broken as well depending on whether or not they reference client/company information.

The custTblCompanyProductCodes table has been replaced with the custTblClientProductCodes table. SIC/PSC/NAISC code selections are now linked directly to the client (via CLIENT_ID) rather than indirectly through COMPANY_ID. Note that this change has broken all client subsets and custom reports based on or including product code selections; Softshare will assist with the fixing or replacing of subsets as necessary.
LATITUDE and LONGITUDE fields have been added to the custTblCenters, custTblCounselors, custTblClients, custTblIndividuals, and custTblConferences tables. These fields are for future expansion and are not used by WebCATS 4.1 build 424. eCenter 2.0 will populate the custTblCenters fields to avoid having to repeatedly geocode (determine that latitude/longitude from street address) the center on every display of Google maps, but that's all we plan on doing for now.
The custTblSessMilestones.COUNSEL_ID and custTblSessInvestment.COUNSEL_ID fields have been removed, replaced with new custTblSessMilestoneCounselors and custTblSessInvestmentCounselors tables that support multiple counselors for milestones and investments, similar to how we support multiple counselors for counseling sessions. Milestones and investments are still single client record types, though, and they both still directly contain CLIENT_ID values. Note that this change has broken all milestone/investment subsets and custom reports based on or including counselors; Softshare will assist with the fixing or replacing of subsets as necessary.
The custTblSessCounseling.PREP and custTblSessCounseling.TRAVEL fields have been removed, replaced by new custTblSessCounselingCounselors.PREP and custTblSessCounselingCounselors.TRAVEL fields.

Note that the session counselor has never been system mandatory, but you now must have at least one session counselor selection in order to document the session's prep and travel time. For existing counseling sessions with no counselor the database conversion assigns a dummy counselor called "(unknown counselor)" in order to preserve non-zero prep and travel time. For counseling sessions with no counselor and zero prep and travel, no dummy counselor has been assigned because there is no prep/travel time to preserve.
For existing multi-counselor counseling sessions, we have no idea how much prep/travel time was performed by each counselor, so the conversion process assigns all of the prep/travel time to the session's oldest counselor (the one with the lowest COUNSEL_ID). If the customer wishes to go back and update counseling sessions to more accurately divide the time between the counselors, there is a new "* Number of Counselors" counseling session subset field that can be used to generate a list of multi-counselor sessions.
Note that this change has broken all counseling session subsets and custom reports based on or including prep/travel time; Softshare can assist with hand-fixing the database or deleting and recreating these subsets as appropriate.

Want more? Browse our extensive list of WebCATS FAQs.