Overview
No need of creating any new tables (or) no need to do build any new data model design from scratch to store customer related information inside Pega. We could use Pega system of record physical tables to store all customer details.
Star Schema concept has been used here, all customer information will be maintained in different tables based on category. All separate/category table information will be linked by a link table.
The system of record layer provides the following data structure that contains the data classes to hold customer data.
-
PegaData-Contact
-
PegaData-Organization
-
PegaData-Role
-
PegaData-ContactOrgRel
-
PegaData-OrgOrgRel
-
PegaData-ContactContactRel
-
PegaData-Address
With an example I have explained Pega’s system of record physical tables. Have a happy learning…
PegaData-Contact
System of record class for storing customer contact data. The personal information of customer like First name, Last Name, Date of Birth, Gender, Email Info, Contact Number etc… can be stored in this table.
Primary Key: Contact ID
Contact ID: [email protected]
PegaData-Organization
System of record class for storing customer organization data. The organization information of customer like Organization Type, Organization name, Start Date, End Date etc… can be stored in this table.
Primary Key: Organization ID
Organization ID: Indian Cricket Team
PegaData-Role
System of record class for storing customer role data. The role information of customer like Role Type, Role Name etc… can be stored in this table.
Primary Key: Role ID
Role ID: Captain
PegaData-ContactOrgRel
System of record class for establishing contact to organization relationship data.
PegaData-ContactOrgRel - The star schema consists of one or more fact tables referencing any number of dimension tables.
Foreign Key: Role ID, Contact ID, Organization ID
Role ID: Captain
Contact ID: [email protected]
Organization ID: Indian Cricket Team
This table will maintain relationship between Contact, Organization and Role and other basic information. When we need to get a complete report of customer PegaData-ContactOrgRel table information can be used, by using key value in this table, system can find related information from all linked tables.
PegaData-OrgOrgRel
System of record class for storing customer organization to organization relationship data. The information like Parent-Organization ID, Current-Organization ID, Current Role ID etc… can be stored in this table.
Foreign Key: Role ID, Organization ID, Parent Organization ID
Role ID: Captain
Organization ID: Chennai Super Kings
Parent Organization ID: Indian Cricket Team
This table will be for maintaining relationship between Contact, Current Organization and Parent Organization related information.
PegaData-ContactContactRel
System of record class for storing contact to contact relationship data. The information like Parent-Contact ID, Contact ID, Role ID etc… can be stored in this table.
Foreign Key: Role ID, Contact ID, Parent Contact ID
Role ID: Daughter
Contact ID: [email protected]
Parent Contact ID: [email protected]
This table will be for maintaining relationship between Person-to-Person and role related information.
PegaData-Address
System of record class for storing customer address data. The address information of customer like Primary Address, Secondary Address, State, Region etc… can be stored in this table.
Primary Key: Address ID
Address ID: Any Address Related Unique ID of a customer
Note: Please do not save Contact ID of user as key value for this table, then inside system we would not be able to maintain multiple address (Primary and Secondary) of customer.
Address ID field is to hold unique id of contact address and Domain ID is to hold customer Contact ID information. By using Domain ID system could establish the contact relationship with other linked table.
Primary Address
Address ID: 258/A
Domain ID: [email protected]
Primary Address: Yes
Secondary Address
Address ID: 258/B
Domain ID: [email protected]
Primary Address: No