The IBuyAdventure .NET Database

IBuyAdventure is driven by a SQL Server 7 or 2000 database, with four tables ( Accounts , Products , ShoppingCarts , and Orders ) as shown in the Figure 24-5:

Figure 24-5:

The business objects encapsulate each of these tables, so the ASP.NET pages never perform direct database access.

The Accounts Table

The Accounts table is used to store the login information for registered customers and has the following structure:

Column Name	Type	Length	Description
CustomerName	nvarchar	50	The name or email address of the registered user . This field is used as the key against all of the tables, and should therefore be unique.
Password	nvarchar	30	The password specified by the user during registration.

The Orders Table

The Orders table stores a summary of all the orders made by customers and has the following structure:

Column Name	Type	Length	Description
CustomerName	nvarchar	50	The name or email address of the registered user. This field is used as the key against all of the tables, and should therefore be unique.
Ordered	datetime	8	The date the order was placed.
TotalValue	float	8	The total value of the order.

When a user hits the Confirm Order button, and moves to the checkout page to confirm an order, an entry is added to this table. The individual items within the shopping cart are not saved to the database when an order is confirmed, although this would be a requirement for a commercial application.

The Products Table

The Products table contains a list of all products that a customer can purchase from IBuyAdventure. The table has the following structure:

Column Name	Type	Length	Description
ProductID	int	4	A unique ID for the product.
ProductCode	nvarchar	10	The unique code for the product.
ProductType	nvarchar	20	The category for the product.
Product Introduction Date	small datetime	4	The date when the product was first added to the catalog.
ProductName	nvarchar	50	The name of the product shown in the catalog.
Product Description	nvarchar	255	A description of the product.
ProductSize	nvarchar	5	The size of the product.
ProductImageURL	varchar	255	The URL of the image to display for the product.
UnitPrice	float	8	The price for this product.
OnSale	int	4	A flag to indicate whether or not the unit price is a sale price: 1 = on sale, 0 = not on sale.
Rating	float	8	A rating out of five for this product in terms of overall quality.

IBuyAdventure has slightly less than 50 products, grouped in 12 categories.

The ShoppingCarts Table

The ShoppingCarts table holds all of the current product details for each user's shopping cart. The table has the following structure:

Column Name	Type	Length	Description
ShoppingCartID	int	4	Auto-generated ID field.
ProductCode	nvarchar	10	The unique code for the product.
ProductName	char	50	The name of the product.
Description	nvarchar	255	A description of the product.
UnitPrice	money	8	The price for this product.
Quantity	int	4	The number of units wanted.
CustomerName	nvarchar	50	The name or email address of the registered user who currently has the specified product in their basket . If the user is not currently registered or logged in, this is a GUID to represent the anonymous user.

Every time an item is added to a user's shopping cart, an entry is added to this table.

Important

The IBuyAdventure sample application does not clean up the database, or remove rows that are associated with sessions that have expired . This would need to be done in a production application. You could handle the Session_OnEnd event and do your database cleanup there.