Designing Relational Database in Example

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…

Image for post

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.

    Image for post

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.

Image for post

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.

Image for post

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

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Offshore Software Development - Best Practices

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

5 Core Criteria for Selecting Software Development Company - TopDevelopers.co

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.

ERP Software Services, POS Software Services , Application Development

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 | WWE Betting Software Developers

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

Hire Best Custom Software Developers in USA

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!