Database Design Basics (PostgreSQL)

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.