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.
Database Table Name
Detail about table
Exported size via CSV file
69 exposed columns Included and loaded with real time data, No blob column
Only Two columns Included. First is to store unique identifier and second is blob
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.
Table with blob column
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
I came here looking for answer to a related question. Say I do have my own table for my data with all exposed columns, no blob. My PK is always a non-business entity, i.e. Only for enforcing unique rows.
Now say I have a column "Name". I don't want it to be my PK. It can change *but* I need to keep it unique. Pega does not let me mark a column as unique - or at least I haven't figured out if it does.
Sounds like an easy thing for Pega to provide. For that matter uniqueness over multiple columns even - again I am not talking about composite PK, simply uniqueness constraint against column or columns in the database.
I could go and add these constraints against the db table. However, that's not preferable. Getting access to the database can be problematic especially in cloud environment. Can you suggest a solution I have missed? Right now I am resigned to simply do a check in the database before I insert a record, OR modify a record. Again, clunky.