Learn Logical Data Modeling Fast | Visual ER Diagrams with DbSchema

In this tutorial, you'll learn how to build a logical design from scratch using DbSchema - including entities, attributes, relationships, normalization (1NF to 3NF), naming rules, and more.

Perfect for beginners or anyone who wants to design clean, structured databases before deploying them.

🔗 Try DbSchema: https://dbschema.com/?utm_source=youtube&utm_medium=organic_video&utm_campaign=youtube_channel&utm_content=t4MKvTVk-sw
📝 Full article: https://dbschema.com/blog/design/logical-design-for-databases/?utm_source=youtube&utm_medium=organic_video&utm_campaign=youtube_channel&utm_content=t4MKvTVk-sw

Concept, Script & Video Production by Roxana Haidiner (Marketing Specialist)

0:00 Introduction
0:16 What is Logical Design?
0:32 Start a Logical Design using DbSchema
0:55 Create Entities &, Attributes with Data Types
2:04 Define Relationships
2:29 Non-Identifying - One to Many Relationship
3:09 Identifying - One Relationship
3:57 Many-to-Many Relationship
4:50 Normalization Explained
5:25 1NF - First Normal Form
6:04 2NF - Second Normal Form
7:08 3NF - Third Normal Form
8:00 Naming Dictionary
8:56 Conversion Dictionary
9:56 Comments & Tags in Logical Design
10:36 Git Integration, Multiple Diagrams, HTML5 Documentation
11:31 Convert to Physical Model
12:08 Download DbSchema for free

#DatabaseDesign #DbSchema #LogicalDesign #SQL #NoSQL #Database #erdiagram

---

Closed Caption:

