| Now that you have become familiar with the inner workings of a database and how to actually create tables, columns, and rows, lets actually walk through the creation of the Web store database using Microsoft Access. NOTE Access is the database that was chosen for the Web store because it is easy to acquire and its interface is relatively easy to learn.
Before you begin designing any database, take a few points into consideration: What kind of data do I want to store? Who will be accessing my data? What kinds of people will be able to change items in the database? What changes or additions will I need to make in the future? The kind of data that you will be storing in the database is the most important question that you can ask yourself. Will you be storing textual information with like names, address, products, and prices, or will you be storing large binary objects such as sound files and images directly in the database. Answering this question can help you pick the appropriate database that will suit your needs. In this case, because you are simply storing names, addresses, products, and prices, you can get by just fine with Access. If you need to include images in your work, you can simply create a reference to the location on the server where the image is located. How you plan to access the data in the database is also an important consideration. Will you have people selecting and viewing records and possibly updating and deleting records, or will you be building a large database full of financial records? If the first is correct, Access will suit your needs. CAUTION Bear in mind, however, if you were creating a real database for a Web store, you might think twice about choosing Access to store credit card information. Although Access will suit your needs, it may not offer the same level of security that its big brother server replacement, SQL Server would.
After all these questions have been answered, it is safe to assume that you are ready to begin architecting your database. Begin by opening a new instance of Access and creating a new database. Assuming you are using Microsoft Access 2000 or later, follow these steps: -
From the File menu, select New. -
When the docked menu on the right appears, select New Database. -
You will be prompted to give your database a name and location to save it. For now, save it in the root of C:\Inetpub\wwwroot. This will eventually be where you create the Web store application. Click Create. Figure 26.10 shows the screen that you will see along with the proper file saving location. Figure 26.10. Save your database to the root of C:\Inetpub\wwwroot.
-
Figure 26.11 shows the empty database window with the Tables page selected. Figure 26.11. An empty database appears, allowing you direct access to begin creating tables.
You are now ready to begin creating all the tables that will be used in the Web store. We have already outlined the main tables that we will use; they are as follows: Customers CreditCards Inventory Orders Products Transactions The Customers Table The customers table will be reserved for all the "clients" accessing and purchasing items from our Web store. The customers table will include such data as name, addresses, email addresses, usernames and passwords, and possibly, phone numbers. You can begin creating the customers table by selecting Create Table in design view. Figure 26.12 shows the design view that you will be seeing after it has been selected. Figure 26.12. The design view allows you to begin creating columns for your table.
Notice that when the design view is open, you can begin entering all the columns (Fields) that will be used within your table. You are also able to input a data type that corresponds with the "type" of information those fields will contain. Data types are also useful for conserving space and for performing calculations and mathematical computations that you could not ordinarily do if a field was of a different type other than number. All the data types are listed next: Text Text data type is the most commonly used data type and can contain up to 255 characters and/or numbers. Memo Similar to the text data type, the memo data type supports up to 65,535 characters. Number Use the number data type when you expect to perform calculations for example, if you need to calculate the total of six items that a user may have selected within the products table. Currency Similar to the number data type, currency should be used when money is involved. You should use the currency data type when defining your cost column within the products table. AutoNumber AutoNumber is generally reserved for columns whose value you want to increment. Generally, autonumber is reserved for the field that contains the primary key. This ensures that all data within that column are unique. Date/Time The date/time data type is most useful when you want to sort items in your fields chronologically. Yes/No The yes/no data type is useful when either something is selected or it is not. It returns either a true or a false value and generally simulates a checkmark effect. OLE Object When you plan on embedding or linking objects from another source, you can use the OLE object data type. Hyperlink When you want the field to jump to a Web address, use the hyperlink data type. Lookup Wizard The Lookup Wizard creates a field that is generally limited to a list of prespecified values. NOTE If you are working with SQL Server, you have many more options to choose from and some of the same ones available in Access, but you will notice that some of the data types that you would think have the same name are actually referenced differently.
You can begin adding your columns to the table by writing the values into the fields and then assigning them a valid data type. The customers table should be created with the following information: | Field Name | Data Type | | CustomerID | AutoNumber | | FirstName | Text | | LastName | Text | | Username | Text | | Password | Text | | Email | Text | | PhoneNumber | Number | | BillingAddress | Text | | BillingCity | Text | | BillingState | Text | | BillingZip | Text | | ShippingAddress | Text | | ShippingCity | Text | | ShippingState | Text | | ShippingZip | Text | Figure 26.13 shows what the completed table should resemble. Figure 26.13. Create the customers table with the appropriate information and corresponding data type.
With the customers table now created, you are ready to save the file. From the File menu, select Save. In the dialog box, type Customers and click OK. Figure 26.14 shows the Save As dialog box. Figure 26.14. Type Customers and click OK.
Now that you have created all the columns, you're presented with the datasheet view. Use the datasheet view to fill up the table with as many rows of information as you like. Figure 26.15 displays the datasheet view. Figure 26.15. Use the datasheet view to fill up the table with rows of data.
You are now ready to create the next table. The CreditCards Table The creditcards table will be used initially to store a valid credit card number for a particular customer. You will be able to determine which credit card belongs to which customer through a relationship. The creditcards table contains the following information: | Field Name | Data Type | | CustomerID | AutoNumber | | CreditCardType | Text | | CreditCardExpiration | Text | The Inventory Table The inventory table will be used to maintain quantities for specific products. The inventory table contains the following information: | Field Name | Data Type | | InventoryID | AutoNumber | | ProductID | Number | | Quantity | Number | The Orders Table The orders table will be used as a temporary repository to keep items that a customer is planning on purchasing. After the order is completed, you can retrieve all that information and move it into the transactions table, completing the order. The orders table contains the following information: | Field Name | Data Type | | CustomerID | Number | | OrderNumber | Number | | ProductID | Number | | Quantity | Number | The Products Table The products table will be used to store all the products that we will be selling in the Web store. The products table contains the following information: | Field Name | Data Type | | ProductID | AutoNumber | | ProductName | Text | | ProductDescription | Memo | The Transactions Table The transactions table is used to keep a log or history of all transactions that have been processed in the Web store. Generally it is good practice to keep a running record of all transactions in case you want to determine what items certain customers are buying, how many, and the amount that certain customers are spending at the Web store. The transactions table contains the following information: | Field Name | Data Type | | TransactionID | AutoNumber | | ProductID | Number | | CustomerID | Number | | DatePurchased | Date/Time | When you are completely finished creating your tables, your database view should look similar in design to Figure 26.16. Figure 26.16. A display of all of the tables within the Web store database.
| 
