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 precise (e.g., string(50)) and precisionless (e.g., string) data types and are designed to make using both as flexible as possible.
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
Creating data types without specifying the precision offers added flexibility by allowing their precise variants to be used without being explicitly defined. Similarly, omitting the precision can be used as a catch-all to preserve data types as they appear during import or export.
For example:
In Logical - creating a precisionless data type as number will allow any of the following to be used: number, number(7,0), number(38,2)
In import or export - a mapping of string to varchar will permit: string to varchar and string(50) to varchar(50)
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).
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.
Remember that unless expressly specified, precision will be preserved. (See the above section for more on "Working with data type precision.”)
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.
In the following example, note how specific precisions can be discarded while preserving the rest (i.e., physical varchar(16777216) will convert to precisionless string, while all other varchars will keep their precision as strings). Also note that leaving a physical data type unmapped (e.g., blank) will result in it being imported as written (e.g., binary(8388608))
Users can specify the mappings for each physical database type by clicking the database name dropdown ("Snowflake" appears by default.)
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 not yet available but will be part of the next release.
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. (Selective export coming soon.)
Next, select the physical database type.
Finally, 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).
Complete the process by clicking the "Save and export to Snowflake" 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.