Consumer Data Repository System (CDRS) †Database Design

Table of Contentss Document Control General Information Revision 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 audiences for this Data Migration Specification papers include:CDRS Technical Workgroup to supervise the undertaking advancement.Customer Service Department ( CSD ) to corroborate and verify the demandsIT section to verify and O.K. the informations migration programAnalysts and developers to plan and develop informations migration processes.2 ER Diagram3 Summary of TablesNoTable NameDescriptiontblAccountShop history profiletblAccountUploadInformation related to account informations lading from BASIS to CDRS.tblConsumerStore consumer profile. A individual history may hold multiple consumers.tblConsumerEmailStore consumer electronic mail. A consumer may hold multiple electronic mails.tblConsumerFaxStore consumer facsimile figure. . A consumer may hold multiple facsimile Numberss.tblConsumerMobi leStore consumer nomadic figure. A consumer may hold multiple nomadic Numberss.tblConsumerPhoneHomeShop consumer place phone 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 positions – position, day of the month changed, and updated bytblUserCDRS 2 user’s 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 Ta ble 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 codification. Mention to refAccountStatus for valid values.HouseNovarchar ( 150 )YesNothingItem 1 of reference 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 13PostCodeA reavarchar ( 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 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 up loadedLastSupplyDateday 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 uploadedIndexs:KeynameTypeFieldPrimaryPrimaryUploadID4.3 tblIncrementalUpdateControlFieldTypeNothingDefaultRemarksDistrictCdChar ( 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 ) , renter ( 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 kept in this fieldOldICNovarchar ( 15 )YesNothingOld IC figureConsumerStatusint ( 3 )101 – Pending, 102 – Data Ready, 105 – DeletedLastUpdateDateday of the monthNoLast updated day of the monthLastUpdateByint ( 11 )NoRecord was last updated by who. Foreign 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 updated day of the monthLastUpdateByint ( 11 )NoUser Idaho who updated the recordIndexs:KeynameTypeFieldPrimaryPrimaryEmailID4.6 tblConsumerPhoneOfficeFieldTypeNothingDefaultRemarksPhoneOfficeIDbigint ( 20 )NoPrimary keyConsumerIDbigint ( 20 )NoForeign keyTelep honeNovarchar ( 50 )NoTelephone noDefaultStatustinyint ( 1 )No0Flag to bespeak default office phoneLastUpdateDateday of the monthNoLast updated day of the monthLastUpdateByint ( 11 )NoUser Idaho who updated the recordIndexs:KeynameTypeFieldPrimaryPrimaryPhoneOfficeID4.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 recordIndexs:KeynameTypeFieldPrimaryPrimaryPhoneHomeID4.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 recordIndexs:KeynameTypeFieldPrimaryPrimaryMobileID4.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 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 castUserID varchar ( 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. Applicable if activity is related to a consumerRemarksVarchar ( 255 )NoAdditional info for the activityIndexs:KeynameTypeFieldPrimaryPrimaryLogIDIndexUserIDIndexAccountIDIndexConsumerIDIndexActivityID4.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 need betterment.View our servicesFree APA Referencing Tool Create your 6th Edition APA mentions rapidly, easy and for free!Free 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 essayOur 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 below to bespeak remotion:Request the remotion of this essay