The Island Hopper News sample Level C database is a SQL Server database called Classifieds.sql. When you run the database setup portion of the Level C setup, the database is created and filled with data for you. It consists of the tables described in the following list.
| Table name | Description |
| Advertisements | Stores classified ads. |
| Categories | Stores categories for classified ads. |
| CustomerPasswords | Stores passwords for customers. |
| Customers | Stores customer information. |
| InvoiceDetails | Stores information about each ad on an invoice (a customer can have more than one classified ad on the same invoice). |
| Invoices | Stores information about an invoice. |
| Payments | Stores information about customer payments. |
| Products | Stores information about Island Hopper News company's products. Currently, there are only two: a classified ad with 100 or fewer words, and a classified ad with 100 to 200 words. As the Island Hopper News company expands, it could offer additional products, each of which would be stored in the Products table. |
| TakeANumber | Assigns unique ID numbers for ads, categories, and customers. |
Level C reuses the Advertisements, Categories, and TakeANumber tables from Level A. For more information about these tables, see Level A — Database Schema in this chapter. Changes to the tables or their data types are summarized in the following descriptions.
The Advertisements table in Level C is almost the same as the Advertisements table in Level A, with the following differences:
The Categories table in Level C is the same as the Categories table in Level A, except that Number data types become integers and the Text data type, used for the Name field, becomes a varchar data type.
The TakeANumber table in Level C is the same as the TakeANumber table in Level A, except that the NextNumber field data type changes from Number to integer and the PropertyGroupName field data type changes from Text to varchar.
The fields and data types of the tables added to the database for Level C are shown in the following tables.
Fields in the CustomerPasswords Table
| Field name | Data type | Description |
| CustomerID | integer | Unique customer identifier. |
| Password | varchar | Customer password. |
Although it has a one-to-one relationship, the CustomerPasswords table is separate from the Customers table. This separation makes it possible for you to implement different security settings for each table.
Fields in the Customers Table
| Field name | Data type | Description |
| Address | varchar | Customer's street address. |
| City | varchar | Customer's city. |
| Country | varchar | Customer's country. |
| CustomerID | int | Unique customer identifier. |
| varchar | Customer's e-mail address. | |
| FirstName | varchar | Customer's first name. |
| LastName | varchar | Customer's last name. |
| PhoneNumber | varchar | Customer's phone number. |
| PostalCode | varchar | Customer's postal code. |
| State | varchar | Customer's state. |
Adding billing functionality to Level C makes it necessary to track information about each person who places an ad, so a Customers table was added to the database.
Fields in the InvoiceDetails Table
| Field name | Data type | Description |
| Description | varchar | Text description of the item associated with this invoice entry. In the sample, this field contains the title of the ad associated with this invoice entry. |
| DetailID | int | Unique identifier for each item listed on the invoice. In the sample, this field contains a unique identifier for each ad. |
| InvoiceID | int | Unique identifier for each invoice (primary key). |
| Price | decimal | Price of product or service; in the sample, price of ad. |
| ProductCode | varchar | Unique identifier for an entry in the Products table. In the sample, this field indicates word count, because 100-word ads are priced differently from 200-word ads. |
| Quantity | int | Answers the question, "how many?" In the sample, this field contains the number of words in the ad. |
| UnitOfMeasure | varchar | Answers the question, "of what?" In the sample, this field contains the duration of ad. |
Fields in the Invoices Table
| Field name | Data type | Description |
| CustomerID | int | Unique identifier for a customer. |
| Description | varchar | Describes the product to which the invoice applies. In the sample, this field is always "Classified Ad Invoice." |
| InvoiceDate | datetime | Date of the invoice. |
| InvoiceID | int | Unique identifier for the invoice. |
| InvoiceTotal | decimal | Total amount of invoice. |
| Reference | varchar | Additional pertinent information. In the sample, this field contains the unique identifier of the ad, for reference. |
Both Invoices and Invoice Details are general purpose invoice tables. In the sample, these tables are used as follows: when you place an ad, the sample generates an invoice and the line item on the invoice corresponds to the ad. For the purposes of the sample, each invoice only has one invoice detail that corresponds to one ad. However, in the context of a larger application (keeping in mind that the sample represents only one part of such an application), you can imagine an invoice with multiple line items (invoice details), each representing different goods or services from the Island Hopper company. Designing the invoice tables this way shows how a company can use one invoicing scheme throughout.
Fields in the Payments Table
| Field name | Data type | Description |
| CardExpDate | datetime | Expiration date of credit card. |
| CardNumber | varchar | Credit card number. |
| CustomerID | int | Unique identifier for the customer. |
| InvoiceID | int | Unique identifier for the invoice to which this payment applies. |
| PaymentAmt | decimal | Amount of payment. |
| PaymentDate | datetime | Date of payment. |
| PaymentID | int | Unique identifier of this payment. |
| PaymentMethod | varchar | Payment method. |
Fields in the Products Table
| Field name | Data type | Description |
| Description | varchar | Description of the product. In the current implementation of Island Hopper News, the product is a classified ad, either 100 or 200 words. |
| ProductCode | varchar | Unique identifier for a product. |
| UnitOfMeasure | varchar | The way a product is measured for invoicing. In the sample, this field contains the ad duration. |
| UnitPrice | decimal | Unit price for a product. In the sample, this field contains the ad price (100 and 200-word ads have different prices). |