Consumer Data Repository System (CDRS) – Database Design

Table of Contentss

Document Control

We will write a custom essay sample on
Consumer Data Repository System (CDRS) – Database Design
or any similar topic only for you
Order Now

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 – refActvity

1 Introduction

1.1 Purpose

This papers is the Database Design for the SYABAS Consumer Data Repository System Enhancements ( CDRS ) .

1.2 Target Audience

The mark audiences for this Data Migration Specification papers include:

  1. CDRS Technical Workgroup to supervise the undertaking advancement.
  2. Customer Service Department ( CSD ) to corroborate and verify the demands
  3. IT section to verify and O.K. the informations migration program
  4. Analysts and developers to plan and develop informations migration processes.

2 ER Diagram

3 Summary of Tables

No

Table Name

Description

tblAccount

Shop history profile

tblAccountUpload

Information related to account informations lading from BASIS to CDRS.

tblConsumer

Store consumer profile. A individual history may hold multiple consumers.

tblConsumerEmail

Store consumer electronic mail. A consumer may hold multiple electronic mails.

tblConsumerFax

Store consumer facsimile figure. . A consumer may hold multiple facsimile Numberss.

tblConsumerMobile

Store consumer nomadic figure. A consumer may hold multiple nomadic Numberss.

tblConsumerPhoneHome

Shop consumer place phone figure. A consumer may hold multiple place phone Numberss.

tblConsumerPhoneOffice

Store consumer office phone figure. A consumer may hold multiple office phone Numberss.

tblConsumerVerification

Track all consumer positions – position, day of the month changed, and updated by

tblUser

CDRS 2 user’s information, this will include username, watchword, function, day of the month assigned, user electronic mail, last login, logout clip and etc.

refAccountStatus

History position search tabular array ( this position is the same as history position in BASIS )

refConsumerType

Consumer type search tabular array

refDistrict

District search tabular array

refGTariff

GTariff search tabular array

refSTariff

STariff search tabular array

refWTariff

WTariff search tabular array

refState

State search tabular array

vwConsumer

Consumer position

vwContact

Consumer contact position

4 Detailed Table Design

4.1 tblAccount

Field

Type

Nothing

Default

Remarks

AccountID

bigint ( 20 )

No

Account Idaho

DistrictCd

char ( 2 )

No

District codification

AccountNo

bigint ( 10 )

No

Syabas client history no. In BASIS, this is consumer no

CheckDigit

tinyint ( 1 )

No

Account cheque figure

AccountStatusCd

char ( 2 )

No

History position. BASIS history position codification. Mention to refAccountStatus for valid values.

HouseNo

varchar ( 150 )

Yes

Nothing

Item 1 of reference format 13

BlockNo

varchar ( 150 )

Yes

Nothing

Item 2 of reference format 13

BuildingName

varchar ( 150 )

Yes

Nothing

Item 3 of reference format 13

LotNo

varchar ( 150 )

Yes

Nothing

Item 4 of reference format 13

Jalan

varchar ( 150 )

Yes

Nothing

Item 5 of reference format 13

Taman

varchar ( 150 )

Yes

Nothing

Item 6 of reference format 13

Seksyen

varchar ( 150 )

Yes

Nothing

Item 7 of reference format 13

Kampung

varchar ( 150 )

Yes

Nothing

Item 8 of reference format 13

Area

varchar ( 150 )

Yes

Nothing

Item 9 of reference format 13

Zip code

int ( 5 )

Yes

Nothing

Item 10 of reference format 13

PostCodeArea

varchar ( 150 )

Yes

Nothing

Item 11 of reference format 13

StateCd

char ( 1 )

Yes

Nothing

Valid codification – S, K, P

Address

varchar ( 600 )

Yes

Nothing

Single line reference – combination of format 13

Address1

varchar ( 65 )

Yes

Nothing

New reference line 1

Address2

varchar ( 65 )

Yes

Nothing

New reference line 2

Address3

varchar ( 65 )

Yes

