When I first started learning databases, I honestly found the terminology a little overwhelming.
There were so many words being thrown around — schemas, primary keys, foreign keys, relationships, joins, normalization — and at first, they all seemed like separate things I had to memorize.
But after working with SQL and creating actual tables, I started realizing that these concepts are all connected.
So in this article, I want to explain them in the simplest way possible, especially for anyone who is just getting started with databases. Let us start by knowing what data modelling is.
DATA MODELLING
Data modeling is the process of deciding:
What information do we need?
What tables should we create?
What should each table contain?
How should the tables be connected?
For example, if I were designing a database for a university, I might need information about:
Students
Departments
Courses
Lecturers
Enrollments
I wouldn't want to throw all of that information into one giant table. That would quickly become messy and difficult to manage.
Instead, I'd create separate tables and connect them.
Students
Departments
Courses
Lecturers
Enrollments
That's the beginning of a data model.
What Is a SCHEMA
Once we've decided how we want our database to look, we need to actually define its structure.
That's where a schema comes in.
A database schema describes what exists in our database.
It can include:
Tables
Columns
Data types
Primary keys
Foreign keys
Constraints
Relationships
For example:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
We're telling the database to create a table called departments and give it these two columns where department_id is the primary key.
The difference between data modelling and the schema is that; data modeling is the plan, the schema is the structure we actually create from that plan.
PRIMARY KEYS
A primary key is basically a unique identifier for each record in a table.
For example:
student_id name 101 Alice 102 Brian 103 Carol
Here, student_id is the primary key.
FOREIGN KEYS
Where Tables Start Connecting
Let's say we have a departments table:
department_id department_name
1 Data Science
2 Computer Science
And we have our students table:
student_id name department_id
101 Alice 1
102 Brian 2
103 Carol 1
Both tables have department_id.
In the departments table, it's the primary key.
In the students table, it's a foreign key.
departments
department_id (PK)
↑
|
|
students
department_id (FK)
Hence we are allowed to say:
Alice belongs to Data Science.
Brian belongs to Computer Science.
Carol belongs to Data Science.
The foreign key is basically helping us connect the information.
Understanding RELATIONSHIPS
You need to understand how tables relate to each other, there are three main relationships which are;
1. One-to-One (1:1)
This means one record is connected to one record.
For example:
Person → Passport
One person has one passport.
2. One-to-Many (1)
It means one record in one table can be connected to many records in another table.
For example:
Department → Students
One department can have many students.
For example:
Data Science
|
├── Alice
├── Carol
└── David
So we have:
One department → Many students
3. Many-to-Many (M)
Here one record can be connected to many records and the same record can also be connected to many records take an instance
students and courses.
One student can take many courses and at the same time, one course can have many students.
So:
Students ↔ Courses
That's a many-to-many relationship.
We usually handle this by creating another table in between.
This is often called a junction table or bridge table.
** What Is a JOIN?**
A JOIN allows us to combine information from different tables.
For example, our students table only tells us the department ID:
student_id name department_id
101 Alice 1
102 Brian 2
103 Carol 1
But what if I want to see the actual department name?
That's where a JOIN comes in.
SELECT
students.name,
departments.department_name
FROM students
JOIN departments
ON students.department_id = departments.department_id;
The result would look something like:
name department_name
Alice Data Science
Brian Computer Science
Carol Data Science
The part I really want to pay attention to is:
ON students.department_id = departments.department_id
This tells SQL:
"These are the columns I want you to use to connect these two tables."
Once I understood that, JOINs became much easier.
Types of JOINs
There are several types of JOINs, but let's focus on the main ones.
INNER JOIN
An INNER JOIN gives you only the records that have a match in both tables.
SELECT *
FROM students
INNER JOIN departments
ON students.department_id = departments.department_id;
Think of it as:
"Show me the records that match."
LEFT JOIN
A LEFT JOIN keeps everything from the table on the left, even when there isn't a matching record on the right.
SELECT *
FROM students
LEFT JOIN departments
ON students.department_id = departments.department_id;
The easiest way to remember it:
LEFT JOIN = Keep everything on the left.
So if there's a student without a matching department, that student can still appear in the results.
RIGHT JOIN
A RIGHT JOIN is basically the opposite.
It keeps everything from the table on the right.
SELECT *
FROM students
RIGHT JOIN departments
ON students.department_id = departments.department_id;
So:
RIGHT JOIN = Keep everything on the right.
FULL OUTER JOIN
A FULL OUTER JOIN keeps everything from both tables.
SELECT *
FROM students
FULL OUTER JOIN departments
ON students.department_id = departments.department_id;
It includes matching records as well as records that don't have a match.
CROSS JOIN
This one is a little different.
A CROSS JOIN creates every possible combination between two tables.
For example, if we have:
3 students
4 courses
We would get:
3 × 4 = 12 combinations
The query would be:
SELECT *
FROM students
CROSS JOIN courses;
It's not something you'll use every day, but it's good to know what it does.
Relationship vs JOIN
A relationship describes how tables are connected in the database.
For example:
Department 1 ───────< Students
This tells us that one department can have many students.
A JOIN, on the other hand, is something we use in an SQL query to actually bring information from those tables together.
For example:
SELECT *
FROM departments
JOIN students
ON departments.department_id = students.department_id;
So the easiest way to remember it is:
Relationship = how the tables are connected.
JOIN = how we retrieve data from those connected tables.
Where Does Normalization Come In?
Another term you'll probably hear a lot when learning databases is normalization.
The basic idea is to organize your data so that you're not unnecessarily repeating the same information everywhere.
For example, imagine having:
student department lecturer
Alice Data Science Mr. John
Brian Data Science Mr. John
Carol Data Science Mr. John
There's a lot of repeated information.Instead, we can separate the information into different tables and connect them.
Departments
↓
Students
↓
Enrollments
↓
Courses
This makes the database cleaner and easier to maintain.
If something about the Data Science department changes, we don't want to update hundreds of student records individually.
Final Thoughts
Learning databases can feel confusing in the beginning because there are so many new terms to learn at once.
But you don't necessarily have to memorize everything separately.
Once you understand how the pieces fit together, it starts making much more sense.
For me, the biggest thing was realizing that tables aren't just random collections of data. They're designed to work together.
So if you're currently learning SQL, don't just memorize JOIN syntax.
Try creating a small database yourself.
Create a few tables, give them primary and foreign keys, establish relationships, and then write queries to bring the information together.
That's when these concepts really start to click.
And trust me, once JOINs finally make sense, SQL becomes a lot less scary.