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 b…


This content originally appeared on DEV Community and was authored by DbVisualizer

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.


Image 1 - Quick Database Diagrams

Image 1 - Quick Database Diagrams



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

Image 2 - 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.

Image 3 - 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.

Image 4 - creating items table

Image 4 - creating items table



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

Image 5 - items table diagram

Image 5 - items table diagram



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

Image 6 - adding items table fields

Image 6 - adding items tablefields



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.

Image 7 - 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.

Image 8 - 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.

Image 9 - items table fields, customers table fields, orders table fields and their diagram

Image 9 - items table fields, customers table fields, orders table fields and their diagram



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.

Image 10 - 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.

Image 11 - 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.

Image 12 - 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.

Image 13 - 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.

Image 14 - 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.

Image 15 - 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.


Image 16 - opening database connection tab tree.

Image 16 - opening database connection tab tree.



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

Image 17 - opening database tab tree.

Image 17 - opening database tab tree.



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

Image 18 - 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”

Image 19 - filling database fields.

Image 19 - filling database fields.



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

Image 20 - 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.

Image 21 - 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.

Image 22 - 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.

Image 23 - 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.

Image 24 - 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.

Image 25 - 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.

Image 26 - 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.

Image 27 -data files.

Image 27 -data files.



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

Image 28 - 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.

Image 29 - 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.

Image 30 - 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.

Image 31 - 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.

Image 32 - 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.

Image 33 - orders table data.

Image 33 - 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.

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.

Image 34 - opening SQL editor in DbVisualizer.

Image 34 - opening SQL editor in DbVisualizer.



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

Image 35 - 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.

Image 36 - totalorders view created in DbVisualizer.

Image 36 - totalorders view created in DbVisualizer.



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

Image 37 - totalorders view table data

Image 37 - 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.

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.

Image 38 - 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.

Image 39 - 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.


Image 40 - 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.

Image 41 - 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.

Image 42 - 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, 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.


This content originally appeared on DEV Community and was authored by DbVisualizer


Print Share Comment Cite Upload Translate Updates
APA

DbVisualizer | Sciencx (2023-02-27T15:54:17+00:00) How To Design And Build A Database In Postgres. Retrieved from https://www.scien.cx/2023/02/27/how-to-design-and-build-a-database-in-postgres/

MLA
" » How To Design And Build A Database In Postgres." DbVisualizer | Sciencx - Monday February 27, 2023, https://www.scien.cx/2023/02/27/how-to-design-and-build-a-database-in-postgres/
HARVARD
DbVisualizer | Sciencx Monday February 27, 2023 » How To Design And Build A Database In Postgres., viewed ,<https://www.scien.cx/2023/02/27/how-to-design-and-build-a-database-in-postgres/>
VANCOUVER
DbVisualizer | Sciencx - » How To Design And Build A Database In Postgres. [Internet]. [Accessed ]. Available from: https://www.scien.cx/2023/02/27/how-to-design-and-build-a-database-in-postgres/
CHICAGO
" » How To Design And Build A Database In Postgres." DbVisualizer | Sciencx - Accessed . https://www.scien.cx/2023/02/27/how-to-design-and-build-a-database-in-postgres/
IEEE
" » How To Design And Build A Database In Postgres." DbVisualizer | Sciencx [Online]. Available: https://www.scien.cx/2023/02/27/how-to-design-and-build-a-database-in-postgres/. [Accessed: ]
rf:citation
» How To Design And Build A Database In Postgres | DbVisualizer | Sciencx | https://www.scien.cx/2023/02/27/how-to-design-and-build-a-database-in-postgres/ |

Please log in to upload a file.




There are no updates yet.
Click the Upload button above to add an update.

You must be logged in to translate posts. Please log in or register.