How To Design And Build A Database In Postgres

How To Design And Build A Database In Postgres

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 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 and download it for your operating system. You can follow this guide to install PostgreSQL on Windows, this guide to install it on Linux, and this guide to install it on macOS.

To install DbVisualizer, navigate to the DbVisualizer 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 tool to see how the items table will look inside the database.

Quick Database Diagrams.

Image 1 Quick Database Diagrams.

You specify tables, their fields, and field data types on the left sidebar, as shown below.

Specifying tables, their fields, and field data types.

Image 2 Specifying tables, their fields, and field data types.

On the right are diagrams produced by tables and their fields, as shown below.

Diagrams produced by tables and their fields.

Image 3 Diagrams produced by tables and their fields.

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.

Creating the items table.

Image 4 Creating the items table.

You can now see the items table has been added to the diagrams.

The items table diagram.

Image 5 The items table diagram.

Step 2: Add the items table fields by specifying the field names and their data types.

Adding items table fields.

Image 6 Adding items table fields.

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.

Items table fields diagram.

Image 7 Items table fields diagram.

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.

Items table fields, customers table fields and their diagrams.

Image 8 Items table fields, customers table fields and their diagrams.

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.

Items table fields, customers table fields, orders table fields and their diagrams.

Image 9 Items table fields, customers table fields, orders table fields and their diagrams.

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.

Defining items table and orders table relationship.

Image 10 Defining items table and orders table relationship.

Let us now do the same for the customers table and define its relationship with the orders table, as shown below.

Defining customers table and orders table relationship.

Image 11 Defining customers table and orders table relationship.

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.

Downloading file containing SQL queries required to create tables in a database.

Image 12 Downloading file containing SQL queries required to create tables in a database.

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.

Connecting Postgres to DbVisualizer

Step 1: Start DbVisualizer and click create a connection button as shown below.

Creating a database connection in DbVisualizer.

Image 13 Creating a database connection in DbVisualizer.

Step 2: Search and select the Postgres driver from the popup menu on the left side of your screen, as shown below.

Searching and selecting Postgres driver in DbVisualizer.

Image 14 Searching and selecting Postgres driver in DbVisualizer.

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.

Object view tab for the Postgres connection.

Image 15 Object view tab for the Postgres connection.

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.

Opening the database connection tab tree.

Image 16 Opening the database connection tab tree.

Inside the connection tab tree, open the Databases tab tree.

Opening the database tab tree.

Image 17 Opening the database tab tree.

Step 2: Right-click on the Databases tab tree and select Create Database option.

Right-clicking databases tab tree and selecting create database.

Image 18 Right-clicking databases tab tree and selecting create database.

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”

Filling database fields.

Image 19 Filling database fields.

If you look at the JohnEcommerceDatabase connection, you will see the database ecommercedb has been created.

Created ecommercedb database.

Image 20 Created ecommercedb database.

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.

SQL file containing SQL queries for creating tables in database.

Image 21 SQL file containing SQL queries for creating tables in database.

Step 2: Double-click the SQL file, which should open on DbVisualizer, as shown below.

SQL file containing SQL queries for creating tables in a database opened on DbVisualizer.

Image 22 SQL file containing SQL queries for creating tables in a database opened on DbVisualizer.

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.

Selecting connection containing the database where you want to create the tables.

Image 23 Selecting connection containing the database where you want to create the tables.

Step 4: Select ecommercedb as the database you want to create the tables.

Selecting database where you want to create the tables.

Image 24 Selecting database where you want to create the tables.

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.

Refreshing objects tree to see the tables created.

Image 25 Refreshing objects tree to see the tables created.

You should now see that all the designed tables have been created on the database we created.

Tables created on database.

Image 26 Tables created on database.

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.

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.

Overview of data files you can download to add data to the created tables.

Image 27 Overview of data files you can download to add data to the created tables.

To import data into the customers table, right-click on the customers table and select the Import Table Data option.

Importing data into the customers table.

Image 28 Importing data into the customers table.

Navigate to the directory containing the data files, select the customers data file and click open.

Selecting and opening customers data file.

Image 29 Selecting and opening customers data file.

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.

Importing customers table data.

Image 30 Importing customers table data.

Once the data has been imported, right-click on the customers table and refresh to see the table data.

Refreshing customers table to see table data.

Image 31 Refreshing customers table to see table data.

Open the table and navigate to the Data tab. You should be able to see the imported data, as shown below.

Imported customers table data

Image 32 Imported customers table data.

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.

The orders table data.

Image 33 The orders table data.

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.

1 CREATE VIEW TotalOrders AS
2 SELECT orders.order_id, customers.customer_firstname,
3 customers.customer_lastname, items.item_name,
4 items.item_category, items.item_price, orders.items_bought,
5 customers.customer_address, items.item_price *
6 orders.items_bought AS spend
7 FROM orders
8 INNER JOIN customers ON
9 orders.customer_id=customers.customer_id
10 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.

Opening the SQL editor in DbVisualizer.

Image 34 Opening the SQL editor in DbVisualizer.

Once the SQL Commander opens, type the SQL query and press the (Ctrl+Enter) keys to run the query.

Running SQL query in DbVisualizer.

Image 35 Running SQL query in DbVisualizer.

If you open the views tab tree, you should see a view called totalorders has been created.

The totalorders view created in DbVisualizer.

Image 36 The totalorders view created in DbVisualizer.

Open the view, and you should see more information about the orders.

The totalorders view table data.

Image 37 The totalorders view table data.

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.


1 SELECT totalorders.item_name, SUM(spend) AS sales
2 FROM totalorders, items
3 WHERE totalorders.item_name=items.item_name
4 GROUP BY totalorders.item_name
5 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.

Top-selling products by total sales.

Image 38 Top-selling products by total sales.

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.

Activating chart view.

Image 39 Activating chart view.

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.

Top-selling products by total sales line chart.

Image 40 Top-selling products by total sales line chart.

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.

Changing chart type on DbVisualizer.

Image 41 Changing chart type on DbVisualizer.

We can convert the line chart into a bar chart by selecting the Bar option as shown below.

Top-selling products by total sales bar chart

Image 42 Top-selling products by total sales bar chart.

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, read more about PostgreSQL for DbVisualizer, and evaluate DbVisualizer Pro for free during 21 days, 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.