Logical Database Design: Keys and Normalization

You called the meeting to agree on the entities, and forty minutes in it is about whether a column should be VARCHAR or TEXT. That question belongs to a later stage. Logical design is where you settle the structure itself, and a model at that stage names no engine anywhere in it.

Get the structure right and the physical schema is a translation of it. Get it wrong and no index repairs the result, because the structure is what is wrong.

Logical, conceptual and physical design

Three levels of model sit between a business conversation and a running database, and confusing them is how a meeting about entities becomes a meeting about data types.

The boundary that decides the most is the second one. Which entities exist, what identifies each one, which relationships are mandatory, where a many-to-many becomes a junction entity, and how far to normalize: those are the logical questions. VARCHAR against TEXT, which columns get an index, partitioning, storage parameters and collation are the physical ones. A logical model is finished when every question in the first group has an answer and none in the second one does.

For the same ground covered from requirements through to SQL, read How to Design a Relational Database Schema and Entity Relationship Diagram; the tools for this stage are compared in Best Logical Database Design Tools.

Entity-to-table mapping

A first pass over a geographic domain gives three entities and their attributes:

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

Each entity becomes a table, each attribute a column, and each relation a foreign key. The mapping itself is mechanical, and the work is in deciding what you refuse to map.

The last row is the interesting one. A country has more than one phone prefix, so PhonePrefix is not an attribute of Countries at all: it is a missing entity, and the first draft above hides it inside a column. A repeated group of columns such as phone1 and phone2 is the same signal wearing a different shape, and so is an attribute that describes something other than the identifier of the entity it sits on. Spotting all three on a diagram takes minutes. Spotting them after the DDL exists takes a migration.

The relationship decisions to make early

Every relation carries four decisions, and each one turns into something the database enforces.

Identifying and non-identifying relations

In an identifying relation the parent's key becomes part of the child's identifier, so the child cannot exist on its own. A phone prefix means nothing without its country, which is why CountryID sits inside the key of PhonePrefixes.

In a non-identifying relation the child references the parent and keeps its own identity: a City keeps CityID and merely points at a Country. Choose identifying only where the child genuinely has no meaning alone, because the parent key then propagates into the key of every entity below it.

Cardinality

Mandatory and optional relations

Mandatory means the child must reference a parent, and the column becomes NOT NULL. Optional means it may exist without one, and every query that joins it needs an outer join. Decide this per relation: a model in which optional is the default is a model in which no join result can be relied on.

Virtual foreign keys when the database enforces nothing

Some relations are real in the data and absent from the database: views, imported datasets, tables whose constraints were never created. In DbSchema you draw one by dragging a column onto the related column in the diagram and choosing a virtual foreign key when it asks. The line is saved in the model file, and nothing is sent to the database, so the relation is documented without a constraint being created. The Query Builder and the Relational Data Editor, both in the Pro edition, then join across that line as if the engine had declared it, which is what gives imported and legacy tables a readable join path.

Normalization in one pass

Normalization belongs to logical design because a model is cheap to change and a deployed schema holding data is not. Three forms cover almost every practical case, and one record shows all three.

1NF puts one value in each attribute

An order arrives as a single record, and one of its fields holds a list rather than a value:

OrderID       101
CustomerName  Alice Smith
PhoneNumber   555-1234
Products      Shoes, T-shirt

One value per attribute splits that field across rows:

Every attribute can now be filtered, validated and indexed on its own, which no value inside a comma-separated list can be.

2NF removes partial dependencies

The key of the split table is OrderID and Product together, while the customer's name and phone number depend on OrderID alone. Putting the key columns first makes that partial dependency easy to see:

Splitting the order-level facts from the line-level ones puts each at its own grain. Orders keeps one row per order:

OrderDetails keeps one row per product on that order:

3NF removes transitive dependencies

Orders now carries a customer identifier, and the name and the phone number depend on that identifier rather than on the order:

Moving the customer's details into their own entity means a phone number is updated in one row. Orders keeps the identifier and nothing else about the customer:

Customers keeps the details, once per customer:

Each form removes a different defect, and each defect shows up as a different complaint from whoever maintains the schema.

Stop at 3NF unless a measured query says otherwise. Going further splits tables that nothing complains about, and every split adds a join to the queries that were already correct. For the cases beyond the third form, see Database Normalization.

Naming and type conversion

A logical model uses business names and abstract types, so two mappings stand between it and something an engine accepts. The naming dictionary decides how a logical name is written physically, turning First Name into first_name or FIRST_NAME by a rule rather than by hand; open it from the DbSchema "Convert Model" menu. The translation happens during the conversion, so the logical model keeps the business names a reviewer reads, and the physical model gets the identifiers the engine sees.

The conversion dictionary maps each logical type to a physical one per target database: the logical type Text converts to VARCHAR(255) for MySQL and NVARCHAR(255) for SQL Server, and you can edit the mapping for any database to match your own conventions. Keeping both as rules is what lets one logical model target several engines without the models drifting apart. Doing it by hand instead means a rename in the logical model is a rename you then repeat once per engine, and the second engine is where the two schemas quietly stop matching.

From logical model to physical schema

With the decisions above made, the conversion is mechanical.

In DbSchema the conversion runs from "Convert Model → Generate Physical Design": it applies the two dictionaries and produces an editable physical model for the database you pick. That step writes the physical model into the same .dbs file and sends nothing to a database. The schema reaches the database only when you connect and apply it.

What the conversion cannot decide for you is indexes, storage and collation. Those are physical choices, and they are made against the queries the application actually runs rather than inherited from the logical model.

Entities, identifiers, cardinality, optionality and normal form are the five decisions that cost the most to change later, and every one of them is made before an engine is chosen. Download DbSchema at https://dbschema.com/download.html, choose Design from Scratch on the welcome screen and then Logical Design, and right-click the canvas to draw the first entity. Database-independent logical design is in the Architect edition, which also carries everything the Pro and Community editions have. The diagram side of the work is described in the database diagrams tool.

FAQ

What is the difference between logical and physical database design?

Logical design names entities, attributes, identifiers and relations, and stays engine-neutral. Physical design turns each of those into a table, a typed column, a primary key or a foreign key constraint for one database, and adds the indexes and storage settings that only make sense once the engine is known.

Can one logical model target more than one database?

One model can target several, because the naming and conversion dictionaries hold the rules rather than the model holding engine syntax. DbSchema retargets an existing model from "Model → Model Properties", showing a preview of how every data type converts before you confirm. Triggers, functions and stored procedures are written per engine, since their languages differ.

Do I still need logical design if I already know SQL?

SQL implements a structure, and logical design is where you decide whether that structure is the right one. Typing DDL quickly does not answer whether a phone prefix is an attribute or an entity.

Which DbSchema edition includes logical design?

Logical and conceptual design are in the Architect edition. Connecting, reverse-engineering, interactive diagrams and the SQL editor are in the free Community edition, and saving the model to a file, schema synchronization, model validation and the visual query builder are in Pro.