top of page

Automatically generate schemas based on a data dictionary

In this post, I am going to describe the schema maintenance procedures that were developed using Postgres which is a part of an enterprise system platform that has an inherent capability to exchange data between systems.


The platform is based on the principle that we must leverage all possible constraint capabilities in the database to improve the efficiency of developers/users and maintain the database's integrity.


The schema maintenance procedures create the schema based on the data dictionary definition and update it if the definition changes. The procedures also add ‘not null’, foreign key, and unique constraints.


In addition, the spsysSchemaCopy procedure sets up a new schema for a new business unit or organization. It will assign the schema a unique system id from the central data server and embed it in the primary key so that all records in all tables are created with that system id. It also sets up an admin user and organization contact, registers the system on the central server, and copies the core data (like reference tables) into the schema.


If you are interested in checking out the source code for the schema procedures, please click on the following link. www.3d-ess.com\sourcecode and look up the following procedures- spsysSchemaUpdate, spsysNotNullConstraintGenerate, spsysUniqueConstraintGenerate, spsysForeignKeyConstraintGenerate, spsysSchemaCopy


I hope the source code for these procedures will be useful to you in your next project. If you are interested in learning more about the 3D Enterprise System platform, please go to www.3d-ess.com.


Thank you for your attention. I appreciate your comments, questions, and likes.

6 views0 comments

Recent Posts

See All

In this post I am going to describe the financial roll-up procedure that was developed using Postgres that is a part of an enterprise system platform that has an inherent capability to exchange data b

In this post, I have a #QuestionForGroup about creating an application framework to connect to the open-source platform I created using Postgres. The platform allows us to create enterprise systems th

In this post I am going to describe the data warehouse generate procedure (DW procedure) that was developed using Postgres that is a part of an enterprise system platform that has an inherent capabili

bottom of page