Home > Articles > Data

Reap the Benefits of Web Caching, Part 3: Database Integration

  • Print
  • + Share This
If you've followed the first two articles in this series and implemented their recommendations, your web pages are almost perfect. Now it's time to add a back-end SQL database. In the final article of the series, Ivan Pepelnjak shows you how to cache the content generated from your SQL database, using a small but highly optimized order-entry system.
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.

 

  • + Share This
  • 🔖 Save To Your Account