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…

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.

coding relational-databases software-development database database-design

To make the most out of the benefits of offshore software development, you should understand the crucial factors that affect offshore development.

Check out these five criteria for the selection of your software vendor, and you will never regret having the wrong quality product made for you.

Vinew Technologies focused majorly over ERP Software services and POS Software services, Web & Application development services & Woo commerce and Wordpress easy-to-use, multipurpose social media plugin for WordPress.

Wrestling betting software development solutions from expert Wrestling betting app and software development company delivers ultimate betting experience. Chat with us.

AppClues Infotech is a top Mobile App Development Company in USA building high-quality Android, iOS, and Native apps for Startups, SMBs, & Enterprises. Contact us now!