Logical Database Design – Entities, Keys, Normalization, and Physical Mapping | DbSchema

Logical database design is the stage where a database becomes clear enough to build. It sits between high-level business ideas and physical SQL implementation. Here you decide entities, attributes, identifiers, relationships, and normalization rules before you worry about engine-specific syntax.

For modern teams, this step matters more than ever: the schema must be readable by developers, analysts, and product stakeholders, then converted cleanly into MySQL, PostgreSQL, SQL Server, Oracle, or another engine. DbSchema is especially useful because the same model can move from logical design to diagram review, validation, and schema documentation.

Table of Contents

1. Logical vs conceptual vs physical design
2. How to start a logical design in DbSchema
3. Entity-to-table mapping
4. Defining identifiers and relationships
5. Normalization with 1NF, 2NF, and 3NF
6. Naming and conversion dictionaries
7. Validate and collaborate on the model
8. Deploy into physical design
9. FAQ
10. Conclusion

Logical vs conceptual vs physical design

One reason logical design is often misunderstood is that teams mix it with conceptual or physical design. They are related, but not identical.

Logical design is where you answer questions such as:

• what entities does the domain need?
• which attribute identifies each entity?
• which relationships are mandatory?
• where should many-to-many relationships become junction tables?
• how far should the model be normalized before implementation?

If you need a practical design-first walkthrough, pair this article with How to Design a Relational Database Schema and Entity Relationship Diagram.

How to start a logical design in DbSchema

You can download DbSchema and create a logical model before any database exists.

1. Open DbSchema.
2. In the Welcome Pane, choose Design from Scratch.
3. Select Logical Design.
4. Add entities, attributes, and relationships to the diagram.

This workflow is useful when the business model is still evolving because you can review the structure first, then decide later whether the target engine is MySQL, PostgreSQL, SQL Server, or something else.

Entity-to-table mapping

Logical design usually starts by translating real-world objects into entities.

Example: country and population model

Suppose we model geographic data:

• Countries (CountryID, Name, Continent, PhonePrefix)
• Cities (CityID, Name, CountryID, Population)
• Residents (ResidentID, Name, CityID, BirthYear)

In logical design:

• each entity becomes a future table
• each attribute becomes a future column
• each relationship becomes a future foreign key

The main benefit of this stage is clarity. It is much easier to spot duplication, missing lookup tables, or bad naming in a diagram than after hundreds of lines of DDL already exist.

Defining identifiers and relationships

Primary key (logical identifier)

Every entity needs an identifier that uniquely distinguishes one record from another.

Examples

• CountryID identifies each country
• CityID identifies each city
• ResidentID identifies each resident

When the model becomes physical, these identifiers become primary keys.

Logical relationships

Relationships describe how entities connect. In the logical model, they are conceptual. In the physical model, they become enforced foreign keys.

Cities.CountryID → Countries.CountryID
Residents.CityID → Cities.CityID

Relationship decisions you should make early

Identifying vs non-identifying

• Identifying relationship: the parent key becomes part of the child identifier. The child depends on the parent for identity.
• Non-identifying relationship: the child references the parent, but the parent key is not part of the child identifier.

One-to-many and many-to-many

• One-to-many: one country can have many cities.
• Many-to-many: many countries can speak many languages, so you need a junction entity such as CountryLanguages.

Mandatory vs optional

• Mandatory: the child must reference a parent.
• Optional: the child may exist without the parent reference.

Cardinality

• 1:1 — one record matches one record
• 1:n — one parent matches many children
• m:n — many records on both sides, implemented through a junction entity

Virtual foreign keys in DbSchema

DbSchema also supports virtual foreign keys. These are relationships defined in the model even when the database itself does not enforce them. They are useful for:

• documenting views or imported data
• connecting legacy tables that lack real constraints
• helping the Query Builder and Relational Data Editor understand how tables relate

Normalization with 1NF, 2NF, and 3NF

Normalization organizes the model so data stays consistent and duplication stays low. Logical design is the perfect place to do this because it is easier to change the model before the physical schema is deployed.

