An Overview of the Dorknozzle Database
| Now that you've become familiar with the inner workings of a database and you've effectively imported/restored the Dorknozzle .SQL file into your database of choice, open the DBMS you plan to use so that we can walk through the various tables contained in the Dorknozzle database. NOTE Because of this book's size restrictions, we can not possibly walk through all the examples using all three database management systems. In the interests of simplicity and attainability, we'll walk through the examples using only Access. However, if you've imported/restored the .SQL script on your DBMS of choice, the overall structure for the Dorknozzle database should appear in your DBMS similar to the way it does in Access. At the very least, you can follow along and understand the inner pinnings and various tables contained in the Dorknozzle database using your DBMS of choice. As you can see from Figure 23.18, numerous tables exist for streamlining how users will interact with dynamic Dorknozzle content. Figure 23.18. The Dorknozzle database contains numerous tables for streamlining dynamic Dorknozzle content.
The rest of the chapter discusses the various tables contained within the Dorknozzle database. The idea is that you become familiar with the various tables and the information within those table that we'll expose within our Dorknozzle web application. Specifically, we'll discuss the following tables:
Remember, we can not possibly cover every aspect of the Dorknozzle web application. Instead, the rest of the book focuses on the Employee Store segment of the web application. As you'll see in the section "Other Tables," I've included numerous other tables so that you can practice the concepts presented in the rest of the book on your own. Let's get started! The Employees TableThe Employees table is reserved for all users accessing and purchasing items from our Dorknozzle intranet store. The idea is that most tables in the database reference an employee to a certain extent. As you'll see later, other tables such as Orders, CreditCards, Departments, and HelpDesk have a relationship of some kind with an employee in the Employees table. For instance, an employee belongs to a department, an employee can have and ultimately store numerous credit cards, an employee can have multiple orders on file within the employee store, and lastly, an employee can have numerous help desk tickets submitted. Opening the database reveals the underlying design behind the Employees table, similar to Figure 23.19. Figure 23.19. The Employees table contains numerous fields regarding employees' personal information. The Employees table includes such data as name, address, email, usernames, password, and phone number. Specifically, the data is outlined as follows:
As you can see, the table's structure is fairly self-explanatory. The only fields that probably need a bit of explaining are the CustomerID and DepartmentID fields. Earlier in this chapter, we discussed the concept of relationships and, more specifically, keys. We said that one-to-many relationships rely on primary and foreign keys. In this case, the CustomerID field is the primary key for this table and is identified as such by the small key icon that appears just to the left of the field. Any relationship that exists between the Employees table and another table is governed by the primary key field. In this case, there's a catch. We also have a foreign key, the DepartmentID field. Although it's hard to tell that this field is considered a foreign key (there's no icon identifying the field as such), we know that for every department that exists in our company, we'll have numerous employees that work in the department. To outline this conceptually in a database, we set aside the DepartmentID field in the Departments table as the primary key and then create a similar DepartmentID field in the Employees table as the foreign key. This way, we need only reference the department by its unique identifier (its primary key) in the Employees table. To visualize this concept, look at the diagram in Figure 23.20. Figure 23.20. A one-to-many relationship exists between the Departments table and Employees table.
As you can see from the diagram (available by choosing the Relationships option from the Tools menu), a one-to-many relationship exists between the DepartmentID primary key in the Departments table and the DepartmentID foreign key in the Employees table. Now let's open the Employees table in a datasheet view. As you can see from Figure 23.21, all the employees listed in the companydirectory.htm page are also listed, complete with all of their personal information, in the Employees table. Figure 23.21. All Dorknozzle employees are listed, complete with their personal information, in the Employees table. Once you grasp the concept of relationships and keys, creating relational database structures is a piece of cake. If you haven't totally grasped it yet, don't worry, there are more tables that we'll be outlining that employ the same concepts. Let's move forward. The Departments TableConsidered a "lookup" table, the Departments table will rarely grow in size and almost never be modified. Lookup tables, such as the Departments table, serve one purpose, namely to separate potentially redundant data out of one table into a separate, easier-to-manage table. For a moment, assume that we don't have a Departments table and that instead, that information is located in the Employees table similar to the following table:
As you can see from the table, the Department Administration appears three times. You can imagine if we had thousands of users in the Employees table how inefficient this would become. Instead, we can separate potentially redundant information out of the Employees table and create a new table, in this case, Departments. the Departments table contains single instances of the departments which are referenced from tables, like the Employees table, through a foreign key. Looking at the structure of the Departments table reveals the following fields:
As you can see from the table and mentioned in the previous section, the DepartmentID field is the primary key for the Departments table. To establish a relationship with other tables, we simply reference the DepartmentID primary key via a foreign key in the table from which we want to establish a relationship. In our case, the Employees table will have a relationship with the Departments table. The second field, Department, is merely the text field that will contain the rows of department names. Looking at the data sheet view for the Departments table reveals the six departments within Dorknozzle similar to Figure 23.22. Figure 23.22. The Departments table contains six departments that represent the six Dorknozzle departments. Because we have six departments listed and because a relationship has been established between the Employees and Departments tables, a user must belong to one of these departments. Maintaining integrity of this relationship is known as referential integrity. Referential integrity dictates that data contained in a field of a table that has a relationship established must be able to be linked to its primary table. In our case, if we entered the number 7 in the DepartmentID field of the Employees table, our database would return an error because the DepartmentID 7 doesn't exist in the Departments table. The Credit Cards TableThe next table to consider is the CreditCards table. Like the Departments table, redundant information would appear in the Employees table if we had to list the employee multiple times simply because they wanted to store more than one credit card. Instead of listing the employee twice, we simply create a separate table for credit cards. The relationship exists between the EmployeeID primary key in the Employees table and the EmployeeID foreign key in the CreditCards table. The CreditCards table exposes the following structure:
If you open the CreditCards table in the data sheet view, you can clearly see that eight of the nine employees are simply storing one credit card on file. This point is shown in Figure 23.23. Figure 23.23. Eight employees have one credit card on file. One employee has two credit cards on file.
The employee with the EmployeeID of 9 however, is storing both a Visa and a MasterCard. This validates the one-to-many relationship. One employee could potentially have many credit cards on file. NOTE Of course, we could normalize the CreditCards table even more. For instance, if we know that Dorknozzle accepts only Visa, MasterCard, American Express, and Discover, we could easily create a new table called CreditCardTypes. That table, like the Departments table, would simply have two fields, CreditCardTypeID and CreditCardType. We could then create a one-to-many relationship between the CreditCardTypes table and the CreditCards table. There does come a point were you can over normalize your database. In this case however, it would make perfect sense. The EmployeeStore tableThe next, and possibly most important, table for us is the EmployeeStore table. We'll use the EmployeeStore table as a way to store all the items we'll be selling in the Dorknozzle employee store. If you've opened the EmployeeStore table in Design view, you'll notice that it has the following structure:
In this case, the ItemID simply serves as the primary key within the table. However, a relationship will be made between this table and the Orders table (mentioned in the next section). Because an employee could have many orders, a relationship is established between the EmployeeStore table and the Orders table so that we know which item is on order. Also notice one unique field in the EmployeeStore table: the ImageURL. In this case, we are simply storing the path to the image on the computer. This keeps our database lightweight because we use the file system to store images while our database merely acts as a reference to thosee images. Looking at the data sheet view reveals all of the items that the Dorknozzle store will carry. As you can see in Figure 23.24, the Dorknozzle store will carry items ranging from t-shirts to stickers to golf balls. Figure 23.24. The Dorknozzle employee store carries numerous items ranging from shirts to golf balls. If you take a look at the relationship diagram shown in Figure 23.25, you'll see the one-to-many relationship between the EmployeeStore table and the Orders table. Figure 23.25. One item could have many orders.
Remember that every item in the EmployeeStore table could potentially have many orders, thus the reason behind the one-to-many relationship. The Orders TableThe Orders table will be used as a temporary repository to house items that customers are planning to purchase. This allows us to keep track of who's ordering what and also allows the shipping and receiving departments to process the order. Looking at the design view of the table reveals the following structure:
You'll notice that this table's structure is a bit different than the ones mentioned thus far. In this case, not only do we have a primary key, we have two foreign keys. The reason for this is simple. The Orders table will have a relationship with two tables as opposed to just one. Each employee could potentially have an order. Thus, we'd need a relationship between the EmployeeID primary key in the Employees table and the EmployeeID foreign key in the Orders table. Furthermore, as we stated in the previous section, each order will have a product association. Since this is the case, we'd need a relationship between the ItemID primary key in the EmployeeStore table and ItemID foreign key in the Orders table. The OrderID primary key in the Orders table simply acts as a unique identifier and is the order number that employees reference if they have questions about a particular order. Other TablesAs mentioned earlier in the chapter, we cannot possibly cover all aspects of the Dorknozzle web application throughout the course of this book. Instead, we'll cover basic functionality such as showing all records in the EmployeeStore table within the employee store web page. Furthermore, we'll allow users to select items from employee store and add them to their cart. Since it'll be difficult to cover all aspects of the Dorknozzle web application, what I have done is expose other tables within the Dorknozzle database so that once you grasp the basic concepts of selecting, inserting, modifying, and deleting, you can take those concepts and integrate the rest of the Dorknozzle functionality on your own. The tables I've exposed for you to work with on your own include the following:
If you look in the database, you'll notice that these tables are preceded with the letter x (as shown in Figure 23.26). The x stands for Xtra, and also serves as a way of keeping these tables grouped separately. Figure 23.26. All tables marked with the x are extra tables you can use to work with the Dorknozzle web application on your own time. |
EAN: 2147483647
Pages: 237