Working Across a Mixed Database Estate: Architect's Guide

Three Types of Schema in a Mixed Database Estate

Enterprise architectures rarely operate on a single database engine. According to Redgate's State of the Database Landscape survey, 79% of organizations - up from 62% in 2020 - manage two or more database platforms across their production environments[1]. As polyglot persistence expands across cloud and on-premises infrastructure, database architects face mounting complexity when attempting to maintain unified governance, lineage, and structural consistency. Managing this sprawl requires a disciplined adherence to the classic three-schema architecture originally formalized by ANSI/SPARC, which separates user perspectives, conceptual business rules, and underlying physical storage[2].

Conceptual, Logical, and Physical Schema Layers

Structuring schemas across three distinct abstraction tiers enables architects to decouple business logic from the idiosyncratic storage primitives of individual database engines:

DbSchema lets database architects design engine-independent conceptual and logical models, then map them directly to concrete physical schemas across relational and NoSQL engines. Unlike single-engine modeling packages, licences that cover every supported engine avoid per-connector add-ons. DbSchema is licensed that way: its pricing page lists All databases against the free Community Edition as well as against Pro and Architect, so adding an engine to the estate is never a new purchase - engine-independent conceptual and logical design is the one workflow the Architect edition adds on top. The wider question of how to pick a database design tool is worth settling before the estate grows, because one logical model has to convert into target DDL for PostgreSQL, MySQL, Oracle, Snowflake, or MongoDB while enforcing unified data types throughout the enterprise.

Four Types of Database in Modern Enterprise Architecture

Modern application architectures select data engines based on access patterns, read/write throughput, and query characteristics rather than forcing every workload into a standard relational model. Redgate's survey puts the figure at 48% for practitioners directly engaged in database management who work across multiple distinct database platforms[1]. Balancing relational rigor with distributed NoSQL speed requires architects to understand the four primary operational database types operating across modern estates.

Evaluating the Core Database Paradigms

Each database paradigm solves specific transactional and analytical challenges across the enterprise stack:

The practical requirement for a mixed estate is a single client that reaches every engine: DbSchema covers 100+ SQL and NoSQL databases and downloads the JDBC driver for you when a connection is opened, so there is no manual classpath configuration or JAR file installation. The JDBC Drivers Manager is still there when a site has to pin or swap a specific driver jar. A unified connectivity layer of this kind allows architects to inspect, model, and query a legacy IBM Db2 instance, a DynamoDB table, and cloud warehouses from a single visual environment.

Four Types of Documentation for Database Estates

Comprehensive documentation is the primary defense against technical debt and architectural drift in multi-engine environments. When services scale across dozens of independent datastores, unrecorded schema changes and implicit relationships quickly create operational blind spots. Establishing standard documentation practices across the lifecycle requires four complementary artifact types:

• Visual ER Diagrams: Interactive visual diagrams representing entities, primary keys, foreign keys, and virtual relationships across tables and collections.
• Data Dictionaries: Exhaustive metadata catalogs that define every table, column, data type, default value, indexing strategy, and field-level business description.
• API Schemas and Contracts: Serialization specifications (such as JSON Schema or OpenAPI definitions) documenting data exchange payloads between application tiers.
• Deployment and Migration Scripts: Version-controlled DDL migration files capturing chronological schema transitions, rollback procedures, and change histories.

Generating Interactive Vector Documentation

DbSchema generates interactive HTML5 database documentation directly from the design model file. The export carries a vector diagram in which collection and field comments are readable as mouse-over tooltips. Developers and analysts can pan, zoom, and search complex schema layouts in their web browser without connecting to the underlying database or installing local desktop software.

Two Main Types of Data Dictionaries: Active vs. Passive

Data dictionaries maintain the metadata defining how information is structured, stored, and secured within an organization. In database management practice, dictionaries fall into two fundamental operational categories: active data dictionaries, which the DBMS maintains automatically so any change to the database structure is reflected without manual intervention, and passive data dictionaries, which are held separately and updated by hand, and so can drift out of sync with the database[4]. Understanding the operational boundary between these two types helps architects construct reliable metadata governance pipelines.

Bridging Active Catalogs with Offline Model Versioning

DbSchema bridges the gap between active engine catalogs and passive architectural repositories by saving the complete schema design to a local XML model file. Architects can then review and modify schemas offline, track changes in standard Git repositories, and compare the design file against a live active dictionary to generate precise deployment scripts. Using a dedicated data dictionary workflow preserves architectural intent across releases.

Connecting directly to live database catalogs has to satisfy whatever the security team already mandates. In DbSchema the Connection Mode list covers the ordinary cases per engine - standard login and password, Use SSL, and an Edit the JDBC URL Manually mode for a URL a cloud console hands you - while the Advanced tab adds an SSL and parameters editor, a free-form driver-properties field and an SSH tunnel section. MongoDB carries its own mode list, including TLS verified against a CA PEM file or a Java TrustStore, SCRAM-SHA-256, X.509 and AWS IAM. Kerberos has no dedicated screen of its own: it is set up by passing driver properties such as oracle.net.authentication_services=(KERBEROS5) through that same Advanced tab. In practice most connections are secured with database credentials plus SSH key tunnels with SSL/TLS encryption.

Four Types of Data Models for Database Architects

Data modeling techniques have evolved alongside computing hardware and query languages over the past six decades. Architects working across legacy enterprise platforms, relational warehouses, and modern schemaless document stores must navigate four distinct data modeling paradigms:

