Consumer Data Repository System (CDRS) †Database Design

Table of Contentss Record Control General Information Modification Log Table of Contentss 1Introduction 1.1Purpose 1.2Target Audience 2ER Diagram 3Summary of Tables 4Detailed Table Design 4.1tblAccount 4.2tblAccountUpload 4.3tblIncrementalUpdateControl 4.4tblConsumer 4.5tblConsumerEmail 4.6tblConsumerPhoneOffice 4.7tblConsumerPhonehHome 4.8tblConsumerMobile 4.9tblConsumerFax 4.10tblUser 4.11tblAuditLog 4.12tblCummulativeSummary 4.13refAudit 4.14refGtariff 4.15refWtariff 4.16refSwtariff 4.17refState 4.18refDistrict 4.19refConsumerType 4.20refAccountStatus 4.21vwConsumer 4.22vwContact 5Lookup Codes 5.1District Codes †refDistrict 5.2Account Status Codes †refAccountStatus 5.3Audit Activity †refActvity1 Introduction1.1 PurposeThis papers is the Database Design for the SYABAS Consumer Data Repository System Enhancements ( CDRS ) .1.2 Target AudienceThe mark crowds for this Data Migration Specification papers include:CDRS Technical Workgroup to manage the endeavor advancement.Customer Service Department ( CSD ) to prove and check the demandsIT segment to confirm and O.K. the informations relocation programAnalysts and engineers to design and create informations movement processes.2 ER Diagram3 Summary of TablesNoTable NameDescriptiontblAccountShop history profiletblAccountUploadInformation identified with account informations replenishing from BASIS to CDRS.tblConsumerStore customer profile. An individual history may hold different consumers.tblConsumerEmailStore purchaser electronic mail. A customer may hold various electronic mails.tblConsumerFaxStore purchaser copy figure. . A shopper may hold various copy Numberss.tblConsumerMobileStore purchase r migrant figure. A shopper may hold numerous migrant Numberss.tblConsumerPhoneHomeShop purchaser place telephone figure. A shopper may hold different spot telephone Numberss.tblConsumerPhoneOfficeStore customer office telephone figure. A buyer may hold various office telephone Numberss.tblConsumerVerificationTrack all customer positions †position, day of the month changed, and refreshed bytblUserCDRS 2 user’s data, this will incorporate username, watchword, work, day of the month doled out, client electronic mail, last login, logout clasp and etc.refAccountStatusHistory position search even cluster ( this position is equivalent to history position in BASIS )refConsumerTypeConsumer type search plain arrayrefDistrictDistrict search plain arrayrefGTariffGTariff search even arrayrefSTariffSTariff search even arrayrefWTariffWTariff search plain arrayrefStateState search plain arrayvwConsumerConsumer positionvwContactConsumer contact position4 Detailed Table Design4.1 tblAcco untFieldTypeNothingDefaultRemarksAccountIDbigint ( 20 )NoAccount IdahoDistrictCdchar ( 2 )NoDistrict codificationAccountNobigint ( 10 )NoSyabas customer history no. In BASIS, this is buyer noCheckDigittinyint ( 1 )NoAccount check figureAccountStatusCdchar ( 2 )NoHistory position. Premise history position codification. Notice to refAccountStatus for substantial values.HouseNovarchar ( 150 )YesNothingItem 1 of reference position 13BlockNovarchar ( 150 )YesNothingItem 2 of reference group 13BuildingNamevarchar ( 150 )YesNothingItem 3 of reference design 13LotNovarchar ( 150 )YesNothingItem 4 of reference group 13Jalanvarchar ( 150 )YesNothingItem 5 of reference group 13Tamanvarchar ( 150 )YesNothingItem 6 of reference position 13Seksyenvarchar ( 150 )YesNothingItem 7 of reference form 13Kampungvarchar ( 150 )YesNothingItem 8 of reference position 13Areavarchar ( 150 )YesNothingItem 9 of reference position 13Zip codeint ( 5 )YesNothingItem 10 of reference position 13PostCodeAreavarchar ( 150 )YesNothingItem 11 of reference group 13StateCdchar ( 1 )YesNothingValid codification †S, K, PAddressvarchar ( 600 )YesNothingSingle line reference †mix of arrangement 13Address1varchar ( 65 )YesNothingNew reference line 1Address2varchar ( 65 )YesNothingNew reference line 2Address3varchar ( 65 )YesNothingNew reference line 3Address4varchar ( 65 )YesNothingNew reference line 4Address5varchar ( 65 )YesNothingNew reference line 5Address6varchar ( 65 )YesNothingNew reference line 6ConTypeIDtinyint ( 4 )YesNothingConsumer history typeSWTariffIDtinyint ( 4 )YesNothingS DutyGTariffIDtinyint ( 4 )YesNothingG DutyWTariffIDtinyint ( 4 )YesNothingW DutyWaterConsumptiondecimal ( 10,4 )YesNothingAverage H2O ingestionIndexs:KeynameTypeFieldPrimaryPrimaryAccountIDIndexAccountNo4.2 tblAccountUploadFieldTypeNothingDefaultRemarksUploadIDbigint ( 20 )NoUpload Idaho. uniqueDistrictCdChar ( 2 )NoDistrict codificationUploadDateday of the monthNoDate informations is uploadedLastSupplyDated ay of the monthNoIncremental update day of the month. Date history is activatedSuccessint ( 11 )NoNo of chronicles effectively uploadedFailedint ( 11 )NoNo of narratives neglected to be uploadedIndexs:KeynameTypeFieldPrimaryPrimaryUploadID4.3 tblIncrementalUpdateControlFieldTypeNothingDefaultRemarksDistrictCdChar ( 2 )NoDistrict codificationLastNewAccountDateday of the monthNoThis is the last gracefully day of the month used.LastAccountProfileDateday of the monthNoLast history profile update day of the monthLastInactiveAccountDateDateNoThis is the last discon day of the month used.LastWaterConsumptionDateDateNoThis is the last charged day of the month used4.4 tblConsumerFieldTypeNothingDefaultRemarksConsumerIDbigint ( 20 )NoConsumer Idaho. uniqueAccountIDbigint ( 20 )NoAccount Idaho.ConsumerTypeIDtinyint ( 4 )NoType †owner ( 0 ) , leaseholder ( 1 ) , other ( 2 ) . Notice to refConsumerType for inside informationsNamevarchar ( 150 )NoConsumer nameNewICNovarchar ( 15 )YesNothingN ew IC figure. This can other than be other notice figure. This data depends on BASIS which has other notice figure kept in this fieldOldICNovarchar ( 15 )YesNothingOld IC figureConsumerStatusint ( 3 )101 †Pending, 102 †Data Ready, 105 †DeletedLastUpdateDateday of the monthNoLast refreshed day of the monthLastUpdateByint ( 11 )NoRecord was last refreshed by who. Remote key.Indexs:KeynameTypeFieldPrimaryPrimaryConsumerID4.5 tblConsumerEmailFieldTypeNothingDefaultRemarksEmailIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyElectronic mailvarchar ( 50 )NoEmail referenceDefaultStatustinyint ( 1 )No0Flag to bespeak default electronic mailLastUpdateDateday of the monthNoLast refreshed day of the monthLastUpdateByint ( 11 )NoUser Idaho who refreshed the recordIndexs:KeynameTypeFieldPrimaryPrimaryEmailID4.6 tblConsumerPhoneOfficeFieldTypeNothingDefaultRemarksPhoneOfficeIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyTelephoneNovarchar ( 50 )NoTel ephone noDefaultStatustinyint ( 1 )No0Flag to bespeak default office phoneLastUpdateDateday of the monthNoLast refreshed day of the monthLastUpdateByint ( 11 )NoUser Idaho who refreshed the recordIndexs:KeynameTypeFieldPrimaryPrimaryPhoneOfficeID4.7 tblConsumerPhoneHomeFieldTypeNothingDefaultRemarksPhoneHomeIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyTelephoneNovarchar ( 50 )NoTelephone noDefaultStatustinyint ( 1 )No0Flag to bespeak default place phoneLastUpdateDateday of the monthNoLast refreshed day of the monthLastUpdateByint ( 11 )NoUser Idaho who refreshed the recordIndexs:KeynameTypeFieldPrimaryPrimaryPhoneHomeID4.8 tblConsumerMobileFieldTypeNothingDefaultRemarksMobileIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyMobileNovarchar ( 50 )NoTelephone noDefaultStatustinyint ( 1 )No0Flag to bespeak default portable phoneLastUpdateDateday of the monthNoLast refreshed day of the monthLastUpdateByint ( 11 )NoUser Idaho who refreshed the recordIndex s:KeynameTypeFieldPrimaryPrimaryMobileID4.9 tblConsumerFaxFieldTypeNothingDefaultRemarksFaxIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyFaxNovarchar ( 50 )NoPhone no/Facsimile NumberDefaultStatustinyint ( 1 )No0Flag to bespeak default copy figureLastUpdateDateday of the monthNoLast refreshed day of the monthLastUpdateByint ( 11 )NoUser Idaho who refreshed the recordIndexs:KeynameTypeFieldPrimaryPrimaryFaxID4.10 tblUserFieldTypeNothingDefaultRemarksUserIDint ( 11 )NoPrimary keyUserNamevarchar ( 255 )NoUser login nameElectronic mailvarchar ( 255 )YesNothingUser electronic mail referencePasswordvarchar ( 255 )NoUser watchwordFunctionint ( 2 )NoLastLoginday of the monthNoLogoutTimeday of the monthNoAppIDvarchar ( 255 )NoDistrictCDchar ( 2 )NoNovellIDvarchar ( 150 )YesNothingAssignedvarchar ( 2 )YesNothingIndexs:KeynameTypeFieldPrimaryPrimaryUserID4.11 tblAuditLogFieldTypeNothingDefaultRemarksLogIDint ( 11 )NoPrimary keyLogDateTimetimestampNoTime castUserIDvarchar ( 25 5 )YesNothingUser ID. Outside cardinalDisctrictCdVarchar ( 2 )NoDistrict codificationActivityIDInt ( 11 )NoActivity performed by the client. Notice to refAudit for inside informationsDisctrictCdVarchar ( 2 )NoDistrict codificationAccountIDBigint ( 20 )NoAccount. Material if action is identified with a historyConsumerIDBigint ( 20 )NoConsumer. Relevant if movement is identified with a consumerRemarksVarchar ( 255 )NoAdditional data for the activityIndexs:KeynameTypeFieldPrimaryPrimaryLogIDIndexUserIDIndexAccountIDIndexConsumerIDIndexActivityID4.12 tblCummulativeSummaryFieldTypeNothingDefaultRemarksDisctrictCdChar ( 2 )NoDistrict codificationDateSummaryDateNoDate drumhead informations is generatedEntire Active HistoriesInt ( 11 )No0No of dynamic historiesEntire Inactive HistoriesInt ( 11 )No0No of dormant historiesEntire ConsumersInt ( 11 )NoEssay Writing Service Fully referenced, conveyed on cut, Essay Writing Service.Assignment Writing Service Everything we do is focussed on forming the most ideal task for your careful demandsTaging Service Our Marking Service will help you select the nations of your work that need betterment.View our servicesFree APA Referencing Tool Create your sixth Edition APA specifies quickly, simple and for free!Free Harvard Referen