Consumer Data Repository System (CDRS) †Database Design

Table of Contentss Archive Control General Information Amendment 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 direct the endeavor advancement.Customer Service Department ( CSD ) to validate and confirm the demandsIT area to check and O.K. the informations movement programAnalysts and designers to design and create informations relocation processes.2 ER Diagram3 Summary of TablesNoTable NameDescriptiontblAccountShop history profiletblAccountUploadInformation identified with account informations replenishing from BASIS to CDRS.tblConsumerStore purchaser profile. An individual history may hold numerous consumers.tblConsumerEmailStore purchaser electronic mail. A buyer may hold numerous electronic mails.tblConsumerFaxStore shopper copy figure. . A customer may hold different copy Numberss.tblConsumerMobileStore buyer iti nerant figure. A purchaser may hold numerous migrant Numberss.tblConsumerPhoneHomeShop buyer place telephone figure. A customer may hold various spot telephone Numberss.tblConsumerPhoneOfficeStore buyer office telephone figure. A shopper may hold numerous 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 appointed, client electronic mail, last login, logout clasp and etc.refAccountStatusHistory position search plain exhibit ( this position is equivalent to history position in BASIS )refConsumerTypeConsumer type search even arrayrefDistrictDistrict search even arrayrefGTariffGTariff search even arrayrefSTariffSTariff search even arrayrefWTariffWTariff search even arrayrefStateState search plain arrayvwConsumerConsumer positionvwContactConsumer contact position4 Detailed Table Design4.1 tblAccountField TypeNothingDefaultRemarksAccountIDbigint ( 20 )NoAccount IdahoDistrictCdchar ( 2 )NoDistrict codificationAccountNobigint ( 10 )NoSyabas customer history no. In BASIS, this is shopper noCheckDigittinyint ( 1 )NoAccount check figureAccountStatusCdchar ( 2 )NoHistory position. Premise history position codification. Notice to refAccountStatus for legitimate values.HouseNovarchar ( 150 )YesNothingItem 1 of reference group 13BlockNovarchar ( 150 )YesNothingItem 2 of reference design 13BuildingNamevarchar ( 150 )YesNothingItem 3 of reference position 13LotNovarchar ( 150 )YesNothingItem 4 of reference position 13Jalanvarchar ( 150 )YesNothingItem 5 of reference position 13Tamanvarchar ( 150 )YesNothingItem 6 of reference design 13Seksyenvarchar ( 150 )YesNothingItem 7 of reference form 13Kampungvarchar ( 150 )YesNothingItem 8 of reference group 13Areavarchar ( 150 )YesNothingItem 9 of reference position 13Zip codeint ( 5 )YesNothingItem 10 of reference group 13PostCodeAreavarchar ( 150 )Ye sNothingItem 11 of reference position 13StateCdchar ( 1 )YesNothingValid codification †S, K, PAddressvarchar ( 600 )YesNothingSingle line reference †blend of configuration 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 uploadedLastSupplyDateday 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 ) , tenant ( 1 ) , other ( 2 ) . Notice to refConsumerType for inside informationsNamevarchar ( 150 )NoConsumer nameNewICNovarchar ( 15 )YesNothingNew IC f igure. 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. Outside 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 )NoTelephone 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 recordIndexs:Keyn ameTypeFieldPrimaryPrimaryMobileID4.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 ( 255 )Yes NothingUser ID. Outside cardinalDisctrictCdVarchar ( 2 )NoDistrict codificationActivityIDInt ( 11 )NoActivity performed by the client. Notice to refAudit for inside informationsDisctrictCdVarchar ( 2 )NoDistrict codificationAccountIDBigint ( 20 )NoAccount. Appropriate if action is identified with a historyConsumerIDBigint ( 20 )NoConsumer. Material 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 making the most ideal task for your definite demandsTaging Service Our Marking Service will help you choose the nations of your work that need betterment.View our servicesFree APA Referencing Tool Create your sixth Edition APA makes reference to quickly, simple and for free!Free Harvard Referen