Aleksandar.Bundalo: UML model, PgModeler, database architecture

In this blog, i will work on database modeling, with pgModeler, i will present UML diagram.

For the purposes of system modeling, the system modeling language UML (Unified Modeling Language) was developed.

UML is a tool for visualizing, describing, building and documenting software support in the analysis and development of primarily software solutions.

Models are created in order to better understand the system because they help visualize the real or imagined system, describe its structure and behavior, represent a template by which the system can be implemented and document all the steps of building the system.

Models help the project team visualize the system being formed and allow recording various constraints on the system.

The example is presented without the use of ISO standards that follow the energy infrastructure, with the aim of showing the way of work and creating the database architecture.

PgModeler enables easy creation and editing of the database, export of formed tables in the form of SQL script, PNG image, SVG file format, HTML form or direct export to the PostgreSQL server.

In order to prevent the creation of errors or 'rules', PgModeler has a function that performs checks and in this way the database is formed without errors, with the so-called Validation function.

PgModeler enables the creation of PostGis extensions. PostGis is an extension for spatial databases and PostgreSQL. It is currently the most powerful open source tool for spatial databases. PostGis contributes to PostgreSQL several spatial data types and over 400 functions for working with those data types.

PostGis supports many OGC/ISO SQL/MM features that you can find in other relational databases, such as Oracle, SQL Server, MySQL and IBM DB2 as well as a number of additional spatial features that are unique to PostGis.

The procedure for creating tables with the necessary columns

It is necessary to pay attention to the data type, if the data is an address or location, the data must be entered as VARCHAR (Knjaza MILosa 10c not as CHAR) or date, PostgreSQL does not recognize the date format, DATE, it must be entered under INTEGER.

In previous versions of pgModeler, it was not possible to create a PostGIS extension for importing data, tables into PostgreSQL or another database (MySQL, SQlite). The picture shows the setting for the geometric data type.

PgModeler enables an easy understanding of the architecture of the base itself, the lines between the tables are easy to see and understand. Primary and foreign keys are the starting points for visualizing the relationship, and the relationship is shown on their edges.

Constraints representing keys can be seen as pk, fk and notnull as nn, in green on the right side of each table.

Formed tables without defined relations and hierarchy and with defined relations.

Data validation during export shows that there is no Postgis extension, select Apply fixes.

Exporting the model offers options, exporting directly to the database (PostgreSQL or another database) and exporting in sql format, which we can later import into the database or work on the tables later.