Consumer Data Repository System (CDRS) †Database Design

Table of ContentssDocument ControlGeneral In coiffeionRevision LogTable of Contentss1Introduction1.1Purpose1.2Target auditory sense2ER 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 Activity refActvity1 Introduction1.1 PurposeThis written document is the Database Design for the SYABAS Consumer Data Repository System Enhancements ( CDRS ) .1.2 Target AudienceThe mark audiences for this Data Migration Specification papers includeCDRS Technical Workgroup to supervise the undertaking advancement.Customer Service Departmen t ( CSD ) to corroborate and verify the demandsIT section to verify and O.K. the in coifions migration programAnalysts and developers to plan and develop informations migration processes.2 ER Diagram3 Summary of TablesNoTable NameDescriptiontblAccountShop news report profiletblAccountUploadInformation related to account informations lading from BASIS to CDRS.tblConsumerStore consumer profile. A individual invoice may hold fourfold consumers.tblConsumerEmailStore consumer electronic mail. A consumer may hold multiple electronic mails.tblConsumerFaxStore consumer facsimile figure. . A consumer may hold multiple facsimile Numberss.tblConsumerMobileStore consumer nomadic figure. A consumer may hold multiple nomadic Numberss.tblConsumerPhoneHomeShop consumer place r wholey figure. A consumer may hold multiple place phone Numberss.tblConsumerPhoneOfficeStore consumer office phone figure. A consumer may hold multiple office phone Numberss.tblConsumerVerificationTrack all consumer posit ions position, mean solar daytime 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 look tabular array ( this position is the same as tarradiddle position in BASIS )refConsumerTypeConsumer type explore 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 cheque figureAccountStatusCdchar ( 2 )NoHistory position. BASIS history position codif ication. insinuate to refAccountStatus for valid values.HouseNovarchar ( one hundred fifty )YesNothingItem 1 of reservoir format 13BlockNovarchar ( 150 )YesNothingItem 2 of reference format 13BuildingNamevarchar ( 150 )YesNothingItem 3 of reference 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 canal reference combination of format 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 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 tblIncrementalUpdateControlFieldTypeNothingDefaultRemarksDistrictCdCh ar ( 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 monthLastInactive voiceAccountDateDateNoThis 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 ) , renter ( 1 ) , other ( 2 ) . Mention to refConsumerType for inside informationsNamevarchar ( 150 )NoConsumer nameNewICNovarchar ( 15 )YesNothingNew IC figure. This can besides be other quotation figure. This information is based on BASIS which has other mention figure kept in this fieldOldICNovarchar ( 15 )YesNothingOld IC figureConsumerStatusint ( 3 )101 Pending, 102 Data Ready, 105 DeletedLastUpdateDateday of the monthNoLast updated day of the monthL astUpdateByint ( 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 bode default 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 tblConsumerPhoneHomeFieldTypeNothingDefaultRemarksPhoneHomeIDbigint ( 20 )NoPrimary ke yConsumerIDbigint ( 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 of 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 historyConsumerIDBigint ( 20 )NoConsumer. Appli cable 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 inactive historiesEntire ConsumersInt ( 11 )NoEssay Writing Service Fully referenced, delivered on clip, Essay Writing Service.Assignment Writing Service Everything we do is focussed on composing the best possible assignment for your exact demandsTaging Service Our Marking Service will assist you pick out the countries of your work that pauperization betterment.View our servicesFree APA Referencing scratch Create your 6th Edition APA mentions rapidly, easy and for freeFree 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 turn outOur experts can assist you with your essay inquiryGet down your orderNo thanksRequest RemovalIf you are the original author of this essay and no longer wish to hold the essay published on the UK Essays web site so delight chink on the nexus beneath to bespeak remotionRequest the remotion of this essay