Pega development provides a strong capability to develop an application without database knowledge. Pega platform supports rational as well as object-oriented programming design pattern for saving data in database tables but in general, Pega stores all the application specific data in BLOB, a column in database table.

Developers able to build application in record time as compare to other technologies’ applications. The main contributing factor in fast development is the object-oriented data model which is saving data in BLOB without creating additional database tables. Developer need not to have any special skill set to learn database concepts and no need to design database specific tables during kick start of the project. Building POCs are quicker and faster than any other technologies.

There are pros and cons of each technologies stack. If object-oriented data model and Blob concept is expediting the application development, but leaving few drawbacks at flip side as well. Few best practices have been mentioned below that developer must keep in mind while designing the Pega application.

Database Table Design Considerations:

• Table naming conventions: Stablish a design pattern for naming convention. Good to have name that can be easily identified by business entities.
• Avoid creating different columns for same entity which has been created in another table. Example: Insured name and insured ID is present in Insured Database table. Do not create Insured name in another table.
• Always reference data from source table.
• Use advance class table mapping to map data from imbedded pages
• Observed that saving data in BLOB acquires more space as compare to rational database table. Additional size could cause additional infrastructure cost. Developer must consider blob(pzpvstream) column carefully for high volume data table.

Developer must evaluate pros and cos of deciding the table structure between fully exposed table vs with BLOB (pzpvstream) column table.

Observation 1: An experiment carried to observe the table size between tables having blob and not having blob. Two tables were created to understand the impact of including blob in table. 50 thousand records were inserted in both table via activity. Comparison from below table reveals that csv file size of blob table compare to full flat table is very big.

Observation 2: Below table shows that flat table (TestTable*) with 50,000 records has acquired 31.41 MB space however blob table (TestTable5_blob**) acquired space of 928.16 MB which is about 29.54 times more than the flat table.

Conclusion: It is observed that inclusion of blob in database table provides faster application development and minimizes the dependencies to the DB knowledge but developer has to carefully select this column when application is dealing with high volume of records

SQL script used to create table: NO BLOB COLUMN:

USE [PEGA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [pega_data].[TestTable](

[ReportID] [int] NOT NULL,

[AddedBy] [varchar](100) NULL,

[pxCreateDateTime] [datetime] NULL,

[ID] [varchar](20) NULL,

[ReportId] [int] NOT NULL,

[Date] [varchar](64) NULL,

[Parent] [bit] NULL,

[CompanyName] [varchar](100) NULL,

[RegisteredNumber] [varchar](10) NULL,

[ParentRegisteredNumber] [varchar](10) NULL,

[MonthlyCreditGuide] [decimal](18, 2) NULL,

[Status] [varchar](32) NULL,

[DateOfMostRecentCCJ] [varchar](64) NULL,

[NaceCode] [varchar](10) NULL,

[DateOfLastFinancialReport] [varchar](64) NULL,

[PretaxProfitYear1] [decimal](18, 2) NULL,

[PretaxProfitYear2] [decimal](18, 2) NULL,

[PretaxProfitYear3] [decimal](18, 2) NULL,

[IntangibleAssets] [decimal](18, 2) NULL,

[Cash] [decimal](18, 2) NULL,

[BankOverdraft] [decimal](18, 2) NULL,

[ShortTermBankLoans] [decimal](18, 2) NULL,

[OtherShortTermFinance] [decimal](18, 2) NULL,

[WorkingCapital] [decimal](18, 2) NULL,

[LongTermBankLoans] [decimal](18, 2) NULL,

[OtherLongTermFinance] [decimal](18, 2) NULL,

[NetAssets] [decimal](18, 2) NULL,

[RevaluationReserve] [decimal](18, 2) NULL,

[ProfitAndLossReserveYear1] [decimal](18, 2) NULL,

[ProfitAndLossReserveYear2] [decimal](18, 2) NULL,

[ProfitAndLossReserveYear3] [decimal](18, 2) NULL,

[ProfitAndLossReserveYear4] [decimal](18, 2) NULL,

[ShareholdersFunds] [decimal](18, 2) NULL,

[NumberOfYearsTrading] [int] NULL,

[QuickRatio] [decimal](10, 2) NULL,

[UltimateParent] [varchar](100) NULL,

[HasAccounts] [bit] NULL,

[DMS_Id] [int] NULL,

[BuyerCountry] [char](3) NULL,

[ParentCountry] [char](3) NULL,

[StockYear1] [decimal](18, 2) NULL,

[StockYear2] [decimal](18, 2) NULL,

[StockYear3] [decimal](18, 2) NULL,

[SalesYear1] [decimal](18, 2) NULL,

[SalesYear2] [decimal](18, 2) NULL,

[SalesYear3] [decimal](18, 2) NULL,

[CurrentAssetYear1] [decimal](18, 2) NULL,

[CurrentLiabilityYear1] [decimal](18, 2) NULL,

[LegalForm] [varchar](64) NULL,

[CreditRating] [int] NULL,

[ReportProviderId] [int] NULL,

[HasPreTaxProfit] [bit] NULL,

[Turnover] [decimal](18, 3) NULL,

[NumberOfEmployees] [int] NULL,

[HasConsolidatedAccounts] [bit] NULL,

[ProviderCompanyId] [varchar](50) NULL,

[ProviderParentCompanyId] [varchar](50) NULL,

[pzInsKey] [varchar](256) NULL,

[pxInsName] [varchar](64) NULL,

[pxObjClass] [varchar](128) NULL,

[IsMigrated] [bit] NULL,

[FinalRating] [varchar](32) NULL,

[RatingDate] [varchar](32) NULL,

[VersionID] [decimal](18, 0) NULL,

[PDFReportID] [varchar](128) NULL,

[PDFDownloadDateTime] [datetime] NULL,

[Provider] [varchar](32) NULL,

[PdfDownloadStatus] [varchar](32) NULL,

[RetryPdfServiceAttemptCount] [int] NULL,

PRIMARY KEY CLUSTERED

(

[ReportID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

SQL to create table: With BLOB COLUMNS:

USE [PEGA]

GO

USE [PEGA]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [pega_data].[TestTable5_blob](

[ReportID] [int] NOT NULL,

[pzPVStream] [varbinary](max) NULL,

CONSTRAINT [PK_TestTable5_blob] PRIMARY KEY CLUSTERED

(

[ReportID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO