Chapter 02 - Data Modeling and Database Design

In the past you may have implemented a database schema where, for example, you stored the item code for each item ordered—such as in a point of sale system, with the order record. Later, when your program queried the order, it retrieved and parsed this field and could determine what was ordered with that order record. Figure 2.1 shows an example of this. You had the opportunity to store one or more item numbers in with the order record.

Fig. 2.1 - Without 1NF, you could store more than one logical item in a physical record. This isn't valid when you normalize your database.

we've created these examples using Microsoft Access working with SQL Server because it's a good tool for creating tables quickly and easily. The tool you use is entirely up to you; even the SQL Enterprise Manager, although a bit less "visual," will still serve your purposes fine. You'll also want to take a look at the end of this chapter and consider one or more of the database tools that you can use to help the process of creating tables and relationships.

With 1NF, duplicates aren't allowed. You need to create a schema where only one item will be recorded for each order record on file. So to implement the point-of-sale solution mentioned earlier, you would have an order represented by one to n records containing the item code information that made up the order. This provides a slightly different challenge to retrieve the information for the record. You must have a means of retrieving each record associated with the order and making sure that you've retrieved each record, but no more or less. Of course, this will lead to order numbers, called out in each record. Later, this chapter gets more into the database design and entity relationship models. Figure 2.2 shows the results of this first pass at normalizing a database table.

Fig. 2.2 - With a 1NF table, each row is a single, atomic record. It must be able to stand alone.

For now, simply remember that with 1NF, you must have each row contain only one instance of the information, and all column values must be atomic.

Second Normal Form

You'll need to add an OrderItemID column to the table to fulfill the requirements for 2NF (see fig. 2.3). You'll keep the OrderNum, but the OrderItemID will be the primary unique identifier for the item in a given row.

Fig. 2.3 - After you add a unique row ID for each line item, this table now fits the 2NF model.

Notice that although we've added a new item as item 11, it can still be related back to the order based on the order number. All we've done is provide a way to uniquely identify the row within the table.

Third Normal Form

That's a strange definition until you understand what's really happening here. The whole goal of normalizing your tables is to remove redundant, non-key information in your tables. Reviewing figure 2.3 shown earlier, you'll quickly see that this model is broken quite nicely. Because you're storing descriptions in the table, and because these descriptions probably are used elsewhere in the database, storing them individually in the table is a problem.

Here's where you start to see the real advantages of moving to 3NF. Remember, the simple examples that we're using here relate to a grocery store. Imagine if you were storing your information as shown in figure 2.3, and that you needed to change the description of "Milk" to "Milk 2%" because your vendor has maliciously introduced "Milk 1%" to the mix. You'd need to write a utility to update the sales database tables and any other table that ever referenced "Milk" to show the correct percentage. A real nightmare that will often lead to problems is inconsistent information in the database. It would take forgetting or not knowing about only one table that needed to be updated to completely invalidate your use of the item "Milk" for sales history.

Normalization is the key to taking care of this. In figure 2.4, the problem is corrected quite simply by removing the description from the table altogether.

Fig. 2.4 - To fit the 3NF model, the description column is removed from the order items table.

Since an item code is already assigned to Milk, you have the information you need to set up the Inventory table, as shown in figure 2.5.

Fig. 2.5 - By creating an inventory table, you create a "home base" to refer to and use any time you reference inventory items.

Of course, once you've begun normalizing your table, it brings to mind the question of how to get a complete picture of the item sold. How can you find out all the information about the line item, what its description is, the sale price, and so forth? This is where relational databases and their use of Views come into play. In the example, you can create a quick query that returns the information you need quickly and easily. Figure 2.6 shows what a sample view would return to your application for the table examples.

Fig. 2.6 - When you create a relational view of the database tables, you can retrieve and work with a complete picture of the information, although it may be dispersed across several tables.

See Chapter 6, "Retrieving Data with Transact-SQL" for more information.

See Chapter 9, "Managing and Using Views" for more information.

You can see that by using the combined information between the tables, you still have the same full data set to work with, but at the same time, you're limiting the sources of information. You'll find quite often that the way you end up working with the information may not change, only the methods used behind the scenes to retrieve that information. This is the case with the 3NF table in this example. You're still storing the same information base—you're just retrieving the information using a different means, which in this case is a logical view of the two related tables.

Next, you'll look into the logical pieces of the functional parts of your system. These pieces will play a key role in your database and overall system design decisions.

Understanding the Client/Server Model

With SQL Server, you can implement true client/server systems. By this we mean that these systems can adhere to the concepts of client/server, allowing you divide functional components into cooperative operations that accomplish your application's goal. This sounds strange, but what it amounts to is dividing processing between the client and server in a way that makes sense to the application. With database-oriented systems, especially those where the database subsystem is open and accessible from many different points, it makes sense to implement an intelligent database layer that will manage the data. This layer is responsible only for storage and inquiries as they relate to the information. It has no responsibility for the presentation of information.

In the next couple of sections, you'll review what types of functions and operations reside in the client and server sides of the client/server model. Although these concepts aren't exhaustive, you need to understand them. For many people, client/server is just a fancy term for a PC database that resides in a common location and is accessed by many different workstations. After reading this chapter, you should understand that client/server is much more than a common storage location. You can't create a client/server system by using Microsoft Access database tables, for example, regardless of whether the tables are stored on a file server or a local system because no intelligent engine can process the database independently of your application. The logic controlling the data is still driven by your client-side application.

You can create Access-based client/server systems by creating linked or attached tables to an Access system. These tables can be based in a server-based intelligent database system and will help you create a client/server system in Access. By saying that Access database tables aren't client/server, we're referring only to the native Access database tables, typically contained in physical files with an .MDB extension.

Typical Roles: The Client Side

The client application will typically be written in a host language—often Delphi, PowerBuilder, Visual Basic, C, or C++, for example. These applications allow users to perform add, change and delete operations against the database, where applicable.

The client application should avoid, at nearly all costs, having to work with the entire database table's contents. When a set of information is worked with, you should always think of it as a results set, not the entire data set available to you. By results set, we mean that you should ask the server application to filter and limit the information that will be presented to you so that the operations you carry out are completed against as small a set of information as possible.

One of the best descriptions we've heard comparing older systems with client/server is that of a file cabinet versus a folder. In older systems, you'd typically be doing the equivalent of asking for a file cabinet full of information, so you can take the time to sift through the contents to find the file you want. In this scenario, your client-side application is the piece doing the sifting. All information from the database table is passed through the client, and the client does the filtering to find the information you want to work with.

In the client/server world, you simply request the file folder you want, and that's what's returned. You don't filter through the file cabinet—the server process does. This limits network traffic, as only the results set is passed back over the network. The other very significant benefit of this is that it also increases performance for your application. Typically, server systems are powerful, very strong computing platforms. Since this optimized server platform can work with all information locally, it can do so at top speed. It will be processing the information at the best rate possible.

In short, your client-side application should be optimized to work with results sets. This works hand in hand with database structure and design because you need to make sure that you create the database in such a way that it can support this requirement. You'll have to define the joins, queries, stored procedures, and table structures to support this optimized query into the contents of the database.

In summary, here are some guidelines for the client side of your application:

• It should gather all needed information before making a request of the server.

• The client is responsible for all data display to the user.

• The client should work with results sets rather than tables.

• The client should do all data-manipulation operations.

• The client provides for all formatting of data and information presentation in reports.

Typical Roles: The Server Side

With SQL Server, your goal is to create the results sets required by the client-side applications. The database engine will be responsible for carrying out the information storage, update, and retrieval in the system. When you first start working with SQL Server, notice that it has no user interface (UI) at all. Yes, utilities are available to help you manage it, but SQL Server in and of itself has no UI. This is by design. SQL Server exists to fulfill requests made of it to the point of returning the results from those requests. Unlike Access, dBASE, FoxPro, and others like them, SQL Server has no involvement in showing users the results of these queries.

When you're designing your database structures, you need to keep a very close eye on how you implement informational control in your system. For example, it may be that different people will need different access levels to the information. Security may be—and often is—a major issue in the query of the database. If this is the case, your table structures and joins need to reflect this requirement. Chapter 19, "SQL Server Security" provides additional information about the security considerations for your system.

You'll also need to keep in mind how users are going to be accessing your information. Remember that in today's world of open systems, new challenges exist in presenting and controlling information. As you create your database tables and the rules that govern them, you need to assume absolutely nothing about the client side of the application. A good question to ask yourself is, "When I receive this information, what needs to happen with it?" You can answer it by saying, "It needs to be stored for later retrieval." But is that all?

If you're storing sales information, you should validate the item code being sold. Does it exist in the inventory database? Is sufficient stock on hand to sell this item? Do you force sufficient stock levels, or do you allow a "negative stock" situation to occur and simply log the discrepancy in a suspected transactions table?

Each issue requires work on the database side through rules and triggers. It's true that you could expect the client application to complete these tasks, but what if someone is accessing your database from Excel or Word? Can you really assume that they've had the presence of mind to make sure that these checks are taking place? These important issues should be carried out by the server to make sure that they happen, regardless of the point of entry.

For additional information about rules and triggers, see Chapter 14, "Creating and Managing Triggers" and see Chapter 11, "Managing and Using Rules and Defaults" In addition, see Chapter 22, "Accessing SQL Server Databases Through Front-End Products"

Exceptions to the Rules and Roles

It may seem like the right thing to do at the time you're implementing that really intricate trigger or rule, but a caution is in order: it can become a nightmare trying to move too much functionality into the wrong side of the client-server model. Think long and hard about other ways you can implement something if you find yourself putting into place an operation that breaksthe client/server model. You'll be glad you did.

Establishing a Roadmap to Your Database

Database flowcharts consist of entity relationship diagrams, or ERDs. ERDs show exactly how a database is structured, what the relationships are between the tables, what rules and triggers are involved in maintaining referential integrity, and so forth. One big benefit of the ERD is that you can sit down with the client and take a logical walk through the database, making sure that the system serves the client's needs.

It's beyond the scope of this book to provide an all-encompassing view of the intricacies of entity relationship diagramming. The information and approach we'll be providing here are meant to fit 90 percent of the cases for what you'll be doing. In some cases, you'll need to implement slightly different or less frequently used facets of the ERD systems. In those cases, you'll be best served by consulting the capabilities of your design software and database back end, as well as the resources available on the Internet and in other sources of information regarding the world of ERD.

Entity Relationship Diagramming: The Flowcharts of the Database World

Fig. 2.7 - This diagram shows a relational diagram for the grocery sales system.

we've added the customer table to be able to track an order for a customer, but apart from this addition, the table structure reflects the earlier tables and relationships. Take a look at how these basic objects—the entities, attributes, and relationships—apply to the simple model.

Using and Referencing Entities

Fig. 2.8 - The basic entity is represented by a box that typically contains two sections.

When you name entities, you should always make the name singular. It will help reinforce the fact that they contain only one instance of the object they represent.

The portion above the dividing line represents the identifying portion of the row. Remember, to have a normalized database in 3NF, you need to be able to uniquely identify each row instance in the database table. By placing the identifying characteristics above the line, it's easy to read and determine how the record will be retrieved in most cases.

In figure 2.8, you can see that, by the definition for the customer table, you'll most likely be retrieving records from it by using the CustomerID.

