Starting my mini SQL project

I've been making a mini-project for myself to practice my SQL skills. The project was a simple Online retail application database. I thought I will do something it had a lot of relevant.

In the beginning, I was thought to do a railway database system but when I googled some tutorials or examples I did not get much-satisfied results for the project. I found plenty of example code of making an online store database. So I decided to choose to pick this project.

The design of the database was based on this blog post here. The blog post gave a great rundown on how to design an online store backend. The main issue I had is that I have been practising SQL since the tutorial so I struggled to remember the commands used to make tables and making the rest of the database. I simply googled the reference for SQL commands which gave me a good refresher on the commands I want to use. I first created a table with the item or products. The table had one column of the id of the item which I set up as the primary key. The second column was the name of the product. Also, I made a purchase table which contains the purchases of customers. With the columns with the first on being an id again and the customer account id. I made the first column containing the if as a primary key.

This is the bit have done. So I should first the rest of the project later on. Next, I would have moved on to making a customer table in which the first column would have been id set as a primary key and three other columns which include name email and password. This will do most of the work of making the online store backend.

One issue is that just having a purchase table is that it gives a limit to the database on how much purchases can be completed. This is solved by making an intermediate table connecting to table which allow for making unlimited purchases on the database. The table can be called purchase item which is connected to the purchase and item tables. With 4 columns purchase id, product id, number of items, total price. with purchase id and product id as the primary and foreign key to helping connect to the other tables. less important columns can be added like the number of times purchased, total price. and so on.

The blog post basically gave me a step by step plan on how to design to the database so it's highly recommended that you check it out. The blog post does not give lines of code to copy and paste to the database. But the pictures and explanation make it clear what you need to do. So just checking up the commands will get you covered.