The Tables for Wild Willies CD Store

Introduction. We are going to create an on-line store for a fictitious company called Wild Willies. The store sells CDs and t-shirts. Wild Willies requires a pretty sophisticated database with a lot of inter-related tables. This page is to give a general discussion to review what is contained in the book.

Departments and Products. This section relates to the tables that underlie the departmental configuration, the products within each department and the product attributes.

Table Name	Description	Links To
Departments	This table contains fields ID for the department the name of the department a short description and the name of the image associated with each department.	This table links to the Products contained in each department through the DepartmentProducts table.
Products	This table contains fields ID for the product the name of the product a short description the name of the image the price sale date specifications sale price flag for whether the product is active	This table links to the Attribute table through the ProductAttribute table. Finally, we also need to assign categories to the Attributes through an AttributeCategory table.
DepartmentProducts	This is the table that ties the particular product's to the list in the department table. The table contains fields ID for the department/product combination the ID for the department the ID for the product	Purely a linking table between Department and Product tables.
ProductAttribute	This is the table that ties the particular product's attributes to the list in the attribute table. The table contains fields ID for the product/attribute combination the ID for the attribute the ID for the product	Purely a linking table between Product and Attribute tables.
Attribute	This table contains all the attributes of all the products in the store. The fields are the ID for the attribute the name of the attribute the category of the attribute	This Attribute table is used largely for referential integrity for each products attributes.
AttributeCategory	Gives a categorization of the attributes such as size or color. ID for the attribute category the name of the category	Links back to the Attribute table in order to categorize them, if reasonable.

Table Name	Description	Links To
Shopper	This table contains all of the basic background on the shopper. The fields are ID for the shopper the shopper's first name the shopper's last name their address their city state or province country zip code phone number fax number e-mail address date the information was entered the username - usually the e-mail address the password for future access a variable that indicates whether they want to use a cookie to automatically retrieve this information each time they visit	This table links to the Basket table to identify what they're buying
Basket	This table contains fields ID for the basket the quantity the date the basket was created the ID of the shopper a flag to indicate whether the order was placed the cost subtotal the cost total including tax and shipping shipping cost tax	This table links to the Shopper table and to each item for this shopperin the BasketItems table.
BasketItems	This is the table identifies each item within a basket ID for the basket item the ID for the product added to the basket the price for the product the name of the product the quantity of the product ordered the ID of the basket in which these items belong stores size stores color	Each BasketItem links back into the Basket in which it is contained.
OrderData	This table contains all the shipping information and billing information and they are distinguished from each other. The fields are profuse and essentially duplicate the Shopper table fields twice, once for the ship information, another for the billing information. It also contains the ID for the order the ID for the shopper the date ordered	This OrderData table is links to the Shopper table and the Basket table..
PaymentData	Gives the final payment information for the order. ID for the payment the ID of the order that the payment is related to the type of credit card the credit card number the expiration date of the card the name of the card owner	Links to the OrderData table

Table Name	Description	Links To
OrderStatus	The fields are ID for each payment ID of the order the payment relates to ID for the card type date the order was shipped date the order was fulfilled date the order was processed on the web any notes shipping number a flag to indicate whether the order has been processed to fulfillment	This table links to the OrderData table.
Shipping	This table contains fields ID for the quantity range low end for price range high end for price range fee for the range	Not directly linked, but it is used in shipping calculation.
Tax	This is the table identifies each item within a basket ID for the state state abbreviation tax rate for the state	Not directly linked, but it is used in tax calculation.