Nothing

New reference line 3

Address4

varchar ( 65 )

Yes

Nothing

New reference line 4

Address5

varchar ( 65 )

Yes

Nothing

New reference line 5

Address6

varchar ( 65 )

Yes

Nothing

New reference line 6

ConTypeID

tinyint ( 4 )

Yes

Nothing

Consumer history type

SWTariffID

tinyint ( 4 )

Yes

Nothing

S Duty

GTariffID

tinyint ( 4 )

Yes

Nothing

G Duty

WTariffID

tinyint ( 4 )

Yes

Nothing

W Duty

WaterConsumption

decimal ( 10,4 )

Yes

Nothing

Average H2O ingestion

Indexs:

Keyname

Type

Field

Primary

Primary

AccountID

Index

AccountNo

4.2 tblAccountUpload

Field

Type

Nothing

Default

Remarks

UploadID

bigint ( 20 )

No

Upload Idaho. unique

DistrictCd

Char ( 2 )

No

District codification

UploadDate

day of the month

No

Date informations is uploaded

LastSupplyDate

day of the month

No

Incremental update day of the month. Date history is activated

Success

int ( 11 )

No

No of histories successfully uploaded

Failed

int ( 11 )

No

No of histories failed to be uploaded

Indexs:

Keyname

Type

Field

Primary

Primary

UploadID

4.3 tblIncrementalUpdateControl

Field

Type

Nothing

Default

Remarks

DistrictCd

Char ( 2 )

No

District codification

LastNewAccountDate

day of the month

No

This is the last supply day of the month used.

LastAccountProfileDate

day of the month

No

Last history profile update day of the month

LastInactiveAccountDate

Date

No

This is the last discon day of the month used.

LastWaterConsumptionDate

Date

No

This is the last billed day of the month used

4.4 tblConsumer

Field

Type

Nothing

Default

Remarks

ConsumerID

bigint ( 20 )

No

Consumer Idaho. unique

AccountID

bigint ( 20 )

No

Account Idaho.

ConsumerTypeID

tinyint ( 4 )

No

Type – proprietor ( 0 ) , renter ( 1 ) , other ( 2 ) . Mention to refConsumerType for inside informations

Name

varchar ( 150 )

No

Consumer name

NewICNo

varchar ( 15 )

Yes

Nothing

New IC figure. This can besides be other mention figure. This information is based on BASIS which has other mention figure kept in this field

OldICNo

varchar ( 15 )

Yes

Nothing

Old IC figure

ConsumerStatus

int ( 3 )

101 – Pending, 102 – Data Ready, 105 – Deleted

LastUpdateDate

day of the month

No

Last updated day of the month

LastUpdateBy

int ( 11 )

No

Record was last updated by who. Foreign key.

Indexs:

Keyname

Type

Field

Primary

Primary

ConsumerID

4.5 tblConsumerEmail

Field

Type

Nothing

Default

Remarks

EmailID

bigint ( 20 )

No

Primary key

ConsumerID

bigint ( 20 )

No

Foreign key

Electronic mail

varchar ( 50 )

No

Email reference

DefaultStatus

tinyint ( 1 )

No

0

Flag to bespeak default electronic mail

LastUpdateDate

day of the month

No

Last updated day of the month

LastUpdateBy

int ( 11 )

No

User Idaho who updated the record

Indexs:

Keyname

Type

Field

Primary

Primary

EmailID

4.6 tblConsumerPhoneOffice

Field

Type

Nothing

Default

Remarks

PhoneOfficeID

bigint ( 20 )

No

Primary key

ConsumerID

bigint ( 20 )

No

Foreign key

TelephoneNo

varchar ( 50 )

No

Telephone no

DefaultStatus

tinyint ( 1 )

No

0

Flag to bespeak default office phone

LastUpdateDate

day of the month

No

Last updated day of the month

LastUpdateBy

int ( 11 )

No

User Idaho who updated the record

Indexs:

Keyname

Type

Field

Primary

