Appendix V: Glossary

The terms of this manual, in alphabetical order inside each group: the ones of pgModeler, the relationship types, the ones of PostgreSQL and the ones of data modeling in general.

1. pgModeler terms

Alias: a friendly name that can be given to an object. It is shown instead of the name when the model is displayed in the compact view.

Canvas: the area of the design view where tables, views and relationships are drawn. See Working on the canvas.

Cardinality: whether each side of a relationship needs to have a counterpart (required) or not (optional). It changes how the foreign key of the relationship is generated. See Relationships.

Changelog: the list of the create, update and delete operations done over the objects of a model, which allows a partial diff with only what changed. See Model changelog.

Code snippet: a fragment of text, in pure SQL or written with the schema microlanguage, kept in the settings to avoid typing repetitive code in the SQL tool. See Snippets settings.

Column propagation: the mechanism by which a relationship creates, in the table that receives it, the columns and constraints that represent the link between the tables. The generated objects are shown in green and italics and are managed by the relationship.

Compact view: a way of showing the model without the column types and the constraint descriptors, using aliases, so that it is easier to discuss with people who are not technical. See Compact view of the model.

Connection: the set of parameters (host, port, user, database, SSL mode...) used to reach a PostgreSQL server. They are configured in the settings and used by the export, import, diff and the SQL tool. See Connections settings.

Crash handler: the application that opens when pgModeler quits unexpectedly and generates a report with technical details. See Crash handler and bug report.

Custom SQL: commands that you append or prepend to the SQL of an object, for example to populate a table right after it is created. See Custom SQL commands.

Design view: the view of the main window where the model is created and edited. The other views are Welcome, Manage, Import, Export, Diff, Fix and Configure.

Diff: the comparison between a model and a database (or between two databases) that generates the SQL needed to make them equal. A Plus feature. See Compare model and database.

Disable SQL code: an option of every object that keeps it in the model but leaves it out of the generated SQL. It is how a stub object is made.

Export: the generation of the model in another form: SQL script, a PNG or SVG image, a data dictionary, or the creation of the database directly on a server. Also called forward engineering. See Export a model.

Fade out and fade in: reduce or restore the opacity of objects on the canvas to focus on part of a crowded model. It changes only how the objects are drawn, not the SQL. See Fading objects.

Generic SQL object: a custom command wrapped as an object of the model, to represent something that pgModeler does not support natively. See Generic SQL objects.

Import: the reading of an existing database to build a model from it. Also called reverse engineering. A Plus feature. See Import a database.

Layer: a way of showing only part of the objects of a model on the canvas. See Layers management.

Magnifier: the tool (key F9) that shows, in a zoomed-out model, the area under the cursor at the normal scale. See Magnifier tool.

Metadata (of objects): everything that exists only at design time and is not converted into SQL, such as tags, textboxes and the positions of the objects. The Objects' metadata tool of the Fix view saves it from a model and applies it to another one, for example to a model rebuilt by reverse engineering. See Objects' metadata handling.

Model: the file (.dbm) that describes a database: its objects and how they are drawn. It does not need a server to exist.

Model fix tool: the tool that repairs model files that cannot be loaded. See Model fix tool.

Model objects widget: the panel that lists all the objects of the model as a tree, grouped by type. See Model objects.

Model overview: the small map of the whole model (key F10) used to move the canvas in large models. See Model overview.

Model restoration: the recovery of the models that were open when pgModeler quit unexpectedly. See Model restoration.

Model validation: the check that finds broken references, duplicated names and other problems in a model, and fixes most of them. See Model validation.

Multi-scene model: a model whose objects are spread over several canvases (scenes), to keep each one small. See Multi-scene models.

New object overlay: the pop-up opened with the key N that creates objects, and relationships between selected tables, with the keyboard.

Object ID: the number that records the order in which an object was created, which defines the order of the objects in the generated SQL. The swap IDs tool changes it. See Swap objects IDs.

Object search: the panel that finds objects of a model by name, type and other attributes, and highlights them. See Object search.

Operations history: the list of the operations done over the objects, which supports undo (Ctrl + Z) and redo (Ctrl + Y). See Operations history.

Plug-in: a library loaded at startup that extends pgModeler with extra features. See Chapter 8.

