E-Commerce Solution with Flask & MySQL
Introduction
From startups to small business all the way to huge brands, businesses are benefiting from e-commerce. Businesses are able to allow easy access to their product or service thanks. Even individuals are able to start their own e-commerce business what little efforts these days. So it’s no surprise that we see it everywhere around us. We can see on how nowadays unicorn companies that derived its identity for being E-commerce such as Tokopedia, Bukalapak, Shopee,etc. have boosted in popularity recently. It is no wonder how SMEs wanted to start jumping towards “E-commerce” type of business, that is where our project comes in.
We have interviewed a company namely PT. Lintas Data Indonesia, which is a SME, in which they play not only in audio visual but also Video Conferencing devices and services. Now, they are still doing business using traditional way. They have a website whereby products are only displayed yet the rest of the transactions need to be dealt with face-to-face meeting even if the supposed sold products are service type of products. We believe that it is rather insufficient considering the trend of E-commerce and of course the existence of database to manage and simplify not only transactions online but also better manage their present stocks and possible ROI considering the efficiency of the steps taken compared to the traditional way. Furthermore, in the status quo they are still using traditional way of enlisting products that is manually. This way resulted in miscommunication due to how traditional way is more prone to errors. Considering on how they work as not only a primary distributor, this means they are also responsible for products that are not theirs.
So, based on the aforementioned story our project will address several problems:
- Efficiency of transactions of products
- Better management of stocks of products using database
- Better management of different stores and their products
After further discussions of the problems, we have concluded that we will create an E-commerce website. We believe this is the best possible solution as the E-commerce website will:
- Be able to track all kinds of transactions, its users and payment methods in which it will be directly stored inside the database. This will also eliminate the long, arduous process of meeting up with people to confirm payments.
- Be able to better manage and supervise the availability of products that is existing.
- Be able to better manage different stores and their respective products.
For all those reasons we created an e-commerce in the form of a web application.
Considering on how this is a group project, we each have done our part in finishing this project.
Database Design
Entity-Relationship
Relations
This is our entire database. It consists of 12 tables in which all are inter-connected to each other.
The first table is to store address of users that are signed in to our website. There are 1 Foreign key inside this table, and there is one Primary key which is the address_id. All the columns are NOT NULL except city_id, line2 and line3 in which it is optional depending on how long the address would be. The primary key is auto-incremented to makes things easier to be sorted out. The foreign key city_id is to access the data regarding the country and city of the user.
address (address_id, line1, line2, line3, city_id, postal_code)
foreign key city_id references city(city_id)
This table to store the methods of payment for the users. There is only 1 Primary key that is payment_method_id. By default we already put 3 values which is visa, mastercard and transfer.
payment_method (payment_method_id, name, description)
This table stores the admin data. There is only 1 primary key that is admin_id.
admin (admin_id, username, email, password)
This table stores the user data. There is 1 primary key that is user_id and 1 Foreign key that is the address_id. Other than the Primary and Foreign Keys, we also used Unique Keys for email and store_id of the users as every user should have a different email and own different stores. We consider on how there is a possibility of a user having multiple addresses. Most of the columns except ‘lastname’, address_id, is_active and store_id is set to NOT NULL because there are some circumstances where a person only have a first name not everyone has a store, not all is always online and have home address.
user (user_id, email, firstname, lastname, password, joined_on, address_id, is_active, store_id)
foreign key (address_id) references address (address_id)
unique (email)
unique (store_id)
This table is to store data for each store registered in our database. There is 1 primary key that is the store_id and 1 foreign key that is the catalog_id. We associate the catalog_id as the store table’s foreign key because as mentioned before, each store have their own catalog(s). Most columns are set to NOT NULL except catalog_id because there could be a new store registered that has not created its own catalog yet.
store (store_id, store_name, about, joined_on)
unique (store_name)
This table is the category table that stores a variety of categories that our web offers. This table consist of 1 primary key that is the category_id and no foreign key. This table is simple because we think that the sufficient amount of data required for a category is only its id and name. As there are only little data stored in this table, all the columns are set to NOT NULL because a category can’t have any of these data (id & name) empty.
category (category_id, category_name)
This table is the order_item table stores each item that the user has added to their shopping basket/shopping cart to purchase. There is 1 primary key that is the order_item_id and 1 foreign key that is the product_id. The product_id foreign key is for the order_item table to access the data of the product that the user has added to their shopping cart. This order_item table is to record the data and total price of each item in the user’s shopping cart/basket.
order_item (order_item_id, product_id, quantity, total_price)
foreign key (product_id) references product (product_id)
This table stores the product data. This table has 1 primary key which is product_id and 2 foreign keys which are category_id from Category table and store_id from Store table. Each product is separated into a category of their own and since a certain type of product can be sold in numerous different stores, the product references its store.
product(product_id, name, brand_id, description, stock, price, available)
foreign key (category_id) references category (category_id)
foreign key (store_id) references store (store_id)
This table is to store country data which will be referenced in address table. This table has 1 primary key which is country_id and no foreign keys.
country(country_id, name)
This table is the transaction table to store the transactions that users has made. There are 1 primary key. There are two foreign keys that are the payment_method_id and the user_id. The payment_method_id is to access the payment method that the user has chosen to fulfill the payment, while the user_id is to know who did the transaction. The ordered_on is a timestamp to show when the transaction occurs.
transaction (transactions_id, payment_method_id, user_id, order_item_id, ordered_on)
foreign key (payment_method_id) references payment_method
(payment_method_id)
foreign key (user_id) references user (user_id)
This table is the city table in which it has one primary key being city_id and one foreign key being country_id. As multiple cities can exist in one country hence country_id is needed to link this table with the country table.
city(city_id, name, country_id)
foreign key(country_id) references country(country_id)
unique (name)
This table consists of one primary key which is the id and 2 foreign keys which are the transaction_id and the order_item_id. This table is created so that a transaction can be done and consists of multiple orders of items.
transaction_order_items(id, transaction_id, order_item_id)
foreign key(transaction_id) references transaction(transaction_id)
foreign key(order_item_id) references order_item(order_item_id)
Normalizations
| Attribute | Determines |
| transaction_id -> user_id, order_id and payment_method_id depends on the transaction_id in order to identify which user made the purchase and the payment method used. The order_id helps to identify which cart is the transaction referring to in order to access the data | user_id, payment_method_id, order_id |
| address_id -> city_id and country_id both depends on the address_id as the address in each country differs, and with address_id we can identify from which city and country it is from | city_id, country_id |
| user_id -> address_id depends on user_id as each user is associated with an address | address_id |
| order_item_id -> order_id and product_id depends on order_item_id as the items that each user ordered are different. The order_item_id will tell us which product the user has put in their cart alongside its quantity and price. The order_id will tell us which cart does this ordered item goes to as each cart can consists of 1 or more items. | product_id, order_id |
| product_id -> store_id and category_id depends on product_id as each product belongs to a specific store alongside the category of the product (e.g. Gadget) | store_id, category_id |
| city_id -> country_id depends on city_id as the country depends on the city data | country_id |
| order_id -> user_id depends on order_id as the orders table works as a cart and we need to identify which cart (order) belongs to which user. | user_id |
Sample Queries
- Query to get total price of all items
SELECT sum(total_price) as total
from transaction
JOIN order_item
ON transaction.order_item_id=order_item.order_item_id
- Query to update the total price of an item depending on the quantity
UPDATE order_item as o
INNER JOIN product as p
ON o.product_id=p.product_id
SET o.total_price=p.price * o.quantity
- Query to combine transaction, order_item and product to access all necessary product data
SELECT *
FROM transaction
JOIN order_item as o
JOIN product as p
WHERE transaction.order_item_id=o.order_item_id
AND order_item.product_id=product.product_id
- Query to get all users that have an address in Indonesia
SELECT firstname, lastname
FROM user
JOIN address
JOIN country
WHERE country.name=’Indonesia’;
- Query to know the total number of products that are in each category
SELECT category_id, count(*) ‘total’
FROM product
GROUP BY category_id;
User Interfaces
The two pages above are the user registration page, the first one is where you create the account for the user, inputting firstname, lastname, email, and password. The second page is for setting up the address of the user.
This is the main page where the products being sold are displayed. The user can click the green button on located on every product to add that product to the user’s shopping cart.
This is the page that displays the user’s shopping cart. The user can proceed to the payment page by clicking the payment button located on the bottom right.
This page can only be accessed by users that has created an account and is logged in can access, this page is for creating a store if the user hasn’t created a store yet.
Database Security
Our project relies on a 3rd party presence which is heroku. Heroku acted as the server in which we connect our database to. Hence, despite only having one account to access the database in which the account attain almost most roles, yet the account does not have root permission so they will not be able to wreck the server even if there is a breach. Of course, in addition Heroku in itself already has a lot of security certificates that comes along in us using them. Furthermore, we encrypted the passwords of all the users with sha256 in consideration of our users privacy if breach were to happen. To increase the security and the secrecy of the real data of the database we create a view for transactional history of users and/or stores. This means that they will receive any update of transactions from database, yet they will not be able to directly view how the database looks like and how the relations might be intertwined.