1. Hierarchical Model: Organizes data into an inverted tree structure with strict one-to-many parent-child relationships, pioneered by early mainframes such as IBM IMS.
2. Network Model: Extends the hierarchical model by allowing child records to link to multiple parent records, creating graph-like many-to-many relationships governed by the CODASYL standard.
3. Relational Model: Organizes data into two-dimensional tables (relations) of rows and columns, establishing relationships through foreign keys and enforcing set-theoretic operations via SQL.
4. Entity-Relationship (ER) Model: Abstract conceptual and logical representations mapping real-world entities, attributes, and cardinality constraints before physical database implementation.

Navigating Schemaless Data via Virtual Relations

In NoSQL document databases such as MongoDB, relationships between collections are not enforced by database-level foreign key constraints. To simplify multi-collection analysis, DbSchema lets architects create virtual relations (virtual foreign keys) by dragging fields between collections on the diagram canvas.

For schemaless databases, the Relational Data Editor opens several collections side by side over virtual relations; selecting a record in the parent instantly refilters every child pane to the documents whose field values match, cascading as many levels deep as needed without hand-writing SQL. This visual exploration helps architects validate data structures and foreign references before deploying schema changes with a database migration tool.

Two Types of Dictionaries for Technical and Business Teams

Effective enterprise governance requires aligning low-level physical implementations with high-level corporate terminology. Organizations manage this alignment by deploying two distinct types of dictionaries: technical data dictionaries, which document the technical metadata of each table and field including data types and relationships and are owned by technical teams, and business glossaries, which define key business terms in plain language so every team interprets the data the same way[5].

Offline Design Model State Persistence

A design model file can store the complete technical dictionary state as human-readable, Git-friendly XML. In addition to table and column comments, the XML model file persists UI diagram layouts, custom data types, virtual foreign keys, visual query builder structures, and SQL editor configurations. This file-based architecture enables database architects to review, edit, and document the complete schema structure offline - on a train, or inside a network that never reaches production - without maintaining an active network connection to production databases.

Two Main Types of Data: Structured and Unstructured

Enterprise estates balance two fundamental data classifications: structured data and unstructured data (including semi-structured document payloads). Structured data adheres to strict tabular schemas with predefined column types, enforceable constraints, and transactional ACID guarantees, as seen in relational databases like PostgreSQL and MySQL. Unstructured and semi-structured data stores, such as MongoDB or Couchbase, accommodate dynamic payloads where document structures vary between records and schemas evolve continuously; Redgate's survey of database professionals found MongoDB to be the most popular platform for unstructured data[1].

Schema Inference and Validation in Schemaless Stores

Visualizing schemaless databases requires specialized reverse-engineering techniques. MongoDB is typically visualized by introspecting a configurable sample of documents per collection and inferring field names, BSON types, nested objects, and arrays. This introspection produces a structured visual approximation of the document layout without scanning multi-gigabyte collections entirely.

A MongoDB diagram in DbSchema is a derivation, not an enforced contract. DbSchema samples documents and infers the field list; the Edit Collection dialog flags such a collection Inferred (no validation), and MongoDB itself still accepts a document that contradicts the sampled shape. Structure becomes enforced only once a JSON Schema validator is authored in the Collection Validator and applied to the database - DbSchema does not seed that editor from the fields it has already inferred, so the rule is written by hand. Virtual relations created between collections are DbSchema-side virtual foreign keys saved in the model file: they drive the diagram, the visual query builder and the cascading data editor, and the MongoDB server neither knows about them nor enforces them.

Manage your heterogeneous database estate from a single visual environment. Download DbSchema, open a design project against your existing databases, and work across every engine you run - every edition, the free Community Edition included, covers every supported database.

Frequently asked questions

What are the three types of schema in a database architecture?

The three types of schema are conceptual, logical, and physical. A conceptual schema defines high-level entities, a logical schema details attributes, and a physical schema maps the design to a specific engine like PostgreSQL or MySQL.

What are the 4 types of database commonly used in enterprises?

The four main types of database are relational, document, key-value, and graph. Redgate's State of the Database Landscape report puts the share of organizations running two or more database platforms at 79%, so most teams need one modeling tool that bridges relational and NoSQL systems.

What are the 4 types of documentation for data environments?

Database documentation typically spans interactive ER diagrams, data dictionaries, API references, and deployment scripts. Interactive HTML5 exports can show field comments as hover tooltips over vector diagrams, so readers need no database access.

What are the two main types of data dictionaries?

Data dictionaries are either active (automatically updated by the DBMS) or passive (maintained separately). DbSchema bridges both by comparing a local model file against the live database to detect schema drift.

What are the 4 types of data models?

Data models are traditionally categorized into relational, hierarchical, network, and entity-relationship models. Modern architects use these frameworks to structure data before deploying physical schemas to live environments.

Which are the two main types of data?

Data is broadly categorized into structured data (rigid rows and columns in relational databases) and unstructured or semi-structured data (flexible JSON documents in NoSQL systems like MongoDB).

How does DbSchema connect to a mixed database estate?

DbSchema covers 100+ SQL and NoSQL databases and downloads the JDBC driver for you when you open a connection. Connections run over standard login and password, SSL/TLS or an SSH tunnel, with per-engine options such as SCRAM-SHA-256, X.509 and AWS IAM on MongoDB; Kerberos is set up by passing driver properties on the connection dialog's Advanced tab.

Sources

1. red-gate.com
2. docs.teradata.com
3. en.wikipedia.org
4. geeksforgeeks.org
5. atlan.com