Plus: the paid edition of pgModeler, which includes everything of Community and adds the tools that work with live servers and a set of plug-ins. See Community and Plus editions.

Protected object: an object that cannot be moved, changed or deleted until it is unprotected. See Protecting objects.

Quick actions: the menu opened by right-clicking an object, with the most common edits (rename, change schema or owner, set a tag, edit permissions...).

Relationship marker: the mark shown in a table, in place of the line, when its relationship is hidden or when the other table is in another scene. Double-click it to see the details.

Scene: one of the canvases of a multi-scene model.

Schema file and schema microlanguage: the templates, written in an internal microlanguage, from which pgModeler generates SQL (for each PostgreSQL version) and XML. See Appendix I.

Schema rectangle: the rectangle drawn around the tables of a schema on the canvas.

Source code preview: the panel (Alt + S) that shows the SQL or XML of the selected objects without exporting them. See Source code preview.

Split model: a plug-in file format (.sdbm) that saves each object in its own file, to make version control easier. See Split database model format.

SQL tool: the part of the Manage view where you run SQL commands, explore the database and handle data. A Plus feature. See SQL tool.

Stub object: an object that exists in the model as a reference but whose SQL is not generated (it has its code disabled). See Creating stub objects.

Swap IDs: see Object ID.

Tag: a color pattern that can be assigned to tables and views to find them at a glance. See Tags.

Textbox: an object used for reminders and documentation over the model. It is not part of the SQL. See Textboxes.

Trial: the evaluation license of Plus, with limits on the number of models, operations and object changes. See Community and Plus editions.

Z stack: the drawing order of the objects on the canvas: the greater the Z value, the closer to the top. See Managing objects stacking.

2. Relationship types

All of them are described in Relationships.

Copy relationship: creates a table with the columns of another one, as the LIKE clause of PostgreSQL does. The tables stay decoupled, and a column that already exists in the derivative table raises an error.

Dependency: the link, shown on the canvas, between a table and a view that uses it. It has no attributes or constraints.

FK relationship: a relationship drawn by pgModeler to represent a foreign key that was created by hand as a constraint of a table. Unlike the one-to-one and one-to-many relationships, it does not manage the columns and constraints. It is also what a relationship becomes when it is converted to free its columns.

Identifier relationship: a relationship in which the receiving table is a weak entity: its rows only exist while the referenced rows of the strong entity exist, and they are removed together with them.

Inheritance (or generalization): the table inheritance of PostgreSQL. The columns of the parent appear in the child but are not in the SQL of the child, because PostgreSQL handles them.

Many-to-many (n:n): links many rows of a table to many rows of another one through an intermediate table, created by pgModeler with the foreign keys of both.

One-to-many (1:n): a row of a table is linked to many rows of the other; the foreign key is created in the table of the "many" side.

One-to-one (1:1): a row of a table is linked to at most one row of the other; the foreign key is created together with a unique constraint.

Partitioning relationship: links a partitioned table to each one of its partitions.

Self-relationship: a relationship of a table with itself, created with Shift pressed when selecting the table. Used for hierarchies. See the FAQ.

3. PostgreSQL terms

Aggregate: a function that computes one value from a set of rows (sum, avg).

Cast: a conversion rule between two data types.

Check constraint: a constraint that requires a condition to be true for every row.

Collation: the rule used to compare and sort text.

Column: a named and typed field of a table, with attributes such as NOT NULL, a default value or an identity.

Constraint: a rule of integrity of a table: primary key, foreign key, unique, check, exclude and not null.

Default value: the value a column receives when an inserted row does not provide one.

Domain: a data type based on another one, with optional constraints.

Exclude constraint: a generalization of the unique constraint: it forbids that two rows are in conflict according to operators that you choose (for example, overlapping periods).

Extension: a package that adds objects (types, functions, operators) to a database, such as PostGIS.

Foreign data wrapper (FDW): the mechanism that lets PostgreSQL read data that lives in another server. A foreign server describes that server, a foreign table represents one of its tables and a user mapping links a local role to a user of the remote server.

Foreign key: a constraint that requires the values of some columns to exist in the primary key (or a unique key) of another table.

Function and procedure: routines stored in the database. A function returns a value and can be used in expressions; a procedure is called with CALL and can control transactions.

Generated column: a column whose value is computed from an expression over the other columns of the row.

