Standalone logical modeling allows users to design independently of a specific database type and establish a foundational business model that can be shared or converted. Logical projects offer full control over the data type mapping, normalization, and naming convention settings for easy import and export from physical.

Working with Logical projects

All projects in SqlDBM, including Logical, are designed to offer a similar and consistent look and feel throughout. However, due to their database-agnostic nature, Logical projects contain some peculiarities that must be considered.

No DDL or database syntax - As there is no SQL in logical projects, syntax-dependent database objects (e.g., views) are not possible in Logical. Revision compare is likewise performed at the property level instead of comparing DDL as text.
Nomenclature - to ensure a consistent experience across projects, Logical objects maintain their analogous physical names in menus and properties (e.g., entities are still referred to as tables and attributes as columns.)
Logical and physical names - just as physical projects allow users to maintain logical names on physical objects, Logical projects permit users to maintain the physical name as a secondary object property. Logical and physical names are swapped when converting (i.e., importing or exporting) to/from Logical.

Starting a logical project

Logical projects can be created the same way as any project in SqlDBM by selecting "New project" from the Dashboard or the main menu of an existing project.

The following options are available when starting a new Logical project:

Start a new (empty) project
Start from a sample
Convert from an existing physical project
Import from Excel - (coming soon)

Working with data types

Logical projects allow users to freely create and manage custom-defined data types. The capabilities of data type mappings in SqlDBM enable users to create, merge, and maintain two-way physical mappings, even offering domain-like flexibility. The "Data types mapping" feature in Logical projects offers both logical-to-physical and physical-to-logical mappings. "Data types mapping" can be found in the main SqlDBM menu of logical projects.

Logical, import, and export mappings can be found here.

Working with data type precision

Logical projects support various precision settings to give users full flexibility in maintaining data type precision with minimal effort. These include:

precise - specify the exact precision for this data type and disallow all others (e.g., amount(38,2) allows amount columns to only be of length 38 and 2 decimals).
any precision - use the asterisk (*) to allow any precision to be specified for a data type ( e.g., string(*) allows strings of any specified length )
precisionless - defines a data type that does not admit precision (e.g., boolean does not allow a precision specification)

Precise data types

Specifying a precision for a data type means that only this particular precision is allowed as a valid data type in the Logical project. Likewise, for import from Physical, specifying data type precision allows for exact mapping of certain data types to multiple Logical correlates (e.g., number(38,2) in Physical can be mapped to currency in Logical, while number(5,0) can be mapped to Postal Code).

In the following example, only time(9) is specified in the Logical mapping, and therefore, time (without precision) appears as an invalid data type.

Precisionless data types

Precisionless data types do not admit precision specification (e.g., date, boolean, integer).

In the following example, the precisionless date data type results in a column of type date(100) being flagged as erroneous.

Any precision data types

To allow a data type to contain any specified precision, use the asterisk symbol in the data type mapping.

In the following example, string data types of any length are allowed because strings were defined as permitting any precision (i.e., string(*)).

Logical data types

Logical projects offer complete flexibility regarding the data types (and precisions) that can be used. This mapping can be accessed and maintained from the main SqlDBM menu by clicking "Data types mapping."

Here, users can create, rename, remove, or merge Logical data types using the left column titled "Logical project." (The right column is used for export mapping and is discussed later in this article.)

The following actions can be performed from this screen:

Create - write in a new data type in the bottom row to add it as a valid data type to the Logical project
Rename - rename an existing data type and save the mapping to apply the changes to related columns in the project
- If precision is specified, it will also be applied; otherwise, it will be preserved.
Merge - rename any data type to any that exists, and the two will be merged, renaming any existing columns.
Delete - press the cross (x) icon to the right of a data type row to delete it. Any columns using this data type will be flagged as invalid in Errors and Warnings (see the related article at the end of this guide).

Importing and exporting between logical and physical projects

Logical projects offer import (from physical) and export (to physical) options along with robust transformation settings for normalization and data type conversion. The following sections outline the options available when performing a conversion.

Data type conversion

Recall that precision settings offer great flexibility in transforming, translating, or truncating data types during the conversion process.

During the import and export process, data type precision (i.e., any, precise, precisionless) options will dictate how data type transformations will take place. For example:

Drop precision: map any data type to a precisionless.
Maintain precision: map a precise or any precision data type to any precision.
Change precision: map a precisionless or any precision data type to precise.
Null case: precisionless to any will result in an invalid (empty precision) data type.

Export mapping

Export mapping allows users to define conversion rules for data types when exporting from Logical to Physical and can be maintained from the "Logical data types" tab of "Data types mapping."