Although a record may usually be retrieved by this identifier, it's not an exclusive handle to the row. In most systems you'll need to provide other avenues to retrieve rows. On the customer table, for example, it's likely that you'll need to implement some name searches. These searches wouldn't include the Customer ID, but after you found the customer the user wanted to work with, you'd likely retrieve the Customer ID for the selection and then retrieve the entire customer record that was selected.

Using and Referencing Attributes

As the name implies, non-key attributes are those items that make up the entity that don't depend on any other entity. In other words, they don't make up, nor are they a part of, a key that's used in the entity.

Key attributes come in two different types: primary and (for lack of a better term) non-primary. Primary keys are always shown above the line, indicating that they're identifying attributes for this entity. If the attribute is a key to the entity but not a part of the identifying structure for the entity, it's placed below the line.

If an item refers to a key value in another table, it's known as a foreign key. Again, if you reference the basic model as in figure 2.9, you can see that the Customer table doesn't have any foreign-key segments, but the Order table does, as indicated by the "(FK)". The foreign keys in the Order table are non-identifying, but help designate the customer that the order refers to.

Fig. 2.9 - The basic ERD shows foreign keys as primary (identifying) and non-primary (non-identifying) columns in the sample tables.

Moving from the Order table to the Line Item table, you'll see that the OrderNumber is listed as an identifying component of the Line Item table. This means that to find a specific instance of an order line item, you need to know the OrderItemID, the ItemCode, and the OrderNumber. In this implementation, the Line Item table is an associative table between the Inventory table and the Order table.

Using and Referencing Relationships

A customer makes orders in the Order table, and an order includes line items that reference inventory items. You can also read the diagram in the other direction. For example, you could also say that inventory items are referenced by line items. In any event, you should be able to show concise paths for information to follow when trying to reach an end result.

In the examples, we're using ERwin by Logic Works. This tool allows you to define the different objects, and then place the relationships between the objects appropriately. In the relationship between the Customer and Order tables, figure 2.10 shows that the relationship is non-identifying and that the relationship is a zero, one or more relationship.

Fig. 2.10 - ERwin allows you to easily define the relationship between the Customer and Order tables.

You can also see that the key in the Order table that will be used to retrieve the customer information is CustomerID. It's automatically added to the Order table, and it's added to the non-key portion of the record. If you define an identifying relationship, CustomerID will be moved to the top portion, or identifying key portion, of the Order table entity.

Each of the other relationships is defined in a similar manner. You can walk down the table structure and determine exactly how the different entities will interact. The next couple of sections look at a methodology that will guide you through the design process with the customer.

System Architecture Definition

While bringing a customer up to speed, terminology, methodology, and approach aren't the goal of client reviews. The goal of any system you'll endeavor to write is making sure that the database structure will support the functionality of the system. This is where the old maxim of "Determine the output first, the input will follow" comes to bear on a project. It's certainly true that the test of any system is the output. If you've created the best system ever devised to allow input of information, it's a sure bet that if you don't have a way to get meaningful information out of the system, your time has been all but wasted.

Review the User's Goals for the System

If you didn't know that the users needed to have an aging report from their point-of-sale system's accounts receivable subsystem, would you automatically store the date that the original invoice went out?

It may seem that involving the user only prolongs the development process. Many studies have shown, as has personal experience, that it's not the case. Spending the time now pays off manyfold later in the project in terms of more accurate deadlines, correct designs, and more.

Truly the only way you can ensure that you're not coding in vain is to make sure that you can fulfill the output needs for the system.

Here's a general set of guidelines that not only will allow you to ensure that you've hit at least the high points of your target audience, but will also map nicely to the database design topics we've covered here:

• Meet with your users and get a good overview of what they need from the system. Get as specific as possible with your discussion. Get copies of current forms, reports, screen shots of what they may have on their current systems, and so on.

• Create a functional overview of the system. An overall system flowchart will be a good component of the overview, allowing the customers to review the system and make sure that you understand what's happening at different steps in the flow of work through the system.