Identity column: an auto-increment column defined with GENERATED ... AS IDENTITY, the SQL standard form. See also serial.

Index: a structure that speeds up the search for rows by the values of some columns or expressions.

Language (procedural): the language in which a function or procedure is written (sql, plpgsql, others added by extensions).

Materialized view: a view whose result is stored and has to be refreshed to be updated.

Not null: the constraint that forbids the null value in a column.

Operator, operator class and operator family: an operator is a symbol applied to values (+, =). Operator classes and families tell the indexes how to use the operators of a data type.

Owner: the role that owns an object and has all privileges over it.

Partitioning: the division of a large table into smaller physical tables (partitions) by a range, a list or a hash of values.

Policy: a row level security rule that defines which rows a role can see or change.

Primary key: the constraint that identifies each row of a table, formed by one or more columns that are unique and not null.

Privilege (or permission): the right of a role to do something with an object (read, change, execute...).

Referential action: what happens to the rows that reference a deleted or updated row, defined in the ON DELETE and ON UPDATE of a foreign key: NO ACTION, RESTRICT, CASCADE, SET NULL or SET DEFAULT.

Role: a user or a group of users of the PostgreSQL server.

Rule: a rewriting of the commands that touch a table or a view.

Schema: a namespace that groups tables, views, functions and other objects of a database. A new database already has the schema public.

Sequence: an object that generates numbers in order, used for auto-increment columns.

Serial: a convenience type that creates a sequence and uses it as the default value of the column. See Creating a sequence from a serial type.

System catalogs: the tables and views (pg_catalog.* and information_schema.*) where PostgreSQL stores the description of its objects. The import reads them.

Table: the object that stores rows, organized in columns.

Tablespace: a location in the file system where the files of the database are stored.

Transform: the rule that converts a data type to and from a procedural language.

Trigger: a function that runs automatically when a table is changed. An event trigger runs when a command of the database (for example CREATE TABLE) is executed.

Unique key: the constraint that forbids repeated values in a column or in a set of columns.

User-defined type: a data type created by the user: base, composite, enumeration or range.

View: a stored query that is used as if it were a table.

4. Data modeling terms

Attribute: a property of an entity. In a table, it is a column.

Cardinality (in the entity-relationship model): how many items of one entity can be linked to items of another: one-to-one, one-to-many or many-to-many. pgModeler shows it with the crow's foot notation or the classical notation (see Relationships).

Composite key: a key formed by more than one column.

Conceptual, logical and physical models: the three levels of detail of a data model. The conceptual one describes the entities and their relationships without technical details; the logical one adds attributes, keys and the rules of the relational model; the physical one is specific to a database system and has types, indexes and storage. pgModeler works at the physical level, and the compact view helps to talk about the other ones.

Crow's foot notation: a way of drawing relationships in which the "many" side ends in a fork (the foot of a crow).

Data dictionary: the document that describes the tables and columns of a database. pgModeler exports it in HTML or Markdown.

DDL (Data Definition Language): the SQL commands that create and change the structure of a database (CREATE, ALTER, DROP).

Denormalization: the deliberate repetition of data, to avoid costly joins or to speed up queries, at the price of a bigger effort to keep the copies consistent.

Entity: something about which the model stores data. In a relational database, it is usually a table.

Entity-relationship diagram (ERD): the drawing of the entities of a model and their relationships.

Forward engineering: generating the database (or its SQL) from the model. See Export.

Junction table (or associative table): the intermediate table that implements a many-to-many relationship, with the keys of both tables.

Lookup table: a small table with a fixed list of values (statuses, categories) that other tables reference with a foreign key.

Natural key and surrogate key: a natural key is formed by data that already identify the row in the real world (an e-mail); a surrogate key is an artificial value created only to identify the row (an identity column).

Normalization: the organization of the tables to avoid repeated data and the problems that come with it when data is changed. It is described by the normal forms (1NF, 2NF, 3NF and others).

Referential integrity: the guarantee that every foreign key refers to a row that exists, which the database enforces with foreign key constraints.

Reverse engineering: building the model from an existing database. See Import.

Strong and weak entities: a strong entity exists by itself; a weak entity depends on another one to exist and is identified with its key. pgModeler represents the latter with the identifier relationship.