We will use a simple online-store example.

First Normal Form (1NF)

Goal: every attribute stores one atomic value.

Not in 1NF

Problems:

• Products stores a list instead of one value
• PhoneNumbers stores repeated values inside one cell

In 1NF

What changed in the model? We removed repeating groups so each attribute can be filtered, validated, and indexed independently.

Second Normal Form (2NF)

Goal: remove partial dependencies when a composite key exists.

Assume (OrderID, Product) is the key.

Not in 2NF

CustomerName and PhoneNumber depend only on OrderID, not on the entire key.

In 2NF

Orders

OrderDetails

What changed in the model? We separated order-level data from line-item data so each non-key attribute depends on the full key of its entity.

Third Normal Form (3NF)

Goal: remove transitive dependencies.

Not in 3NF

CustomerName and PhoneNumber depend on CustomerID, not directly on OrderID.

In 3NF

Orders

Customers

What changed in the model? We moved customer details into their own entity, which means customer data is updated in one place instead of many.

Normal forms summary

If you want the full design workflow around normalization, the related guide How to Design a Relational Database Schema expands the same ideas with a complete example.

Naming and conversion dictionaries

Naming dictionary

The naming dictionary controls how logical names become physical names.

Examples:

• Customer Name → customer_name
• Phone Prefix → phone_prefix
• CountryID → country_id

This helps keep the generated schema consistent across the whole project.

Conversion dictionary

The conversion dictionary maps logical data types to physical ones for each target database.

Examples:

• logical Text → MySQL VARCHAR(255)
• logical Text → SQL Server NVARCHAR(255)
• logical Number → PostgreSQL INTEGER

This is one reason DbSchema works well in multi-database teams: the logical model stays stable while the physical implementation can change per engine.

Validate and collaborate on the model

Once the model looks right, review it as if another team will inherit it tomorrow.

Schema and table validation

DbSchema can check for:

• missing primary keys
• problematic relationships
• redundant structures
• cyclic dependencies
• inconsistent naming

Comments and tags

Comments and tags make the model easier to reuse in documentation and team reviews.

• annotate tables and columns with purpose
• mark important entities with tags
• explain abbreviations or business rules

Find cyclic dependencies

Circular dependencies often signal a design problem. DbSchema can highlight them visually, which is much faster than discovering them only during migration or test setup.

Git integration for teamwork

Logical models are not just for one person on one laptop. DbSchema can store the design in a model file and help teams review it with Git workflows before changes reach production.

Deploy into physical design

When the logical model is ready:

1. click Convert Model
2. choose Generate Physical Model
3. select the target DBMS
4. review the generated tables, columns, and constraints

DbSchema then maps:

• entities → tables
• attributes → columns
• identifiers → primary keys
• relations → foreign keys

From there you can continue with connect to database, schema synchronization, and published documentation.

FAQ

What is logical database design?

Logical database design defines entities, attributes, identifiers, relationships, and normalization rules without locking the schema to one database engine.

What is the difference between logical and physical database design?

Logical design is database-neutral and focuses on structure. Physical design adds data types, indexes, constraints, and engine-specific implementation details.

Why is normalization part of logical design?

Because it is easier to remove duplication and clarify relationships before the schema becomes a deployed database with live data.

Do I need logical design if I already know SQL?

Yes. SQL helps you implement the schema, but logical design helps you decide whether the structure is correct before implementation.

Can DbSchema create a logical model without a live database?

Yes. That is one of its biggest advantages: you can design offline, review the model, then generate or synchronize the physical schema later.

What is the best tool for logical database design?

If you want design-first modeling, multiple diagrams, validation, Git workflow, and later physical conversion in one product, DbSchema is a strong choice.

Conclusion

Logical database design is the stage where database quality is decided. If the entities, identifiers, cardinality, and normalization are right here, the physical schema becomes much easier to implement and maintain.

DbSchema is especially effective because it keeps the logical model useful after the workshop is over: the same design can be validated, versioned, converted, synchronized, and documented.