Consumer Data Repository System (CDRS) †Database Design

Table of ContentssDocument ControlGeneral In doionRevision enterTable of Contentss1Introduction1.1Purpose1.2Target Audience2ER Diagram3Summary of Tables4Detailed Table Design4.1tblAccount4.2tblAccountUpload4.3tblIncrementalUpdateControl4.4tblConsumer4.5tblConsumerEmail4.6tblConsumerPhoneOffice4.7tblConsumerPhonehHome4.8tblConsumerMobile4.9tblConsumerFax4.10tblUser4.11tblAuditLog4.12tblCummulativeSummary4.13refAudit4.14refGtariff4.15refWtariff4.16refSwtariff4.17refState4.18refDistrict4.19refConsumerType4.20refAccountStatus4.21vwConsumer4.22vwContact5Lookup Codes5.1District Codes refDistrict5.2Account Status Codes refAccountStatus5.3Audit use refActvity1 Introduction1.1 PurposeThis papers is the Database Design for the SYABAS Consumer Data Repository System Enhancements ( CDRS ) .1.2 Target AudienceThe scotch audiences for this Data Migration Specification papers acceptCDRS Technical Workgroup to supervise the chore advancement.Customer Service Department ( CSD ) to corroborate and verify the demandsIT section to verify and O.K. the in initialiseions migration programAnalysts and developers to contrive and develop in formattingions migration processes.2 ER Diagram3 Summary of TablesNoTable NameDescriptiontblAccountShop annals profiletblAccountUploadInformation associate to account knowledges committal from BASIS to CDRS.tblConsumerStore consumer profile. A individual history may hold septuple consumers.tblConsumerEmailStore consumer electronic mail. A consumer may hold multiple electronic mails.tblConsumerFaxStore consumer counterpart figure. . A consumer may hold multiple facsimile machine Numberss.tblConsumerMobileStore consumer nomadic figure. A consumer may hold multiple nomadic Numberss.tblConsumerPhoneHomeShop consumer place yell figure. A consumer may hold multiple place phone Numberss.tblConsumerPhoneOfficeStore consumer office phone figure. A consumer may hold multiple office phone Numberss.tblConsumerVerificationTrack totally consumer gravels position, sidereal sidereal mean solar daylight of the month changed, and updated bytblUserCDRS 2 users information, this will include username, watchword, function, day of the month assigned, user electronic mail, last login, logout clip and etc.refAccountStatusHistory position search tabular array ( this position is the same as history position in BASIS )refConsumerTypeConsumer type search tabular arrayrefDistrictDistrict search tabular arrayrefGTariffGTariff search tabular arrayrefSTariffSTariff search tabular arrayrefWTariffWTariff search tabular arrayrefStateState search tabular arrayvwConsumerConsumer positionvwContactConsumer contact position4 Detailed Table Design4.1 tblAccountFieldTypeNothingDefaultRemarksAccountIDbigint ( 20 )NoAccount IdahoDistrictCdchar ( 2 )NoDistrict codificationAccountNobigint ( 10 )NoSyabas client history no. In BASIS, this is consumer noCheckDigittinyint ( 1 )NoAccount check over figureAccountStatusCdchar ( 2 )NoHistory position. BASI S history position codification. Mention to refAccountStatus for valid values.HouseNovarchar ( cl )YesNothingItem 1 of credit rating format 13BlockNovarchar ( cl )YesNothingItem 2 of credit entry format 13BuildingNamevarchar ( 150 )YesNothingItem 3 of fictional character format 13LotNovarchar ( 150 )YesNothingItem 4 of reference format 13Jalanvarchar ( 150 )YesNothingItem 5 of reference format 13Tamanvarchar ( 150 )YesNothingItem 6 of reference format 13Seksyenvarchar ( 150 )YesNothingItem 7 of reference format 13Kampungvarchar ( 150 )YesNothingItem 8 of reference format 13Areavarchar ( 150 )YesNothingItem 9 of reference format 13Zip codeint ( 5 )YesNothingItem 10 of reference format 13PostCodeAreavarchar ( 150 )YesNothingItem 11 of reference format 13StateCdchar ( 1 )YesNothingValid codification S, K, PAddressvarchar ( 600 )YesNothingSingle line reference combination of format 13Address1varchar ( 65 )YesNothingNew reference line 1Address2varchar ( 65 )YesNothingNew reference l ine 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 ingestionIndexsKeynameTypeFieldPrimaryPrimaryAccountIDIndexAccountNo4.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 histories successfully uploadedFailedint ( 11 )NoNo of histories failed to be uploadedIndexsKeynameTypeFieldPrimaryPrimaryUploadID4.3 tblIncrementalUpdateControlFieldTypeNothingDefau ltRemarksDistrictCdChar ( 2 )NoDistrict codificationLastNewAccountDateday of the monthNoThis is the last supply 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 billed day of the month used4.4 tblConsumerFieldTypeNothingDefaultRemarksConsumerIDbigint ( 20 )NoConsumer Idaho. uniqueAccountIDbigint ( 20 )NoAccount Idaho.ConsumerTypeIDtinyint ( 4 )NoType proprietor ( 0 ) , tenant ( 1 ) , other ( 2 ) . Mention to refConsumerType for inside informationsNamevarchar ( 150 )NoConsumer nameNewICNovarchar ( 15 )YesNothingNew IC figure. This can besides be other mention figure. This information is based on BASIS which has other mention figure unploughed in this fieldOldICNovarchar ( 15 )YesNothingOld IC figureConsumerStatusint ( 3 )101 Pending, 102 Data Ready, 105 DeletedLastUpdateDateday of the monthNoLast updat ed day of the monthLastUpdateByint ( 11 )NoRecord was last updated by who. Foreign key.IndexsKeynameTypeFieldPrimaryPrimaryConsumerID4.5 tblConsumerEmailFieldTypeNothingDefaultRemarksEmailIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyElectronic mailvarchar ( 50 )NoEmail referenceDefaultStatustinyint ( 1 )No0Flag to bespeak heedlessness electronic mailLastUpdateDateday of the monthNoLast updated day of the monthLastUpdateByint ( 11 )NoUser Idaho who updated the recordIndexsKeynameTypeFieldPrimaryPrimaryEmailID4.6 tblConsumerPhoneOfficeFieldTypeNothingDefaultRemarksPhoneOfficeIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyTelephoneNovarchar ( 50 )NoTelephone noDefaultStatustinyint ( 1 )No0Flag to bespeak default office phoneLastUpdateDateday of the monthNoLast updated day of the monthLastUpdateByint ( 11 )NoUser Idaho who updated the recordIndexsKeynameTypeFieldPrimaryPrimaryPhoneOfficeID4.7 tblConsumerPhoneHomeFieldTypeNothingDefaultRemarksPhoneHom eIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyTelephoneNovarchar ( 50 )NoTelephone noDefaultStatustinyint ( 1 )No0Flag to bespeak default place phoneLastUpdateDateday of the monthNoLast updated day of the monthLastUpdateByint ( 11 )NoUser Idaho who updated the recordIndexsKeynameTypeFieldPrimaryPrimaryPhoneHomeID4.8 tblConsumerMobileFieldTypeNothingDefaultRemarksMobileIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyMobileNovarchar ( 50 )NoTelephone noDefaultStatustinyint ( 1 )No0Flag to bespeak default mobile phoneLastUpdateDateday of the monthNoLast updated day of the monthLastUpdateByint ( 11 )NoUser Idaho who updated the recordIndexsKeynameTypeFieldPrimaryPrimaryMobileID4.9 tblConsumerFaxFieldTypeNothingDefaultRemarksFaxIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyFaxNovarchar ( 50 )NoPhone no/Facsimile NumberDefaultStatustinyint ( 1 )No0Flag to bespeak default facsimile figureLastUpdateDateday of the monthNoLast updated day o f the monthLastUpdateByint ( 11 )NoUser Idaho who updated the recordIndexsKeynameTypeFieldPrimaryPrimaryFaxID4.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 )YesNothingIndexsKeynameTypeFieldPrimaryPrimaryUserID4.11 tblAuditLogFieldTypeNothingDefaultRemarksLogIDint ( 11 )NoPrimary keyLogDateTimetimestampNoTime castUserIDvarchar ( 255 )YesNothingUser ID. Foreign cardinalDisctrictCdVarchar ( 2 )NoDistrict codificationActivityIDInt ( 11 )NoActivity performed by the user. Mention to refAudit for inside informationsDisctrictCdVarchar ( 2 )NoDistrict codificationAccountIDBigint ( 20 )NoAccount. Applicable if activity is related to an historyConsumerIDBi gint ( 20 )NoConsumer. Applicable if activity is related to a consumerRemarksVarchar ( 255 )NoAdditional info for the activityIndexsKeynameTypeFieldPrimaryPrimaryLogIDIndexUserIDIndexAccountIDIndexConsumerIDIndexActivityID4.12 tblCummulativeSummaryFieldTypeNothingDefaultRemarksDisctrictCdChar ( 2 )NoDistrict codificationDateSummaryDateNoDate drumhead informations is generatedEntire Active HistoriesInt ( 11 )No0No of active historiesEntire Inactive HistoriesInt ( 11 )No0No of motionless historiesEntire ConsumersInt ( 11 )NoEssay report Service Fully referenced, delivered on clip, Essay Writing Service.Assignment Writing Service Everything we do is focussed on composing the surmount possible assignment for your exact demandsTaging Service Our Marking Service will dish up you pick out the countries of your work that collect betterment.View our servicesFree APA Referencing Tool hold your 6th Edition APA mentions rapidly, easy and for drop outFree Harvard Referencing Tool Our free online Harvard Referencing Tool makes citing easy.Free Vancouver Referencing Tool Get aid with your Vancouver referencing with our free online tool.Free Study Guides Everything you need to cognize during your surveiesTopographic point an order now Our experts are waiting to assist you with your renderOur experts can assist you with your essay inquiryGet down your orderNo thanksRequest RemovalIf you are the original antecedent of this essay and no longer wish to hold the essay published on the UK Essays web site so delight chink on the nexus below to bespeak remotionRequest the remotion of this essay