In this chapter, we are going to make an e-commerce app. An e-commerce app is a website where people buy and sell goods. For example, an online book store is an e-commerce app where people can buy and sell books. Our e-commerce app is called LWMart.

The LWMart homepage shows a list of items for sale. A logged in user can add items to the user's cart. The user can then go to the cart page and order the items in the cart. Finally, the user can visit the orders page to view the history of orders that the user placed.

Architectural Overview

LWMart stores users, items, carts, and orders in the database. The database data is stored in a computer called db.com. The database server, serve_db, also runs in db.com. The LWMart webserver is a Python application that runs in a separate computer called lwmart.com. When lwmart.com receives an HTTP request from a client, lwmart.com queries the database in db.com as necessary and sends a HTTP response back to the client.

Database Schema

Let's consider how we want to store users, items, carts, and orders in the datbase. Let's reuse the users table from the LWBlog database. The items table contains the ID, item name, the item price, and the item description. Here is an example:

id	name	price	description
101	Smartphone	800	The best phone ever!
202	Teddy Bear	15	Very fluffy!
303	Shoes	79	The most comfortable shoes ever made!

Every user has a cart which contains 0 or more items. In database terminology, the items and the user (cart) has a many-to-one relationship. We could store the cart items in the users table, but SQL experts usually recommend creating a separate table to store this assocation. We also need to store some other data, such as the quantity of the item in the cart. Here is an example table called cart_items:

user_id	item_id	quantity	index
10201	101	1	1
10201	303	2	2

The index is the location of the item in the cart relative to other items.

When the user places an order, LWMart stores the order information in the orders table. Here is an example:

id	user_id	total	created
124098	10201	78	2021-04-21T07:34:79Z
098344	25024	830	2023-02-21T07:34:79Z

Each order has a unique ID, the ID of the user that placed the order, the total order amount, and the date when the order was placed.

We also need to store the items in the order. This is also a many-to-one relationship. Here is an example table called order_items:

order_id	item_id	quantity	price
124098	303	2	39
098344	101	1	800
098344	202	2	15

The price is the price of the item at the time the order was placed.

The name of the database that contains the users, items, cart_items, orders, and order_items tables is called lwmart.

Retrieving the Home Page

When users visit lwmart.com, the browser sends the following request:

GET / HTTP/1.1

When the serve receives the request, it constructs the home page and sends it back to the client. The home page consists of all the items that are currently for sale. The server retrieves the items from the database by issuing the following request:

SELECT id,name,price,FROM items

Each item HTML should include the link to the item page. For example, if the item ID is 202 and the item name is "Teddy Bear," then the corresponding HTML snippet might be:

<h3><a href='/items/202'>Teddy Bear</a></h3>

Which appears as follows:

Teddy Bear