Orange County NC Website
Browse       Search
DocuSign Envelope ID: 1CCA18DC-6CC1-428C-A15D-5173EE24B471 <br /> SOW Attachment H <br /> Data Conversion Process for EnerGov Enterprise Server <br /> (Template DB Option) <br /> Overview: <br /> This document is an intro to the SQL Server EG_Template database and how to populate it. <br /> Modularized Design: <br /> As with the EnerGov software, the EG_Template db is sectioned off into modules. Each contains one master table at the <br /> top of the chain (ex. 'permit' for the Permit module). Within each module,there will be various child tables branching <br /> out below the master table for that module (ex. 'permit_address', 'permit_note', etc.). <br /> There are tables that cross multiple modules. The most notable of these involve inspections and payment transactions. <br /> Database diagrams have been included in the EG_Template database. These show the tables and their relationships for <br /> each module. <br /> Required Fields: <br /> There are certain fields in the EnerGov software which are required fields, and we cannot write records to the EnerGov <br /> db without populating these columns. Sometimes, these required fields will not be available in the legacy source data, <br /> so a simple default value can be written to the EG_Template db to fulfill any NOT NULL constraint. <br /> Some of these fields are drop-down lists in EnerGov,which means that we will be restricted in the values that we can <br /> write to these required fields in the EnerGov db. For drop-down fields, there is no restriction on what can be written in <br /> the EG_Template db. So, exact spelling or careful matching to the EnerGov configured values is not an issue for fields <br /> that are destined for EnerGov drop-down fields. We will run these through a separate mapping table to translate the <br /> values to the appropriate EnerGov value during conversion. These mappings will be negotiated during the development <br /> phase of the conversion. <br /> Custom Fields(any fields not available in the master table for the module in question): <br /> Most legacy systems will have some attribute fields that are not specified in the corresponding master table within <br /> EG_Template. In EnerGov,we will refer to these as custom fields. Within each module, there will be a child table for <br /> such custom fields. Since these are specific to the legacy system(s),you may add columns to these tables in <br /> EG_Template to accommodate any needed custom fields in the migration. For example, 'permit_additional_fields' is the <br /> table for extra fields relating to the 'permit' records. <br /> Gap Handling(where legacy data doesn't fit anywhere within EG_Template): <br /> There are sometimes special features of a legacy system which EnerGov does not account for in the EG_Template db. <br /> We may have to work out a custom solution to handle these special cases. <br /> Contacts: <br /> This is always a big topic for data migrations. These generally fall into two categories: <br /> 1. Those contacts that were managed with each person/company having one contact record, which is kept up to <br /> date over time. As this person/company is associated with records over time (getting a business license, pulling <br /> permits, being associated to a code violation), that one contact record is attached to the permit, license, code <br /> case, etc. With this model, there is generally no duplication of contact records (except when created by <br /> mistake). <br /> 2. Contacts where the user keys the contact attribute info on each permit, case, license, etc. With this model, <br /> there is no single master record representing the contact itself. So, if a contact has been associated to 10 <br /> different permits over time, there would be 10 records with the contact attributes (each one will likely have <br /> slightly different values in the various fields like name, address, phone, etc.). With this model, there is <br /> considerable duplication of contacts. <br />