HNM Style
project / March 14th, 2024
Background
HNM Style is a full-stack E-commerce web application that displays a list of shopping items for sale from a database. The image for each item is rendered in the home page, and a customer user can get quick details on item name, brand, and gender by hovering their mouse over the image. Once a user registers a Customer account and signs in, they are able to add or remove items from their shopping cart and edit their billing information. If a user is signed in with Owner access, they are able to add items, edit the details of an existing item, or delete an item from the list of items for sale.
This application is the final project for the University of Calgary Software Engineering Masters course ENSF 608 (Databases), built with teammates Nikhil Naikar and Hannah D'Souza.
Purpose
For the final project for the Software Engineering Masters course ENSF 608 (Databases), students worked in teams of three to implement an application that uses a database for any real-world scenario, with the intention of applying our knowledge of database design and SQL queries that we learned in the course.
For our project problem, we devised the following premise:
The owner of a casual wear designer and retailer wishes to modernize and enhance their company operations by shifting their business model from traditional retail to E-commerce. To implement this change, a database is required to organize transactions, inventory, and customers. For each order, a customer should be able to add items of their choosing. In addition to the list of items for the order, the order should also contain the total price of the items, a unique identifier, the date of purchase, and an activity status to indicate if the order is completed, pending, or canceled. According to the owner, each customer should provide a first name, last name, email address, and password during the signup process and have an ID assigned to them upon signup completion. To facilitate and complete transactions, customers must provide a payment method. Because the company is still in the early stages of their transition to E-commerce, the owner has specified that only credit cards will be accepted. To bill a customer via credit card, information about the card number, expiry date, and the 3-digit Card Verification Code is required. Items displayed for purchase should list the item name, brand, color, price, and item type.
Concepts and Tools Used
Python
Flask
Jinja
SQLAlchemy
Bootstrap
HTML
CSS
Heroku
DB Browser
SQLite
Database Design
Process
The project includes 4 submissions:
A project proposal including the problem definition, proposed solution, proposed final outcome, and proposed functionalities.
A project EERD (Enhanced Entity-Relationship Diagram) for our proposed database application illustrating the different entities and relationships in the database.
A project implementation demonstration, where the group demonstrates a complete implementation of our project to the Instructor and ensure the database is populated according to the proposal and EERD.
A project final report detailing the project design and all SQL queries used in the project.
Here is our final EERD:
Our solution is a full-stack web application in which the back-end is built using the Flask web framework, the front-end is built using modified HTML and CSS templates from BootstrapMade (https://bootstrapmade.com/sailor-free-bootstrap-theme/), and the database is created in SQLite using the SQLAlchemy library. All of these components are connected using Python. The SQLAlchemy library assists in mapping Python classes to database tables, while also providing functions that execute SQL queries without having to write lengthy SQL code. When the guest user visits the home page, the application will query for all items from the items table in the database and display them in the home page:
Each item will also have a type, and the different types (eg. Accessories, Bottoms, Footwear, Tops) will be displayed above the items. When the user clicks one of the item types, the application HTML template will automatically show all items with that type:
When a user hovers their mouse over an item, the name, brand, price, and gender type for the item will be displayed.
When the user clicks the Sign Up button in the header, a form will appear and the user can sign up by entering their username, email password, phone number, address, and billing info.
There are two types of users.
An Owner is responsible for maintaining the content of the website and will be able to sign in, add items to the home page, edit item information, delete items from the home page, and log out.
A Customer will be able to sign in, add items to their order, view their order, remove items from their order, submit an order, edit their billing information, and log out.
If a user visits the website and are not signed in as either an Owner or Customer, they will only be able to view the items, sign up for an account, or log into an existing account.
The user will also have the choice to sign up as an Owner or Customer (ideally in a real app a guest should not be able to sign up as an Owner, but we decided to keep the project as simple as possible). Once the submit button is clicked and the information entered by the user is verified, a user record will be added to the users table using the form information.
Below, the user is signing up as an Owner named Admin:
Once the user is signed up, they can log in using the Login form. Here, we are signing into an account with Owner-level access:
After logging in, the Owner’s username and user type will be displayed in the header above. Also notice that the header now has an Add Item link and a Logout button. If an Owner hovers their mouse over an item, they will now see icon links for editing and deleting the item.
Clicking Add Item shop will take the Owner to the Add Item page, where they can add information about the item.
When the user clicks the submit button in the Add Item page, a query will be made to the items table to see if there is already an existing item with the same name. If there is, the Owner will be redirected back to the Add Item page and there will be a message indicating to the Owner that the item already exists in the database.
Otherwise, if the name of the item entered is unique, a new item record will be created and added to the items table. The Owner will be redirected back to the home page and the new item will appear at the bottom of the page.
If the edit item button for an item is clicked, the Owner will be redirected to the Edit Item page. The form is in the same format as the Add Item form, but the information for the item that was clicked will be queried and pre-populated into the form.
Here, we are editing the price of the item we just added from $25.00 to $15.00. Once the submit button is clicked, the item will be queried by its ID and its attributes will be updated. The Owner will be redirected to the home page, where they can see the price change.
If the delete icon is clicked, the item will be queried and deleted from the items table. The user is redirected back to the home page and the item will no longer be displayed.
Once the Owner is finished with their session and wishes to log out they can click the logout button.
If a user creates a Customer account, records will be added to the users table, billing table, and the order table. For simplicity, the order number will always match the user ID.
When a user signs into a Customer account, they will have access to different functionalities. They can view their order or edit their billing information. Hovering their mouse over an item will also display an plus icon, used to add the item to their order.
Clicking the plus sign will redirect the Customer to an Add Item page, where the fields are already pre-populated and read-only (cannot be edited) and the Customer can click submit to verify the addition of the item to their order.
When the Customer clicks View Order, the items in the placed_in table that have an order number for the order linked to the Customer will be displayed. The Customer will have the option to remove items from their order by clicking the delete icon displayed with each item.
When an order is submitted, the item in the placed_in table with a matching order number will be deleted from the database, and the order will be empty. The Customer will then be redirected to a page confirming that their order has been submitted.
When the Edit Billing link is clicked, a form pre-populated with the billing information of the Customer will be displayed. Just like the Edit Item form for Owner, clicking submit will query the billing by its ID and its attributes will be updated with the information in the form.
Like Owner, when the Customer is finished with their session, they can click Logout to log out.
The changes in our final design compared to the initial design includes the following:
Instead of having three separate tables for the USER, OWNER, and CUSTOMER, we instead just have a USER table with an additional attribute called type, which will be either “Owner” or “Customer”. This change was necessary to make it easier for our Flask app to log users in, as having three different user tables made it confusing for Flask to keep track of who should be logged in and who shouldn’t.
The id for the user is the sole primary key. Shipping address attribute was removed from the USER entity.
Color was made a single-value VARCHAR attribute for the ITEM entity instead of a multivariable attribute, since it would be easier to implement.
The Revews composite attribute for ITEM was removed since there was no room to place reviews in our front-end.
There is no functionality to search a record, as it was deemed unnecessary because all our Items would be displayed at once on the home page.
A Sort-by-Price functionality was not implemented as we decided that sorting by item type (eg. Tops, Bottoms, Accessories, Footwear) would make more sense for the user.
Weight attribute was removed from the ITEM entity as it was no longer used to calculate shipping weight in the SHIPPING_PROVIDER entity.
order_num became the primary key for ORDER entity
Amount attribute was added for the PLACED_IN entity
Changed weight attribute in SHIPPING_PROVIDER to a normal attribute instead of a derived attribute.
Here is a screenshot of our database structure:
Here are screenshots of our database tables:
user Table
item Table
order Table
placed_in Table
billing Table
inventory Table
shipping_provider Table
warehouse Table
Outcome and Takeaways
Building this application was the first team project I worked on in the Software Engineering Masters program at the University of Calgary, and being able to create a full-stack application in a team environment that suited the interests of all our team members was both an enjoyable and invaluable experience. Applying the Flask knowledge I learned from creating the Coffee Connect project to this project further validated that the time I spent learning these new skills and frameworks was truly worth it.
Possible Improvements Going Forward
This app can also be further improved to reflect a real E-commerce app by adding options to filter for different fields like price and seating. Additionally, doing research on implementing a real payment option would help the app take one step closer to becoming an app that can be used for real shopping.