How To Design And Build A Database In Postgres
Tags: postgres
Published: 2023-02-27 15:54:17

**As a data analyst, a client called John approaches you and tells you that he is starting an online ecommerce business. John then asks you to design and build a relational database that will enable him to store important information that his ecommerce business generates.**

The data the client wants to be stored in the database is data related to items on sale, orders made, and customers who make the orders. After that, he wants you to analyze the data to come up with analytics represented visually so that he can monitor the business performance.

In this article, I will show you how to design and build a relational database from scratch in Postgres for John’s ecommerce business. We are using Postgres because it is an advanced open-source relational database system that helps data analysts to manage data regardless of the size of the dataset.

After that, I will show you how to run SQL queries on data stored in the database to come up with analytics and visualize the analytics using charts on DbVisualizer.

## Prerequisites
To follow through with this article, you need a database designing tool, a database management system, and a SQL client. In this case, we will use the [Quick Database Diagrams](https://www.quickdatabasediagrams.com/) tool for design, Postgres as the database management system, and DbVisualizer as the database SQL client.

To install PostgreSQL, navigate to the [PostgreSQL download page](https://www.postgresql.org/download/) and download it for your operating system. You can follow [this guide](https://www.youtube.com/watch?v=IYCNAjyoHs8&t=5s) to install PostgreSQL on Windows, [this guide](https://www.youtube.com/watch?v=-LwI4HMR_Eg&t=599s) to install it on Linux, and [this guide](https://www.youtube.com/watch?v=EZAa0LSxPPU) to install it on macOS.

To install DbVisualizer, navigate to the [DbVisualizer download](https://www.dbvis.com/download/) page. Then download the recommended installer for your operating system. After that, execute the installer you have downloaded and follow the instructions that follow.

When you start DbVisualizer, you are prompted to choose the DbVisualizer plan to proceed. You can proceed with the DbVisualizer free version with limited features or get a 21-day free trial on the pro version.

## Designing the Database Using Quick Database Diagrams
First of all we need to design the database. There are many tools you can use for this, and for this example we will be using Quick Database Diagrams, also known as QuickDBD. QuickDBD is a simple web-based tool that lets you quickly draw database diagrams by typing. QuickDBD enables you to design database tables as diagrams and then allows you to download a SQL file containing the SQL queries needed to create the tables in a database.

Since the client wants to collect data on items, customers, and orders, we can say that the data classes were already specified for each category. For items, he wants data on the following data classes:

- Item name
- Item category
- Item price

For customers, he wants data on the following:

- The first name of the customer
- The last name of the customer
- The details about the item that was bought
- The address of the customer

For orders, he wants data on the following:

- The details on all of the items that were bought
- The details about the customer that bought the item
- The quantity of the items that were bought

Looking at the client’s data requirements, we can assume that the database needs three tables to store data from each category.

To design the database and the tables, we need to specify all the fields needed for each table. Then we will do data normalization and define table relationships. Normalizing data is the process of organizing data to reduce redundancy and make it more flexible and efficient.

Let’s now spec out all the fields needed for the items table as shown below,

- The ID of the item.
- The name of the item.
- The category of the item.
- The price of the item.

We can now use the [Quick Database Diagrams](https://www.quickdatabasediagrams.com/) tool to see how the items table will look inside the database.
<br />
![Image 1 - Quick Database Diagrams](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/edyr2807czgfgt4dy32d.png)
<figure><figcaption>Image 1 - Quick Database Diagrams</figcaption></figure>
<br />
You specify tables, their fields, and field data types on the left sidebar, as shown below.
<br />

![Image 2 - Specifying tables, their fields, and field data types](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dp8n6uhsh3fnfy364330.png)
<figure><figcaption>Image 2 - Specifying tables, their fields, and field data types</figcaption></figure>
<br />
On the right are diagrams produced by tables and their fields, as shown below.
<br />

![Image 3 - diagrams produced by tables and their fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5xzg42eire00kebzmo7n.png)
<figure><figcaption>Image 3 - diagrams produced by tables and their fields</figcaption></figure>
<br />
Below are the steps you can follow to create the items table.

**Step 1:** On the left sidebar, write the table's name, jump to the next line and add a dash.
<br />

![Image 4 - creating items table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e41oll6zve1lu5s8idiw.png)
<figure><figcaption>Image 4 - creating items table</figcaption></figure>
<br />
You can now see the items table has been added to the diagrams.
<br />

![Image 5 - items table diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t2nxgx8j63g54jm7sq6t.png)
<figure><figcaption>Image 5 - items table diagram</figcaption></figure>
<br />
**Step 2:** Add the `items table fields` by specifying the field names and their data types.
<br />

![Image 6 - adding items table fields](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k0j4txpdeumx1gs2gp7j.png)
<figure><figcaption>Image 6 - adding items tablefields</figcaption></figure>
<br />
`Item_id` field is a unique identifier for each item data stored in the `items table` hence it is the `primary key (pk)`. The field has an `int` data type with the `item_price` field because the fields will hold data in number form.

`Item_name` and `item_category` fields have `varchar` data types because they will store data in string form with a maximum length of 50 characters. You can now see how the items table will look on the diagram.
<br />

![Image 7 - items table fields diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/qx3qzit9s08dp853e0a8.png)
<figure><figcaption>Image 7 - items table fields diagram</figcaption></figure>
<br />
Let us now define the fields needed for the `customers table`, as shown below.

- Customer ID (Int data type)
- Customer Firstname (Varchar data type)
- Customer Lastname (Varchar data type)
- Customer Address (Varchar data type)

We can now use the Quick Database Diagrams tool to design the `Customers Table` as shown below.
<br />

![Image 8 - items table fields, customers table fields and their diagrams](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/nh9r1fabolv0iqewwym2.png)
<figure><figcaption>Image 8 - items table fields, customers table fields and their diagrams</figcaption></figure>
<br />
Let us now define all the fields needed for the `orders table`, as shown below.

- Order id (Int data type)
- Item name(Varchar data type)
- Item category (Varchar data type)
- Item price (Int data type)
- Items bought (Int data type)
- Customer firstname (Varchar data type)
- Customer lastname (Varchar data type)
- Customer address (Varchar data type)

We can now use the Quick Database Diagrams tool to design the `orders table` as shown below.
<br />

![Image 9 - items table fields, customers table fields, orders table fields and their diagram](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vk1uh88jp6xsb5lvq8fg.png)
<figure><figcaption>Image 9 - items table fields, customers table fields, orders table fields and their diagram</figcaption></figure>
<br />
Looking at the three tables we designed, you will realize that the `orders table` has fields similar to the `items table` and `customers table`. This is where we can use normalization by breaking a larger table, like `orders table`, into smaller tables and defining the relationships between the tables. This will enable us to eliminate repetitive data fields and ensure data is stored logically.

The first thing we will do is remove all fields on the `orders table` that look similar to fields on the `items table`. Then we will add the field that is a unique identifier of the `items table` to the `orders table` and connect the fields on both tables to define the two tables' relationship, as shown below.
<br />

![Image 10 - defining items table and orders table relationship.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/njzbgnmfjyysjvi1zlph.png)
<figure><figcaption>Image 10 - defining items table and orders table relationship.</figcaption></figure>
<br />
Let us now do the same for the `customers table` and define its relationship with the `orders table`, as shown below.
<br />

![Image 11 - defining customers table and orders table relationship.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/454g0niypi39f08sqkrw.png)
<figure><figcaption>Image 11 - defining customers table and orders table relationship.</figcaption></figure>
<br />
After defining the tables' relationships, let us now download the SQL file that contains SQL queries required to create the tables in a database. You can download the SQL file by clicking the `EXPORT` button and selecting `PostgreSQL`, as shown below.
<br />

![Image 12 - downloading file containing SQL queries required to create tables in a database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xz72joqe7jg3t6m0o4oc.png)
<figure><figcaption>Image 12 - downloading file containing SQL queries required to create tables in a database.</figcaption></figure>
<br />
Tools like QDBD help you to define database structure by typing and diagrams where they also provide you with SQL queries that you can run on DbVisualizer to create tables for a database in PostgreSQL.
<br />
## Connecting Postgres to DbVisualizer
**Step 1:** Start DbVisualizer and click `create a connection` button as shown below.
<br />

![Image 13 - creating a database connection in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lyeqcxqrn0p3zqib1pre.png)
<figure><figcaption>Image 13 - creating a database connection in DbVisualizer.</figcaption></figure>
<br />
**Step 2:** Search and select the Postgres driver from the popup menu on the left side of your screen, as shown below.
<br />

![Image 14 - Searching and selecting Postgres driver in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/syf2wpchlfi6ovynl1b1.png)
<figure><figcaption>Image 14 - Searching and selecting Postgres driver in DbVisualizer.</figcaption></figure>
<br />
An object view tab for the Postgres connection is opened.

**Step 3:** Fill in the empty fields as shown below:

**Name:** JohnEcommerceDatabase
**Database:** postgres
**Database Userid:** postgres
**Database Password:** Enter Your Postgres password

Once you have filled in all the empty fields, click the connect button at the bottom. If the connection is successful, your screen should look as shown below.
<br />

![Image 15 - object view tab for the Postgres connection.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tz54aayoq9hxbq8ax0um.png)
<figure><figcaption>Image 15 - object view tab for the Postgres connection.</figcaption></figure>
<br />
## Creating a database on Postgres using DbVisualizer
Let us now create a database that will house the tables we designed.

**Step 1:** Open the database connection `JohnEcommerceDatabase` tab tree as shown below.
<br />
![Image 16 - opening database connection tab tree.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wfy93fh92ytjfbs1bcgi.png)
<figure><figcaption>Image 16 - opening database connection tab tree.</figcaption></figure>
<br />
Inside the connection tab tree, open the `Databases` tab tree.
<br />

![Image 17 - opening database tab tree.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tyi7q8vrydevlis0dgbz.png)
<figure><figcaption>Image 17 - opening database tab tree.</figcaption></figure>
<br />
**Step 2:** Right-click on the `Databases` tab tree and select `Create Database` option.
<br />

![Image 18 - right-clicking databases tab tree and selecting create database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ya6hc0z3lz6vv4c8di62.png)
<figure><figcaption>Image 18 - right-clicking databases tab tree and selecting create database.</figcaption></figure>
<br />
**Step 3:** Fill in the fields as shown below and then click the `Execute` button to create the database.

**New Database Name:** “Name for your Database.”
**Owner:** “Owner of the database in PostgreSQL.”
**Template:** “Boilerplate files containing SQL scripts that help you create objects in a database.”
**Encoding:** “Encoding converts data into a standard format.”
**Collation:** “Collation specifies how data is sorted and compared in a database”
<br />

![Image 19 - filling database fields.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9fq4ru5vkynydo7sji5w.png)
<figure><figcaption>Image 19 - filling database fields.</figcaption></figure>
<br />
If you look at the `JohnEcommerceDatabase` connection, you will see the database `ecommercedb` has been created.
<br />

![Image 20 - created ecommercedb database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/aiunhtazdxzqi1od27gn.png)
<figure><figcaption>Image 20 - created ecommercedb database.</figcaption></figure>
<br />

## Creating Tables Designed On Quick Database Diagrams

**Step 1:** Navigate to the folder where the SQL file containing SQL queries for creating the tables we designed was saved.
<br />

![Image 21 - SQL file containing SQL queries for creating tables in database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f078xib4kr0mihb0rrnl.png)
<figure><figcaption>Image 21 - SQL file containing SQL queries for creating tables in database.</figcaption></figure>
<br />
**Step 2:** Double-click the SQL file, which should open on DbVisualizer, as shown below.
<br />

![Image 22 - SQL file containing SQL queries for creating tables in a database opened on DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wnmgm8jjecxpwqw5aj1n.png)
<figure><figcaption>Image 22 - SQL file containing SQL queries for creating tables in a database opened on DbVisualizer.</figcaption></figure>
<br />
The SQL file has queries defining our database structure, where related data is grouped into tables consisting of rows and columns.

**Step 3:** Open `[ choose connection ]` drop-down menu and select `JohnEcommerceDatabase` as the connection containing the database where you want to create the tables.
<br />

![Image 23 - selecting connection containing the database where you want to create the tables.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/new0a6h2ibhq1tfrd5ku.png)
<figure><figcaption>Image 23 - selecting connection containing the database where you want to create the tables.</figcaption></figure>
<br />
**Step 4:** Select `ecommercedb` as the database you want to create the tables.
<br />

![Image 24 - selecting database where you want to create the tables.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wmuj1r469vuax2czw9nq.png)
<figure><figcaption>Image 24 - selecting database where you want to create the tables.</figcaption></figure>
<br />
**Step 5:** Press the `(Ctrl+Enter)` keys to run the SQL queries. Right-click on the table tab and refresh to see the tables created.
<br />

![Image 25 - refreshing objects tree to see the tables created.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/obssu14qseguuy1atybi.png)
<figure><figcaption>Image 25 - refreshing objects tree to see the tables created.</figcaption></figure>
<br />
You should now see that all the designed tables have been created on the database we created.
<br />

![Image 26 - tables created on database.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fves4mb6qd65ydrinvn8.png)
<figure><figcaption>Image 26 - tables created on database.</figcaption></figure>
<br />

## Importing Data Table Into Postgres Using DbVisualizer
To run SQL queries, we need to add data to the tables we created. I have created three files containing data we can import into the tables and run SQL queries. You can download the data files by navigating to [this link](https://drive.google.com/drive/folders/18cA5nqlGDwHZyAFWLEwMdIpt2eNp1DOV).

Once the download is complete, navigate to the folder where the data files have been saved and you should see the three data files for each table as shown below.
<br />

![Image 27 -data files.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/319y7qoov20ph8tncleu.png)
<figure><figcaption>Image 27 -data files.</figcaption></figure>
<br />
To import data into the `customers` table, right-click on the `customers` table and select the `Import Table Data` option.
<br />

![Image 28 - importing data into the customers table.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/p5q2im5vluyhgj57ruff.png)
<figure><figcaption>Image 28 - importing data into the customers table.</figcaption></figure>
<br />
Navigate to the directory containing the data files, select the customers data file and click open.
<br />

![Image 29 - selecting and opening customers data file.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rzup4g863y5qfgnc0h0k.png)
<figure><figcaption>Image 29 - selecting and opening customers data file.</figcaption></figure>
<br />
Click the `Next` button and keep clicking it until you reach the window shown below, and then click the import button. The window provides an option to import all rows or a just 50 rows from the data file. Batch import option significantly improves the data import speed.
<br />

![Image 30 - importing customers table data.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tknlvt5w8kgxgui78e38.png)
<figure><figcaption>Image 30 - importing customers table data.</figcaption></figure>
<br />
Once the data has been imported, right-click on the `customers` table and refresh to see the table data.
<br />

![Image 31 - refreshing customers table to see table data.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vi0ybbv3ck54ejf2ldua.png)
<figure><figcaption>Image 31 - refreshing customers table to see table data.</figcaption></figure>
<br />
Open the table and navigate to the `Data` tab. You should be able to see the imported data, as shown below.
<br />

![Image 32 - imported customers table data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d0ftuwnwa2195ljru0mq.png)
<figure><figcaption>Image 32 - imported customers table data</figcaption></figure>
<br />
Repeat the same process and import data into the `items table` and `orders table`.

## Running SQL queries on DbVisualizer
If we look at the `orders table`, we will see that it has data coming from the `customers table` and `items table` represented by customer_id and item_id columns, respectively. For that reason, we can’t get much information from the orders table such as which items are selling well, as shown below.
<br />

![Image 33 - orders table data.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/0tl8jf4e3vnehhu8jb95.png)
<figure><figcaption>Image 33 - orders table data.</figcaption></figure>
<br />
To get more information about the orders, we can create a view which is a virtual table based on the result of an SQL statement. The SQL statement will use SQL joins where a JOIN clause is used to combine rows from two or more tables based on a related column between them.

The SQL query for creating the view table is as shown below.

```
CREATE VIEW TotalOrders AS
SELECT orders.order_id, customers.customer_firstname,
customers.customer_lastname, items.item_name,
items.item_category, items.item_price, orders.items_bought,
customers.customer_address, items.item_price *
orders.items_bought AS spend
FROM orders
INNER JOIN customers ON
orders.customer_id=customers.customer_id
INNER JOIN items ON orders.item_id=items.item_id
```
The SQL query first creates a view table called `TotalOrders`. Then all columns needed for the `TotalOrders` view table are selected from the orders table. After that, the SQL query uses the `INNER JOIN` clause to join the `customers table` and the `items table` to the `orders table` using related columns. In simple terms, the SQL query gets all the columns with their data from the `items table` and `customers table` and adds them to the `TotalOrders view table`.

To run the SQL query in DbVisualizer, click the `SQL Commander` tab at the top of your screen and select `New SQL Commander`, as shown below. Doing so will create a new tab to run SQL queries in.
<br />

![Image 34 - opening SQL editor in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/k4m424fvk5pdgytoarst.png)
<figure><figcaption>Image 34 - opening SQL editor in DbVisualizer.</figcaption></figure>
<br />
Once the SQL Commander opens, type the SQL query and press the `(Ctrl+Enter`) keys to run the query.
<br />

![Image 35 - running SQL query in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/xj66a3fweglstozaivlt.png)
<figure><figcaption>Image 35 - running SQL query in DbVisualizer.</figcaption></figure>
<br />
If you open the `views` tab tree, you should see a view called totalorders has been created.
<br />

![Image 36 - totalorders view created in DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a4pnlo5kis9uiyof7hx7.png)
<figure><figcaption>Image 36 - totalorders view created in DbVisualizer.</figcaption></figure>
<br />
Open the view, and you should see more information about the orders.
<br />

![Image 37 - totalorders view table data](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/65i0o5rd9xk0y7hn8k07.png)
<figure><figcaption>Image 37 - totalorders view table data</figcaption></figure>
<br />
Since the client also wants to get some analytics from his ecommerce business to monitor its performance, we can create a SQL query that shows `top-selling products by total sales`, as shown below.

```
SELECT totalorders.item_name, SUM(spend) AS sales
FROM totalorders, items
WHERE totalorders.item_name=items.item_name
GROUP BY totalorders.item_name
ORDER BY SUM(spend) DESC
```
From the SQL query above, we are selecting the `item_name` column from the `totalorders` view table, summing up customer spending on each product as `sales` where values in the `item_name` column in the `totalorders` view table are equal to values in `item_name` column in `items` table, and then, the results are grouped by the `item_name` column in the `totalorders` view table and ordered by sales in a descending order, as shown below.
<br />

![Image 38 - top-selling products by total sales.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/56abzz4hd98o9woumfv8.png)
<figure><figcaption>Image 38 - top-selling products by total sales.</figcaption></figure>
<br />
When using DbVisualizer, you can present data from any table in a configurable chart displayed in a line, bar, area, or pie chart. To activate the chart view, click the rightmost button in the result toolbar, as shown below.
<br />

![Image 39 - activating chart view.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sphenpv4ew9ldrh7ub4k.png)
<figure><figcaption>Image 39 - activating chart view.</figcaption></figure>
<br />
When switching to the chart view, DbVisualizer automatically picks the first date or text column as the `X-axis` for the chart and the first numeric column as the `Y-axis`. In the following example, the `item_number` is the X-axis, and `Sales` as Y-axis, as shown below.
</figcaption></figure>
<br />

![Image 40 - top-selling products by total sales line chart.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/19hor45al4cmf7rxdpu7.png)
<figure><figcaption>Image 40 - top-selling products by total sales line chart.</figcaption></figure>
<br />

## Changing Chart Type
To change the chart type, right-click on the chart and select the chart type option. The screenshot below shows our chart is in line form.
<br />

![Image 41 - changing chart type on DbVisualizer.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/55bxuzm0mo44gjkdma5i.png)
<figure><figcaption>Image 41 - changing chart type on DbVisualizer.</figcaption></figure>
<br />
We can convert the line chart into a bar chart by selecting the Bar option as shown below.
<br />

![Image 42 - top-selling products by total sales bar chart.](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5wa48iakiwcc9yk7ef1j.png)
<figure><figcaption>Image 42 - top-selling products by total sales bar chart.</figcaption></figure>
<br />
The chart shows Airpods as the top-selling product with 2400 in sales and Water Filter as lowest selling product with 184 in sales.

## Conclusion
In this article, you have learned how to design, build, and normalize a PostgreSQL database from scratch. You have also learned how to connect Postgres to a SQL client, create a database, run SQL queries and convert table data into charts. To learn more about database development and follow the newest trends in the database space, make sure to follow [the DbVisualizer blog](https://www.dbvis.com/thetable/), and we will see you in the next one.

## About the author
Bonnie is a web developer and technical writer creating easy-to-understand technical articles.