Julie MacAller
Microsoft Corporation
January 1999
The Island Hopper News sample is an automated classified ads system created by a fictitious company, Island Hopper Publishing, as a test project to evaluate converting the current paper-based weekly newspaper into an online newspaper. The design team consists of the Island Hopper News editor, the Classified Ads and Accounting department heads, and two developers.
This paper describes the design of the Island Hopper database, classifieds.sql. It discusses the tables, columns, and relationships that comprise the database. It also includes a list of the stored procedures in the database and a description of what each of those stored procedures does.
The Island Hopper database is a Microsoft SQL Server database. SQL Server offers many advantages for distributed applications and is often the best choice when you are building a database for multiple user access.
The following figure shows the tables in classifieds.sql.
Notice that the primary key for each database is identified, as well as the relationships between tables. This diagram was generated using the Microsoft® Visual Database Tools from within Visual Basic.
Note The Visual Database Tools are included in each of the Visual Studio languages: Visual Basic, Visual C++, Visual J++, Microsoft® Visual InterDev™, and Microsoft® Visual FoxPro®.
This section describes the purpose of each table and provides details on the columns for each table.
The Advertisements table stores classified ads and related data. The following figure is a detailed view of the Advertisements table that shows the properties of each column.
Note The InvoiceID column identifies an invoice. A customer can submit more than one ad, and several ads can appear on the same invoice. Generating the invoice is a separate process from submitting an ad and might happen later. The InvoiceID column allows null values to account for this.
The Categories table stores a list of categories into which classified ads can be grouped. One of the business rules for the system is advertisements must be associated with a specific category. Storing categories in a database table makes it easy to get an up-to-date list of current categories. The following figure shows a detailed view of the Categories table that includes the properties of each column.
The CustomerPasswords table stores customer passwords. It is separate from the Customers table to make it easier to secure. The following figure shows a detailed view of the CustomerPasswords table that includes the properties of each column.
Note The designers used a table to store passwords instead of the Microsoft® Windows NT® security features, because they knew customers would access the system over the Internet using a browser and therefore wouldn't have NT accounts.
The Customers table stores information about customers who submit classified ads. The following figure shows a detailed view of the Customers table that includes the properties of each column.
Note The Country column was included to make the system friendly for international customers; it accepts null values so that customers aren't forced to fill in the field.
These tables work together. In the Island Hopper application, an invoice consists of one or more line items (details). Each line item denotes a separate ad. The Invoices table contains information about each invoice as a whole; the InvoiceDetails table contains information about each line item in an invoice. The following figure shows a detailed view of both tables that includes the properties of each column and a representation of the relationship between them.
The Payments table stores information about customer payments. The following figure shows a detailed view of the Payments table that includes the properties of each column.
Note The CardNumber and CardExpDate columns allow null values, because customers can pay by either check or credit card. If they choose to pay by a check, the CardNumber and CardExpDate values have no meaning.
The Products table stores information about the products offered by Island Hopper Publishing. Currently, Island Hopper Publishing offers only two products: a classified ad of 100 or fewer words and a classified ad of 100 to 200 words. These two products enforce the following Island Hopper News business rule: ads must have 200 or fewer words. Further, the product codes relate to the duration of the ad. If the ad runs for 1 week, the product code is AD-100. If the ad runs for more than 1 week, the product code is AD-200.
The developers added the Products table for future expansion. As Island Hopper Publishing grows, they could offer other products, each of which would be stored in the Products table. The following figure shows a detailed view of the Products table that includes the properties of each column.
The TakeANumber table stores blocks of unique IDs for ads, categories, and customers. It serves a utility function in the application, so it isn't related to the other tables. The following figure shows a detailed view of the TakeANumber table that includes the properties of each column.
It might appear that the TakeANumber table is an overly elaborate solution, especially when you consider SQL Server's identity columns, which are designed to permit the creation of unique IDs. In many cases, identity columns are an efficient solution. You can add the record to the database and get back the value of the identity column for the just-added record.
However, when you are using MTS, as Island Hopper News does, identity columns are not an ideal solution, because you can insert only one record at a time. You have to use the MAX command first to find the highest value. The MAX command locks the database table while it scans to find the highest value, increments that value, inserts the new record, and commits the transaction. You can't insert the next record until the lock is released. In a distributed environment like Island Hopper News, with multiple clients, identity columns do not scale well.
In Island Hopper News, the TakeANumber component gets a batch of numbers from the TakeANumber table all at once. The only time you have to wait for the database is when you need a new batch of numbers. Otherwise, the numbers are just a call to the TakeANumber component away.
The Island Hopper database contains several stored procedures that automate retrieval of customers or classified ads. They include the following:
| Stored Procedure Name | Contents |
| Customer_GetByEmail | Retrieves customer records from the database where the e-mail address matches a supplied e-mail address value. Used by the db_CustomerC component. |
| Customer_GetByID | Retrieves customer records from the database where the customer ID matches a supplied customer ID value. Used by the db_CustomerC component. |
| Customer_ListByLastName | Retrieves customer records from the database where the last name matches a supplied value for the last name. Used by the db_CustomerC component. |
| Product_GetByID | Retrieves product records from the database where the product code matches a supplied produce code value. Used by the db_ProductC component. |
| Product_ListByDesc | Retrieves product description records from the database where the product description matches a supplied product description value. Used by the db_ProductC component. |
The developers decided to use stored procedures for these retrieval operations because of the performance benefit. Stored procedures are precompiled collections of SQL statements and control-of-flow language that execute very quickly. Stored procedures are stored within a database; can be executed with one call from an application; and accept user-declared variables, conditional execution, and other powerful programming features.
Additionally, the developers were able to take advantage of the Stored Procedure Debugging feature available when Visual Studio Enterprise Edition is installed. The Stored Procedure Debugger runs in the editor window and makes it easy to debug stored procedures without leaving the Visual Studio environment.
With the Stored Procedure Debugger, you can set breakpoints and step through the procedure. You can view the values of variables and passed parameters in the Locals window. You can also drag expressions to the Watch window to track them as you step through or run the procedure. The results of SQL PRINT statements are displayed in the Output window. For more information, search online for "debugging stored procedures" in MSDN™ Library Visual Studio 6.0.