• Present the functional overview to the users. Walk through the system carefully to make sure that it's correct.

• Create a set of tables using a good ERD tool. Don't worry initially about foreign keys and the like. It's more important at this point to simply make sure that you're gathering the right informational items for the users.

• Present the database tables to the users—not from the perspective that they should understand how and why you've laid out the tables the way you have, but more from the standpoint of "OK, ask me any question about where some bit of information will be stored. We want to make sure that we can show you all the information you need." Of course, the first thing you should do—even before meeting with the customers—is to review the reports and samples that you obtained early on and make sure that you're addressing them appropriately.

• Next, put into place the relationships between the tables as needed. Make sure that you can walk down all the logical paths that you expect the users will need, based on your needs analysis. There will be more information about resolving many-to-many joins later in this chapter.

• Present this new schema to the users with a challenge. Ask them to present you with a query for information from the system. Can you satisfy it with identifying and/or non-identifying relationships? Can you get there from here? This is the test of your database design. You should be able to address each and every one of the stated intentions for the system.

Systems get very complex very fast. We can't overemphasize the importance of reviewing with the users all the different requests that may be made of the system. In one such case, we prevented a substantial design rewrite by meeting with users. We found that, although the information was available to determine a specific-case customer, we hadn't correctly laid out the relationships. By preventing this oversight early on, you'll save many, many hours of development time in the long run.

Avoid Many-to-Many Joins

A join is a way of creating a logical view of your data. You specify how the information is retrieved, if it's related to other tables and what information you'd like to see. From there SQL Server will return the results set to your client application in the form of a view on the table as you've defined it.

If you didn't have the associative table between the Order Table and the Inventory table, you would end up with a many-to-many relationship between the two. This isn't a good way to accomplish this, as you wouldn't have a singular path for identifying an instance of an order record. Figure 2.10 shows the associative table.

Fig. 2.10 - An associative table has been implemented to remove the many-to-many relationship problem imposed by the Order and Inventory tables.

When to Break the Mold

For example, if you're working with static tables in your application, as may be the case with the inventory table in the example, you may want to load that table to a local Access table for access by your application. If you were to do this once a day, you'd be reasonably assured of having the correct and up-to-date information at the workstations.

It may be that you're loading down tables that, when taken alone, don't provide a complete picture. For example, if you have a customer table, an account balance table, and a sales representative table—all related based on customer number—you'll end up with a three-way join to return a complete picture of the information.

You may want to consider denormalizing this set of tables at the client. In this case, you could create a single table that would hold the information from all three tables as a single row of information. That way, when you request information for customer X, you'll receive all the information you need, and a further join won't be required.

Of course, in reality you wouldn't want to be manipulating something as potentially dynamic as the customer account balance in a remote mode. As a rule, you'll want to consider this method of denormalization only for static tables, or at least those that change only infrequently.

From Here...

Review things carefully with your customers, whether the customer is internal or an external client, and this will provide substantial leverage in your projects and will help you toward coming in on time and on budget.

The following chapters provide additional information that you'll find useful in your database design efforts:

• Chapter 6, "Retrieving Data with Transact-SQL" will show how you create the joins that this chapter talked about. It will also show how you can create the SQL statements that you'll need to retrieve the information in the format, order, and filtered results sets that you can most optimally work with.

• Chapter 9, "Managing and Using Views" shows how you can create the logical data sets that you'll be working with in SQL Server's relational environment.

• Chapter 11, "Managing and Using Rules and Defaults" shows how to implement business rules and data integrity in your database tables.

• Chapter 14, "Creating and Managing Triggers" will help you add referential integrity to your applications by enabling server-side processes when certain data-driven events occur.

• Chapter 19, "SQL Server Security" will help determine the best approach for securing your database and the objects contained within it.

Table of Contents

01 - Introducing Microsoft SQL Server

03 - Understanding the Underlying Operating System Windows NT