Logical Design
The database logical design is the process of creating a conceptual schema or data model of a database. This process involves identifying the entities, attributes, and relationships that are important to the organization or system being modeled. The goal of the logical design is to create a high-level representation of the data that is independent of any specific database management system or implementation.
The logical design is typically created using entity-relationship (ER) modeling or a similar approach. The resulting schema is often depicted in a diagram that shows the entities, attributes, and relationships, as well as any constraints or business rules that apply to the data.
The logical design is an important step in the database design process because it provides a clear understanding of the data and how it relates to the organization or system being modeled. This understanding is essential for creating an efficient and effective physical database design, which involves mapping the logical schema onto a specific database management system and optimizing the database for performance and storage.
In the logical design the elements are called:
| Physical Design | Logical Design | Comments |
|---|---|---|
| Schema | Subject Area | |
| Table | Entity | |
| Column | Attribute | |
| Foreign Key | Relation | Indentifying, Non-Identifying, Many-to-Many + Cardinality |
Types of Relationships
Identifying vs. Non-Identifying Relationships
Identifying: The parent key is part of the child’s key. The child cannot exist without the parent.
Example: PhonePrefixes(CountryID, PhonePrefix) — depends on Countries(CountryID).
Non-Identifying: The child references the parent key, but it’s not part of its identifier.
Example: Residents references Cities.CityID, but has its own primary key.
One-to-Many vs. Many-to-Many
One-to-Many: One country → many cities. Cities.CountryID is a foreign key.
Many-to-Many: Countries ↔ Languages via CountryLanguages(CountryID, LanguageID).
Mandatory vs. Optional Relationships
Mandatory: Child must reference parent. E.g., Residents.CityID is required.
Optional: Link may be missing. (Not used in current design but allowed for future needs.)
Cardinality
- 0..*: A country may have no residents.
- 1..*: Each city must have at least one resident.
- 0..1: A city may or may not have residents.
- 1: Each phone prefix must belong to one country.
- X..Y: A team has 2 to 5 members.
On Delete / Update Actions
- CASCADE: Deletes related child records.
- NO ACTION: Blocks delete if related child exists.
- SET NULL: Unlinks the child by setting foreign key to null.
These are part of the physical design and supported by DbSchema.
Virtual Relationships in DbSchema
DbSchema allows defining virtual relationships - logical links not present in the database schema. Useful for:
- Views or external sources
- Modeling unforced relationships
- Using in Query Builder or Data Editor
Helpful for documentation and exploration without modifying the real schema.
Normalization
Normalization means organizing data to avoid duplication and make updates easier. We’ll use customer orders as an example.
First Normal Form (1NF)
Goal: Each field contains one value only – no lists.
Not in 1NF – multi-value fields:
| OrderID | CustomerName | Products | PhoneNumbers |
|---------|---------------|-------------------|----------------------|
| 101 | Alice Smith | Shoes, T-shirt | 555-1234, 555-5678 |
| 102 | Bob Lee | Laptop | 555-1111 |
1NF Solution – split values into rows:
| OrderID | CustomerName | Product | PhoneNumber |
|---------|---------------|-----------|-------------|
| 101 | Alice Smith | Shoes | 555-1234 |
| 101 | Alice Smith | T-shirt | 555-5678 |
| 102 | Bob Lee | Laptop | 555-1111 |
Second Normal Form (2NF)
Goal: Remove partial dependencies – all non-key columns depend on the full key.
Not in 2NF – composite key issue:
| OrderID | Product | CustomerName | PhoneNumber |
|---------|----------|---------------|-------------|
| 101 | Shoes | Alice Smith | 555-1234 |
| 101 | T-shirt | Alice Smith | 555-1234 |
2NF Solution – split into:
Orders Table:
| OrderID | CustomerName | PhoneNumber |
|---------|---------------|-------------|
| 101 | Alice Smith | 555-1234 |
OrderDetails Table:
| OrderID | Product |
|---------|---------|
| 101 | Shoes |
| 101 | T-shirt |
Third Normal Form (3NF)
Goal: Remove transitive dependencies – non-key fields depend only on the key.
Not in 3NF – customer details depend on CustomerID, not OrderID:
| OrderID | CustomerID | CustomerName | PhoneNumber |
|---------|-------------|--------------|-------------|
| 101 | C001 | Alice Smith | 555-1234 |
3NF Solution – separate customers:
Orders Table:
| OrderID | CustomerID |
|---------|-------------|
| 101 | C001 |
Customers Table:
| CustomerID | CustomerName | PhoneNumber |
|------------|---------------|-------------|
| C001 | Alice Smith | 555-1234 |
Normal Forms Summary
| Normal Form | What It Fixes | How It Helps |
|---|---|---|
| 1NF | Repeating groups & non-atomic values | Each field holds one value |
| 2NF | Partial dependencies on composite keys | Separates data by full key dependency |
| 3NF | Transitive dependencies | Removes indirect links between columns |
The Naming Dictionary
The naming dictionary stores how the logical names should be mapped into physical names.
Open the naming dictionary from the 'Convert Model' menu. Entities, attributes and relations names will be generated by replacing the logical keywords with the physical keywords.
The Conversion Dictionary
Generate Physical Model