Primary

PhoneOfficeID

4.7 tblConsumerPhoneHome

Field

Type

Nothing

Default

Remarks

PhoneHomeID

bigint ( 20 )

No

Primary key

ConsumerID

bigint ( 20 )

No

Foreign key

TelephoneNo

varchar ( 50 )

No

Telephone no

DefaultStatus

tinyint ( 1 )

No

0

Flag to bespeak default place phone

LastUpdateDate

day of the month

No

Last updated day of the month

LastUpdateBy

int ( 11 )

No

User Idaho who updated the record

Indexs:

Keyname

Type

Field

Primary

Primary

PhoneHomeID

4.8 tblConsumerMobile

Field

Type

Nothing

Default

Remarks

MobileID

bigint ( 20 )

No

Primary key

ConsumerID

bigint ( 20 )

No

Foreign key

MobileNo

varchar ( 50 )

No

Telephone no

DefaultStatus

tinyint ( 1 )

No

0

Flag to bespeak default mobile phone

LastUpdateDate

day of the month

No

Last updated day of the month

LastUpdateBy

int ( 11 )

No

User Idaho who updated the record

Indexs:

Keyname

Type

Field

Primary

Primary

MobileID

4.9 tblConsumerFax

Field

Type

Nothing

Default

Remarks

FaxID

bigint ( 20 )

No

Primary key

ConsumerID

bigint ( 20 )

No

Foreign key

FaxNo

varchar ( 50 )

No

Phone no/Facsimile Number

DefaultStatus

tinyint ( 1 )

No

0

Flag to bespeak default facsimile figure

LastUpdateDate

day of the month

No

Last updated day of the month

LastUpdateBy

int ( 11 )

No

User Idaho who updated the record

Indexs:

Keyname

Type

Field

Primary

Primary

FaxID

4.10 tblUser

Field

Type

Nothing

Default

Remarks

UserID

int ( 11 )

No

Primary key

UserName

varchar ( 255 )

No

User login name

Electronic mail

varchar ( 255 )

Yes

Nothing

User electronic mail reference

Password

varchar ( 255 )

No

User watchword

Function

int ( 2 )

No

LastLogin

day of the month

No

LogoutTime

day of the month

No

AppID

varchar ( 255 )

No

DistrictCD

char ( 2 )

No

NovellID

varchar ( 150 )

Yes

Nothing

Assigned

varchar ( 2 )

Yes

Nothing

Indexs:

Keyname

Type

Field

Primary

Primary

UserID

4.11 tblAuditLog

Field

Type

Nothing

Default

Remarks

LogID

int ( 11 )

No

Primary key

LogDateTime

timestamp

No

Time cast

UserID

varchar ( 255 )

Yes

Nothing

User ID. Foreign cardinal

DisctrictCd

Varchar ( 2 )

No

District codification

ActivityID

Int ( 11 )

No

Activity performed by the user. Mention to refAudit for inside informations

DisctrictCd

Varchar ( 2 )

No

District codification

AccountID

Bigint ( 20 )

No

Account. Applicable if activity is related to an history

ConsumerID

Bigint ( 20 )

No

Consumer. Applicable if activity is related to a consumer

Remarks

Varchar ( 255 )

No

Additional info for the activity

Indexs:

Keyname

Type

Field

Primary

Primary

LogID

Index

UserID

Index

AccountID

Index

ConsumerID

Index

ActivityID

4.12 tblCummulativeSummary

Field

Type

Nothing

Default

Remarks

DisctrictCd

Char ( 2 )

No

District codification

DateSummary

Date

No

Date drumhead informations is generated

Entire Active Histories

Int ( 11 )

No

0

No of active histories

Entire Inactive Histories

Int ( 11 )

No

0

No of inactive histories

Entire Consumers

Int ( 11 )

No

chat
Jennyfer from PhDessay
Jennyfer
Hi! We can edit and customize this paper for you.
Just send your request for getting no plagiarism essay https://goo.gl/Crty7Tt