Once Logical data types have been defined in the left column, their physical mappings can be specified on the right. By clicking on the database name dropdown ("Snowflake" appears by default), users can specify the equivalent physical mapping for logical data types.

Import mapping

Use the "Physical mapping" tab on the "Data types mapping" to control how Physical data types are mapped to their Logical equivalents on import.

When importing, the data types mapping rules from the logical project will be applied first, then non-matching data types will be suggested to convert as is (e.g., object to object). Data types that exist in the physical project will be displayed first as non editable (1). The rest are displayed for informational purposes.

Typically, physical projects will contain data types of varying precision (i.e., varchar(1), varchar(10), varchar(100), etc). To simplify the import process, multiple precisions will be grouped into one data type with an asterisk (any) (2).

Recall that multiple definitions and translation options can be specified for the same data type using the precision options (3). The example above is using the below mapping to allow different treatment of varchar data types.

Users can specify the mappings for each physical database type by clicking the database name dropdown ("Snowflake" appears by default.) (4)

Defining subtypes and supertypes through discriminators

Overview of subtypes and supertypes

In logical modeling, subtypes and supertypes are used to represent hierarchical relationships between entities.

Supertype: Contains common attributes that apply to all subtypes.
Subtype: Inherits attributes from the supertype but also includes unique attributes specific to that subtype.

Note that subtypes will inherit the supertype identifiers when they are defined. This behavior is similar to creating identifying foreign key relationships.

Using the discriminator

The discriminator symbol below an entity designates it as a supertype. Subtypes can be added or created by dragging a relationship from the discriminator to an existing table. The discriminator properties can also configure extended settings like differentiating columns, completeness, exclusivity, and transformation options.

An overview of discriminator settings and functionality:

Discriminator properties

The following properties can be configured on the discriminator.

Name - a descriptive name of the discriminator
Differentiator columns - On rollup transformations (denormalization), these columns are used to identify the subtype records. For this reason, differentiator columns do not appear on the diagram and are only added to the object on export. If you wish to avoid this behavior, add differentiator columns to the supertype directly.
Complete/Incomplete - changes the visual depiction of the discriminator (single line or double line) but has no bearing on the associated objects
Inclusive/Exclusive - changes the visual depiction of the discriminator (empty circle or circle with 'x') but has no bearing on the associated objects
Physical transformation - transformation rules can be set independently for each supported physical project type. These rules are applied on export (see the section below for more info). These include:
- Rollup to supertype - only the supertype entity is created, inheriting subtype columns as:
  - Inherit all attributes - passes all subtype columns to the supertype
  - Inherit only primary attributes - only the identifying supertype columns are passed to the supertype
Rolldown to subtype - only the subtype entities are created, inheriting supertype columns as:
- Inherit all attributes - passes all supertype columns to the subtype
- Inherit only primary attributes - only the identifying supertype columns are passed to the supertype

Import and Export

Comprehensive control over data types and mappings makes import and export to Logical projects a simple process with no surprises. Object filtering and selective conversion are supported.

Note that Logical Visibility options also play a role in Logical projects, however, only during import and export. Tables and columns marked as "Logical only" will not be exported to physical projects, while "Physical only" objects from Physical projects will not be included in import.

As there are no Physical view modes in Logical projects, there is no "Physical only" visibility option here.

For more info on Logical Visibility, please see the article "Logical Modeling" at the end of this page.

Export to physical

To export from Logical and create a new Physical project, press the "Export to physical" button on the left-hand project menu. You have the option of exporting to an existing project or creating a new one.

Create a new project, selecting the physical database type or search and click on an existing project to start the export wizard.

Next, review the existing Export Mapping for the chosen database and make any necessary adjustments (the changes will be saved and applied to the Export Mapping).

Now review the changes for the following objects and uncheck those you wish to exclude from the export.

SQL Objects - Logical entities that will be converted to physical tables
Project-level objects - templates, conventions, and other settings
Diagrams - diagrams to be exported to target projects

Complete the process by clicking the "Save and export to <project type>" button.

A link to the newly created physical project will be shown at the top of the screen.

Import from physical

To import from a physical project, press the "Import to physical" button on the left-hand project menu. (Selective export coming soon, existing Logical project contents will be overridden.)

Next, select (or search for) the name of the physical project being imported.

Finally, review the existing Import Mapping for the chosen database and make any necessary adjustments (the changes will be saved and applied to the Import Mapping). The list of data types from the chosen project will be shown at the top (where the existing physical data types will be locked for editing). The rest of the Import Mapping is shown for context and can be used to add mappings for specific precisions. To keep the list manageable, precision is removed from the incoming data types but can be specified in the editable area.

Complete the process by clicking the "Save and import" button.