Reap the Benefits of Web Caching, Part 3: Database Integration
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The Sandbox
In part 2 of this series, you learned how to use the Last-Modified HTTP header to improve the responsiveness of your web application. That article also documented ways to calculate the correct modification date and time for dynamic web pages, including adjustments that have to be made for external libraries and data files.
To conclude this series, I’ll show you how to integrate the dynamic web pages with a back-end SQL database and implement a complete HTTP caching solution in an interactive environment. This article focuses heavily on the SQL code needed to support the HTTP caching; if you’re a web developer, you’ll find it a good starting point when negotiating your needs with your database administrator.
As always, it’s best to illustrate a solution with a working example, so we’ll implement parts of a simple order-entry system. A database supporting a very simple order-entry system needs at least these tables:
Table |
Description |
Products |
Product codes, names, descriptions, and prices |
Orders |
Order headers (user ID, shipping address, status) |
OrderItems |
Ordered items (product code, quantity, and offered price) |
- Our system will go beyond this bare minimum, including two more tables:
Table |
Description |
Users |
Used for user authentication and authorization purposes; for example, a user will be shown only the orders she has entered in the past |
Categories |
Organizes the product catalog in categories |
- Listing 1 displays the SQL definitions of all these tables.
Listing 1 Database table definitions.
CREATE TABLE Users(
UserID uniqueidentifier NOT NULL PRIMARY KEY,
Username varchar(50) NOT NULL,
Password varchar(50) NOT NULL)
CREATE TABLE Categories(
ID int IDENTITY NOT NULL PRIMARY KEY,
Name varchar(50) NOT NULL)
CREATE TABLE Products(
ProductID int IDENTITY NOT NULL PRIMARY KEY,
ProductName varchar(50) NOT NULL,
Description varchar(max) NULL,
Price numeric(10, 2) NOT NULL,
Category int NOT NULL)
ALTER TABLE Products WITH CHECK ADD CONSTRAINT FK_Products_Categories
FOREIGN KEY(Category) REFERENCES Categories (ID)
ON UPDATE CASCADE
CREATE TABLE Orders(
OrderID int IDENTITY NOT NULL,
UserID uniqueidentifier NOT NULL,
ShippingAddress varchar(max) NOT NULL,
ShipDate datetime NULL)
ALTER TABLE Orders WITH CHECK ADD CONSTRAINT FK_Orders_Users
FOREIGN KEY(UserID) REFERENCES Users (UserID)
CREATE TABLE OrderItems(
ItemCounter int IDENTITY NOT NULL,
[Order] int NOT NULL,
Product int NOT NULL,
Quantity int NOT NULL,
Price numeric(10, 2) NOT NULL,
CONSTRAINT PK_OrderItems PRIMARY KEY CLUSTERED (
ItemCounter ASC, Order ASC))
ALTER TABLE OrderItems WITH CHECK ADD CONSTRAINT FK_Items_Orders
FOREIGN KEY(Order) REFERENCES Orders(OrderID)
ALTER TABLE OrderItems WITH CHECK ADD CONSTRAINT FK_Items_Products
FOREIGN KEY(Product) REFERENCES Products(ProductID)
The web application will contain four major pages on which we’ll try to achieve as much caching as possible:
Page |
Description |
ProductCatalog |
Lists products, sorted by categories |
ProductItem |
Describes an individual product |
ListOrders |
Displays all orders entered by user who is currently logged in |
ShowOrder |
Displays the contents of the selected order |
In the following sections, I’ll show you how to adjust the Last-Modified, Expires, and ETag headers in the HTTP responses sent by the web server, based on modifications made to the back-end SQL database (assuming that you’ve already calculated the LastModified date based on information in the previous article). We’ll start with the easiest task—creating the product catalog.