In the previous discussion, we talked about the main challenges of working with PostgreSQL. Out of those challenges, we are going to focus on two important ones first: 1. Wri
MSMuhammad SufiyanSoftware Engineer · Jul 26
Backend Engineering HubT-
In the previous discussion, we talked about the main challenges of working with PostgreSQL.
Out of those challenges, we are going to focus on two important ones first:
1. Writing efficient queries 2. Designing the database structure (schema)
In this blog, we will mainly focus on database design and understand how to structure data properly.
Our First Example: Storing Cities Data
For our first small project, we want to store a list of large cities inside a database.
We will take this data from a public source, such as Wikipedia, which lists large cities around the world.
That table contains many columns, but for this exercise, we only care about four pieces of information:
City name
Country
Population
Area (urban area)
Our goal is to design a database that can store this information correctly.
The Database Design Process
Before creating any table, we should follow a simple design process.
This process can be reused for almost any database design problem.
We ask ourselves three basic questions:
1️⃣ What kind of thing are we storing?
In our case, we are storing a list of cities.
2️⃣ What properties does this thing have?
Each city has the following properties:
name
country
population
area
3️⃣ What type of data does each property contain?
Now we decide the type of data for each property:
name → text (string)
country → text (string)
population → number
area → number
Answering these three questions makes database design much easier.
Turning Design Into a Database Structure
Now we use these answers to actually build our database.
What is a Table?
Since we are storing a list of cities, we create a table.
We can name this table cities.
A table:
lives inside a database
stores related data
contains multiple records
In our case, the cities table will store information about all cities from the list.
All data in a database is stored inside tables, so designing tables properly is very important.
Columns: Storing Properties
From the second question (properties), we create columns.
Our cities table will have these columns:
name
country
population
area
Each column stores one specific type of information about a city.
If you imagine a table like an Excel sheet:
columns are the headings
rows are the data entries
-
Data Types Matter
Each column also has a data type:
name → text
country → text
population → number
area → number
Choosing the correct data type is important because it helps the database:
store data efficiently
validate input
perform better queries
Rows: Storing Actual Data
Once the table and columns are ready, we can start adding data.
For example:
Tokyo
Delhi
Shanghai
Each city added to the table becomes a row.
So:
one row = one city
each row contains values for name, country, population, and area
Important Database Terms Recap
Let’s summarize the basic terminology:
Database → stores data
Table → stores related records
Column → defines a property
Row → one complete record
Understanding these terms is essential before writing any SQL queries.
What’s Next?
Now that we understand:
how to think about database design
how tables, columns, and rows work
The next step is to actually create this table using SQL and start inserting data.
That’s what we’ll do next.
HIRINGMINE CAREER SIGNAL
This writing is proof of expertise.
Explore the author’s verified skills, projects and availability—or start a professional conversation.
That table contains many columns, but for this exercise, we only care about four pieces of information:
1️⃣ What kind of thing are we storing?
What is a Table?
Since we are storing a list of cities, we create a table.
Data Types Matter