Logical Database Design: Keys and Normalization | DbSchema
Logical database design is the stage where a database becomes precise enough to build but not yet tied to an engine. You decide entities, attributes, identifiers, relationships and normalization here, and you decide them before any syntax question arrives.
Get this stage right and the physical schema is a translation. Get it wrong and no amount of indexing repairs it, because the structure itself is what is wrong.
Logical vs conceptual vs physical design
The three levels are often mixed up, which is why teams argue about data types in a meeting that was supposed to agree on entities.
| Level | Answers | Output | Audience |
|---|---|---|---|
| Conceptual | what does the business care about? | broad entities and how they relate | stakeholders |
| Logical | what is the structure, and what are its rules? | entities, attributes, identifiers, cardinality, normal forms | designers and reviewers |
| Physical | how does this run on one engine? | tables, data types, indexes, constraints, engine SQL | implementers and DBAs |
The boundary that matters is the second one. A logical model is finished when every structural question is answered and no engine-specific question has been.
- Belongs to logical design: which entities exist, what identifies each one, which relationships are mandatory, where a many-to-many becomes a junction entity, how far to normalize.
- Belongs to physical design: VARCHAR versus TEXT, which columns get indexes, partitioning, storage parameters, collation.
For a design-first walkthrough that runs the whole way through to SQL, pair this with How to Design a Relational Database Schema and Entity Relationship Diagram.
Entity-to-table mapping
Logical design starts by turning real-world objects into entities. Take a geographic model: Countries, Cities and Residents.
- Countries (CountryID, Name, Continent, PhonePrefix)
- Cities (CityID, Name, CountryID, Population)
- Residents (ResidentID, Name, CityID, BirthYear)
| Business idea | Logical result | Physical result later |
|---|---|---|
| a country exists | Countries entity | countries table |
| a city belongs to a country | relation from Cities to Countries | foreign key |
| a resident belongs to a city | relation from Residents to Cities | foreign key |
| a country has several phone prefixes | PhonePrefixes as its own entity | table with a composite key |
The mapping is mechanical: each entity becomes a table, each attribute a column, each relation a foreign key. The judgement is in what you refuse to map.
- An attribute that can hold more than one value is not an attribute. It is a missing entity.
- A repeated group of columns, such as phone1 and phone2, is the same signal.
- An attribute that describes something other than the entity's identifier belongs on a different entity.
Spotting these in a diagram takes minutes. Spotting them after the DDL exists takes a migration.
The relationship decisions to make early
Every relationship carries four decisions. Making them explicitly at the logical stage is the difference between a model that translates cleanly and one that argues with the engine later.
| Decision | Why it matters | Example |
|---|---|---|
| Is the relationship identifying? | decides whether the parent key becomes part of the child's identifier | PhonePrefixes(CountryID, PhonePrefix) |
| Is it mandatory or optional? | becomes the NULL rule on the foreign key column | every City must belong to a Country |
| What is the cardinality? | decides whether a junction entity is needed | one Country has many Cities |
| What happens on delete or update? | becomes the referential action | ON DELETE CASCADE versus NO ACTION |
Identifying vs non-identifying
In an identifying relationship the parent's key becomes part of the child's identifier, so the child cannot exist independently. A phone prefix is meaningless without its country, which makes CountryID part of its key.
In a non-identifying relationship the child references the parent but keeps its own identity. A City keeps CityID as its identifier and merely points at a Country. Choose identifying only when the child genuinely has no meaning alone, because the choice propagates into every table below it.
Cardinality
- 1:1 - one record matches one record. Usually a sign the two entities should be one.
- 1:n - one parent, many children. The common case, implemented as a foreign key on the child.
- m:n - many on both sides. Never implementable directly, so it becomes a junction entity, and that entity often turns out to carry attributes of its own.
Mandatory vs optional
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 it per relationship, because a default of optional produces a schema where nothing can be relied on.
Virtual foreign keys when the database enforces nothing
Some relationships are real in the data but absent from the database: views, imported datasets, legacy tables whose constraints were never created. A virtual foreign key records the relationship in the model even though the engine does not enforce it.
- Document how imported or legacy tables actually relate, so the next reader is not guessing.
- Connect tables across a boundary the database cannot constrain, such as two schemas or a view.
- Give diagram and query tooling the join paths it would otherwise have no way to know.
State the limit plainly in your documentation: a virtual foreign key is a modelling annotation, not a constraint. Nothing stops a bad row from being written.
Normalization in one pass
Normalization belongs to logical design because changing the model is cheap and changing a deployed schema is not. Three forms cover almost every practical case. The example is an online store.
1NF: one value per attribute
| Before - OrderID | CustomerName | Products | PhoneNumbers |
|---|---|---|---|
| 101 | Alice Smith | Shoes, T-shirt | 555-1234, 555-5678 |
| After - OrderID | CustomerName | Product | PhoneNumber |
|---|---|---|---|
| 101 | Alice Smith | Shoes | 555-1234 |
| 101 | Alice Smith | T-shirt | 555-5678 |
Repeating groups are gone, so each attribute can be filtered, validated and indexed on its own.
2NF: no partial dependencies
| Before - OrderID | Product | CustomerName | PhoneNumber |
|---|---|---|---|
| 101 | Shoes | Alice Smith | 555-1234 |
| 101 | T-shirt | Alice Smith | 555-1234 |
| After | Orders | OrderDetails |
|---|---|---|
| structure | OrderID, CustomerName, PhoneNumber | OrderID, Product |
| row | 101, Alice Smith, 555-1234 | 101, Shoes / 101, T-shirt |
With (OrderID, Product) as the key, customer data depended on only half of it. Order-level and line-level facts now sit at their own grain.
3NF: no transitive dependencies
| Before - OrderID | CustomerID | CustomerName | PhoneNumber |
|---|---|---|---|
| 101 | C001 | Alice Smith | 555-1234 |
| After | Orders | Customers |
|---|---|---|
| structure | OrderID, CustomerID | CustomerID, CustomerName, PhoneNumber |
| row | 101, C001 | C001, Alice Smith, 555-1234 |
Customer details depended on CustomerID, not on OrderID. Moving them out means a customer's phone number is updated in one place.
| Normal form | What it removes | The symptom it cures |
|---|---|---|
| 1NF | repeating groups and non-atomic values | you cannot query or constrain a value inside a list |
| 2NF | partial dependencies on a composite key | the same fact is repeated on every line item |
| 3NF | transitive dependencies between non-key attributes | one business change means many row updates |
Stop at 3NF unless a measured query says otherwise. For the deeper treatment with more cases, see Database Normalization.
Naming and type conversion
A logical model uses business names and abstract types. Two mappings turn it into something an engine accepts, and both belong to the model rather than to the migration script.
- A naming rule turns logical names into physical ones consistently: Customer Name becomes customer_name, Phone Prefix becomes phone_prefix, CountryID becomes country_id.
- A type mapping turns abstract types into engine types: logical Text becomes VARCHAR(255) on MySQL, NVARCHAR(255) on SQL Server, and logical Number becomes INTEGER on PostgreSQL.
Keeping both as rules rather than manual edits is what lets one logical model target several engines without diverging.
From logical model to physical schema
The conversion itself is mechanical once the decisions above are made.
| Logical | Physical |
|---|---|
| entity | table |
| attribute | column |
| identifier | primary key |
| relation | foreign key |
| identifying relation | parent key inside the child's primary key |
| mandatory relation | NOT NULL on the foreign key column |
| cardinality m:n | junction table |
What conversion cannot decide for you is indexes, storage and collation. Those are physical choices, and they should be made against real queries rather than inherited from the logical model.
FAQ
What is logical database design?
The stage that defines entities, attributes, identifiers, relationships and normalization rules without tying the structure to one database engine.
What is the difference between logical and physical database design?
Logical design is engine-neutral and answers structural questions. Physical design adds data types, indexes, constraints and engine-specific SQL.
Why is normalization part of logical design?
Because it changes the structure, and structure is cheapest to change before the schema exists and holds data.
When is a relationship identifying?
When the child has no meaning without its parent, so the parent's key becomes part of the child's identifier. If the child can be identified on its own, the relationship is non-identifying.
Do I still need logical design if I already know SQL?
Yes. SQL implements a structure. Logical design is where you decide whether the structure is right, and that question does not go away because you can type DDL quickly.
Conclusion
Logical design is where database quality is decided. Entities, identifiers, cardinality, optionality and normal form are the five things that are expensive to change later, and all five are settled before an engine is chosen.
Tooling then carries that model forward: validate it, comment and tag it, find cyclic dependencies, keep it under Git review, and convert it into the physical schema. In DbSchema those steps share one model file, and database-independent logical design is an Architect Edition feature — see the database diagrams tool, or compare the alternatives in Best Logical Database Design Tools.
Ready to build the model? Download DbSchema for the free Community Edition, or compare the editions if you need logical design and offline model files.
Model the structure before you pick an engine
DbSchema keeps entities, identifiers and relationships in one model file, validates it, and converts it into the physical schema for your target database.
