Consumer Data Repository System (CDRS) †Database Design

confuse of ContentssDocument ControlGeneral developmentRevision LogTable of Contentss1Introduction1.1Purpose1.2Target interview2ER Diagram3Summary of Tables4Detailed Table Design4.1tblAccount4.2tblAccountUpload4.3tblIncrementalUpdateControl4.4tblConsumer4.5tblConsumerEmail4.6tblConsumerPhoneOffice4.7tblConsumerPhonehHome4.8tblConsumerMobile4.9tblConsumerFax4.10tblUser4.11tblAuditLog4.12tblCummulativeSummary4.13refAudit4.14refGtariff4.15refWtariff4.16refSwtariff4.17refState4.18refDistrict4.19refConsumerType4.20refAccount experimental condition4.21vwConsumer4.22vwContact5Lookup Codes5.1District Codes refDistrict5.2Account Status Codes refAccountStatus5.3Audit Activity refActvity1 Introduction1.1 PurposeThis papers is the in fix upionbase Design for the SYABAS Consumer Data Repository System Enhancements ( CDRS ) .1.2 Target AudienceThe fix audiences for this Data Migration Specification papers let inCDRS Technical Workgroup to supervise the undertaking advancement.Customer retu rn Department ( CSD ) to corroborate and verify the demandsIT role to verify and O.K. the datarmations migration programAnalysts and developers to plan and develop informations migration processes.2 ER Diagram3 Summary of TablesNoTable NameDescriptiontblAccountShop muniment profiletblAccountUploadInformation related to forecast informations lading from BASIS to CDRS.tblConsumerStore consumer profile. A man-to-man fib may bear threefold consumers.tblConsumerEmailStore consumer electronic mail. A consumer may maintain tenfold electronic mails.tblConsumerFaxStore consumer telefax enroll. . A consumer may hold twofold facsimile Numberss.tblConsumerMobileStore consumer nomadic figure. A consumer may hold multiple nomadic Numberss.tblConsumerPhoneHomeShop consumer place cry figure. A consumer may hold multiple place phone Numberss.tblConsumerPhoneOfficeStore consumer bit phone figure. A consumer may hold multiple office phone Numberss.tblConsumerVerificationTrack all consu mer locations position, twenty-four hour periodtime of the calendar month changed, and updated bytblUserCDRS 2 users information, this will include username, watchword, function, mean solar daylight of the month assigned, user electronic mail, final stage login, log start magazine publisher and etc.refAccountStatusHistory position search tabular align ( this position is the same as storey position in BASIS )refConsumerTypeConsumer compositors case 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 computer codeAccountNobigint ( 10 )NoSyabas client bill no. In BASIS, this is consumer noCheckDigittinyint ( 1 )NoAccount curb figureAccountStatusCdchar ( 2 )NoHistory position. BASIS history position codification. take note to refAccountStatus for valid values.HouseNovarchar ( 150 )YesNothingItem 1 of extension phone 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 rip reference combination of format 13Address1varchar ( 65 )YesNothingNew reference line 1 Address2varchar ( 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 piss 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 tblIncrementalUpdateControlFieldTypeNothingDefaultRemarksDistrictCdChar ( 2 )NoDistrict codificationLastNewAccountDateday of the monthNoThis is the finale 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 charge day of the month used4.4 tblConsumerFieldTypeNothingDefaultRemarksConsumerIDbigint ( 20 )NoConsumer Idaho. uniqueAccountIDbigint ( 20 )NoAccount Idaho.ConsumerTypeIDtinyint ( 4 )NoType proprietor ( 0 ) , renter ( 1 ) , otherwise ( 2 ) . watch 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 kept in this fieldOldICNovarchar ( 15 )YesNothingOld IC figureConsumerStatusint ( 3 )101 Pending, 102 Data Ready, 105 D eletedLastUpdateDateday of the monthNoLast updated day of the monthLastUpdateByint ( 11 )NoRecord was last updated by who. impertinent key.IndexsKeynameTypeFieldPrimaryPrimaryConsumerID4.5 tblConsumerEmailFieldTypeNothingDefaultRemarksEmailIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )No unusual keyElectronic mailvarchar ( 50 )NoEmail referenceDefaultStatustinyint ( 1 )No0Flag to invite indifference 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 negligence office phoneLastUpdateDateday of the monthNoLast updated day of the monthLastUpdateByint ( 11 )NoUser Idaho who updated the recordIndexsKeynameTypeFieldPrimaryPrimaryPhoneOfficeID4.7 tblConsumerPhoneHomeFieldTypeNothingDefaultRemarksPhoneHomeIDbigint ( 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 facsimil e 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. a pplicable if activity is related to an historyConsumerIDBigint ( 20 )NoConsumer. relevant 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 stable historiesEntire ConsumersInt ( 11 )NoEssay paternity proceeds full referenced, delivered on clip, Essay Writing Service.Assignment Writing Service Everything we do is focussed on composing the best possible duty assignment for your exact demandsTaging Service Our Marking Service will assist you pick out the countries of your work that need betterment.View our servicesFree APA Referencing shot develop your 6th Edition APA mentions rapidly, easy and for take overFree Harvard Referencing Tool Our shrive 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 rase an order now Our experts are delay to assist you with your examineOur experts can assist you with your essay inquiryGet down your orderNo thanksRequest RemovalIf you are the original beginning of this essay and no longer proclivity to hold the essay published on the UK Essays web site so savor chink on the nexus under to bespeak remotionRequest the remotion of this essay