Shoplist development notes
Written on February 11th, 2022 by Terry
Price Tracking
The purpose of this web application is price tracking. Unlike Camelcamelcamel which tracks Amazon items only, the Shoplist aims to support multiple online shops, and users can maintain their own lists.
Current version supports Amazon US and BestBuy.
After creating a user account and logged-in, head to the “⨁ Add” section, user just need to copy and paste the URL of an item to start tracking its price.
Also features a price history chart to see the price trend!
Database design
In the Password manager project, the database design is rather basic and size was be limited, only contains one table to store the url, encrypted password, login and optional note. However the Shoplist database requires tables to store user login data, item data, prce history, etc. Lot of them will be accumulating. MySQL database is used this time. After all, Password manager was designed for single user only, but Shoplist is for more users.
During development, MySQL workbench is a handy tool to monitor the database behavior.
Software design is always about how to perform a task efficiently and scalability. One example is when storing the price data of an item, which might belongs to two different users. A one-to-many design will eliminate lots of duplications.
| Items | Links | Sources | Price history |
|---|---|---|---|
| ➀ Item1, UserA | Items ➀, Sources ➊ | ➊ URL(1) | Sources ➊, 1-Jan, $ |
| ➁ Item1, UserB | Items ➁, Sources ➊ | Sources ➊, 2-Jan, $ |
Back end coding
Flask works well with SQLAlchemy library that helps to manage database, but this time I wanted to get familiar with SQL commands and design on a more fundamental level, I decided to code without the help of an extra library. However it wasn’t without any issues. The connection timeout is worth mention, as it seems not appear when using library (such as SQLAlchemy). A common approach is to ping the database server, and reconnect if the connection is lost. Also need to make sure ICMP communication is allowed, between the Python/Flask server and MySQL server. Which, also lead me to learn more about AWS server config, as I setup a MySQL server on AWS using RDS.
Interface design
Some items are known to have issues, for example, some products listed on BestBuy use per month installment as default price, some special discount event on Amazon creates unusual item pages. It’s hard to keep those items supported at the moment. Therefore users are asked to fetch and check the item properties before confirm to add it to the database.