Hello and welcome to the DB schema
channel. In this video, I'll show you
how to start a logical design using DB
schema. I'll also explain what logical
design means and how it
works. A logical design is a database
model that defines how data is organized
using entities, attributes, and
relationships, but without depending on
any specific database engine like MySQL
or PostgreSQL.
You can think of it as the blueprint of
your
database. Now let's see how you can
create a logical design in DB
schema. First open DB schema. On the
welcome screen, click design from
scratch. Choose logical
design. Enter a name for your project.
Then click okay.
In logical design, you start by creating
entities. Each entity will become a
table in your
database. Inside each entity, you add
attributes. These will become the
columns of the
table. For example, I'll create three
entities. To create a new entity, just
right click on the diagram and choose
create entity.
Click add attribute. Then type the
name and choose the data type which is
customizable. This is how you define
what kind of data will be stored in that
column, numbers, text, dates and so
on. I'll choose varchar with a length of
three. So I can store short codes like
USA or
UK. Now I'll make this field a primary
key. That means every country will have
a unique ID and no duplicates are
allowed. Once we've added country ID to
the cities entity, we can now create a
relationship. I'll draw a line from
country ID in cities to country ID in
countries entity.
Now we can see several options for how
to define this relationship. These
settings help describe how closely the
two entities are connected. In this
example, the relationship is one to many
because one country can have many cities
but each city belongs to only one
country. It's also a nonidentifying
relationship because country ID is just
a foreign key in the city's entity. It's
not part of the primary
key. That means cities are linked to
countries, but they still have their own
identity. To explain more clearly how
relationships are defined, I've created
a few more entities in the
diagram. This will help you understand
the different types of relationships in
a simple way.
In this example, we have a countries
entity with a primary key, country ID
and a phone prefixes entity that stores
phone codes for each
country. Phone prefixes entity is the
child in this relationship and it uses
both country ID and phone prefix as its
primary key. This means the identity of
each phone prefix depends on the
country. It cannot exist without it. The
two fields together make the record
unique. The cardality here is exactly
one. Each phone prefix must belong to
exactly one
country. Now let's look at a many to
many relationship.
This means that records in both entities
can be linked to multiple records in the
other. For example, one country can have
many spoken languages and the same
language can be spoken in many
countries. To connect them, we need a
third entity called a junction entity.
In this case, it's called country
languages. Country languages contains
two fields, country ID and language ID.
Together they form the primary key and
create the link between the two
tables. The cardality here is zero or
more on both sides. A country can have
many languages or even none and a
language can be spoken in many
countries. Normalization means
organizing your data in a way that
removes duplication and keeps it clean.
It helps break down big messy tables
into smaller ones where each table
focuses on just one
thing. This makes the database easier to
update and helps avoid mistakes like
storing the same info in multiple
places. There are five normal forms in
database normalization, but in this
video I'm going to focus on the first
three, the ones that are most commonly
used and essential for a solid database
design.
Let's talk about first normal form. The
first rule of clean
databases. The goal. Make sure each
column contains only one indivisible
value. Here's a
problem. In this table, fields like
products contain multiple values. This
breaks atomicity and makes searching or
updating much harder. The solution?
Split those values into separate rows.
Now each value is atomic and your data
is in first normal form, clean,
searchable and ready for the next steps
in
normalization. Let's break down second
normal form. The goal remove partial
dependencies which means every non-key
column must depend on the entire primary
key not just part of it.
Example, if your table uses a composite
key like order ID plus product, but
fields like customer name and phone
number depend only on order ID, you have
a
problem that causes repeated customer
data, violating second normal
form. The solution, split it into two
tables.
The orders table holds customer name and
phone number identified by order
ID. The order details table contains
product information linked using a
composite key of order ID and
product. Now each column depends fully
on its table's key. This removes
redundancy and sets you up for third
normal
form. Let's simplify third normal form.
The goal remove transitive
dependencies. Non-key columns should
depend only on the primary
key. In this table, order ID is the key.
But customer name and phone number
depend on customer ID, not directly on
order ID. That's a transitive dependency
and it breaks third normal
form. The solution? split the data. One
table for orders with order ID and
customer
ID. Another table for customer info
linked by customer ID. Now each column
depends directly on its table's key. It
keeps your data clean, avoids
duplication, and makes updates much
easier.
The naming dictionary in DB schema helps
you control how your table and column
names will look in the actual
database. In the logical design, names
are written in a clean human friendly
way, sometimes with spaces or camelc
case like country ID or phone
prefix. But when you generate the
physical model, DB schema can
automatically convert these into formats
that match your database standards.
For example, country ID might become
country FID and phone prefix can turn
into phone
pfx. This keeps everything consistent,
readable in SQL scripts and compatible
with database systems like PostgreSQL
that use underscores.
This is the model conversion rules
window in DB schema. It helps control
how data types and other settings are
converted when switching from the
logical model to the physical database
like
PostgrSQL. On the left, we see the data
types used in the logical
design and on the right we define how
those should be converted in the target
database. For example, int becomes
integer and varchchar turns into
varchchar with a value in
postgrql. You can also define default
values and other options for tables and
columns. Here this gives you full
control over how your model is
translated into a real working database
ensuring consistency and compatibility
with your chosen system.
You can add comments to any entity,
attribute, or relationship to explain
its
purpose. For example, you might add a
comment to the resident name attribute
saying full name of the resident used
for official documents and
reports. Tags can also be used to
categorize or flag elements in your
model.
For instance, you could tag resident
name with something like PII or personal
data. This helps teams know what's
important, like which fields store
personal data or which ones are visible
to
users. DB schema's Git integration makes
it easy to collaborate on your logical
design with your team. The design is
saved as an independent DBS model file.
It's not connected to any real database
yet. That means you can share it, track
changes, and version it just like any
code
project. In the logical design phase,
you can create multiple diagrams to
organize your model into clear sections.
This makes large designs easier to
manage and understand.
You can also export your design as
interactive HTML5
documentation. It's easy to share with
your team or stakeholders, even if
they're not using DB
schema. Once your logical model is
complete, you can easily convert it into
a physical model.
In this example, I'll choose
PostgresQL, but you can select any
supported database
system. DB schema will automatically
convert your entities into tables,
attributes into columns, and
relationships into foreign keys based on
the selected database engine.
Using DB schema for your logical design
makes it faster and easier to move into
a real
database with everything already mapped
and ready to
go. Thanks for
watching. Check out our other videos or
our documentation for more tips and
detailed tutorials.