Designing Relational Database in Example

In this story I want to share with you a basic relational database which helped me to understand ideas of organizing data for relational databases. If you are a newbie to designing relational databases, this material is for you.

Photo by Campaign Creators on Unsplash

› Preface

Several years ago I was a junior developer with high ambitions to create web apps. Once I got an idea to analyze all money that I spend to understand my expenses better. I needed an application which could store payments, show statistics graphs, and give more than just a colourful circle graph with all payments in a month.

For these reasons, I decided to develop an app and include all the features that I wanted.
I am going to discuss with you the design of a relational database, which I made for this app.

› What data do I need to store?

I explored this question with an example. I imagined that I bought the “Martian” book in the book shop, which located in the book shop “Book lovers”. Then, I paid for the book 5$ in cash on 20 Aug 2020 at 3:04 pm.

Accordingly, at this moment, there are several parameters of the purchase:

• the name of the product — the “Martian” book,
• the price — 5$,
• the location of the shop — “Book lovers” shop,
• the payment type — cash,
• the currency — USD,
• date and time — 20 Aug 2020, 3:04 pm.

Figure 1. Purchase parameters.

I had been thinking about whether I need to specify an exact product or not. In a book shop, you can buy many things besides books. And what about shopping in a supermarket? A list of products can be super long.

› Organizing Tables

I decided to create an abstract model for a product. I divided this parameter (product name) into category and subcategory tables, and connected them by primary and foreign keys. For the preceding example, the category is shop and subcategory is book shop.

Figure 2. Two new tables: categories and subcategories.

Another option is to store categories and subcategories in one table. Although storing both categories and subcategories simplifies the overall model, setting them in one table can complicate selecting categories from the table — an example of this table in the Script section.

Payment methods’ (cash or card) and currencies’ (USD, EUR, etc) values are known and immutable. Therefore, for storing the payment methods and currencies, I created methods and currencies tables respectively.

Figure 3. Two new tables: methods and currencies.

Price, location, date and time are always different. There is no need to store these parameters in separated tables.

The last entry is payments, which contains all the preceding parameters.

Figure 4. A new table: payments.

› Script

The next step is to define columns and constraints.

The subcategories table contain an additional category_id column, which is a foreign key constraint, for connecting to the categories table.

All id columns have the serial pseudo data type. Using the integer data type is also correct.

A name column in categories, currencies, and methods are unique across the tables. A name of subcategory is not unique because a subcategory can refer to many categories.

By default, all columns in PostgreSQL are null. I added not null constraint to specific columns, in which information is valuable and required for analysis.

Execute the following script to create the tables:

I included drop table if exists table_name cascade to make it easier to apply new changes. Therefore, when you add an attribute, for example, you do not need to destroy a table manually.

An alternative table for storing categories and subcategories is:

• id is an identifier of both a category and subcategory.
• name is a name of category and subcategory.
• category_id refers only to a category.

An example of the products table:

Table 1. The products table filled with random values

› Result

The payments table is a key table of this app. This table connects to categories, subcategories, methods, and currencies tables by foreign keys.

Figure 5. The final relational database model.

› Further Reading

PostgreSQL Foreign Key

PostgreSQL Primary Key

PostgreSQL Not-Null Constraint

PostgreSQL UNIQUE Constraint

Using PostgreSQL SERIAL To Create Auto-increment Column

› About Author

Jane is a Go programmer and technical writer in software engineering. She has been writing technical material for 5 years in both English and Russian. She completed her specialist degree in Information Security from Novosibirsk State Technical University, and specializes in the information security of automated systems. You can follow her on Twitter and see her other written work at publications.enthusiastic.io.