Traditional database design techniques such as the Systems Design Life Cycle (SDLC), also known as the Yourdon Waterfall method, and Information Engineering (IE) were created to make design a manageable process, complete with tools, milestones, and reviews. A design-testing phase is a crucial methodological step in any formal process, but often designers neglect testing when timelines grow short and budgets shrink. To make matters worse, a project that is rushed through without formal testing has surely also suffered shortcuts in its requirements-gathering phase. If you find yourself caught in just such a project predicament, I have a suggestion for you: Use questions and user views to test your design.
As you develop a database design for a rushed project, you'll find yourself asking questions about the database requirements that your requirements documents don't answer. When that happens to me, I put myself in the role of an end user. I ask what information end users need to do their jobs because those needs will become report requests. I keep a list of those questions. I add to the list as I review the data model and compare the model to the T-SQL code that my modeling tool, Sybase's PowerDesigner 10.0, generates. Then, I execute the T-SQL code in Query Analyzer to build databases, tables, constraints, and indexes.
Why take time to formulate potential end-user questions? Because you won't be able to produce reports if the data isn't in the database. For example, in a retail organization, end users might ask, What's our best-selling product, and who manufactures it? What kind of feedback have we had on order number 123456? What's the payment information on this shopping cart? You should anticipate such likely inquiries because they provide valuable information about database content and design requirements.
Let's look at an example of how the question-view-test method could help you quickly design a retail database. Figure 1 shows a small part of the organization's physical data model (PDM) and uses conceptual (crow's foot) notation to specify relationships (a single line for one, a crow's foot for many, a single hash for mandatory, and a hollow circle for optional). The model contains tables for Customer, Company, Address (either company or customer), and AddressType. In this example, the Company table is actually a supertype of company subtypes such as Manufacturer, Vendor, and Shipper, but I elected not to show subtype tables in this diagram because they aren't relevant to this discussion. The AddressType table is a lookup table; it contains a list of values such as shipping address, billing address, branch office, and main office. Because the relationship between AddressType and Address is enforced, the AddressType table helps you control what values your users can enter in the AddrTypeCode column in the Address table. Based on the initial requirements you received, this model is adequate.
Now put yourself in the end-user role and start asking questions. In this example, you should ask what information belongs on reports that print customer names. You know enough about your company to realize that some end users will need customer company affiliation and job title along with customer name.
You can't generate that combination of information by using the tables in Figure 1, so you extend your design by adding an Employment associative table to the model, as Figure 2 shows. This design can accommodate a many-to-many (M:N) relationship between the Company and Customer tables because in this environment, a customer can be affiliated with more than one company. Conversely, a customer can work for no company (one-to-zero—1:0), one company (one-to-one—1:1), or several companies (one-to-many—1:M), as the relationship between the Customer and Employment tables indicates.
Next, you write the user view, vCustomerCompanyInfo, that Figure 2 shows so that you can test your design. A user view is a user-defined virtual table made up of various columns that originate from one or more user tables. You can read more about views in my article "Views and Stored Procedures," September 1999, InstantDoc ID 5917. The view in Figure 2 includes columns from three tables: dbo.Customer, dbo.Employment, and dbo.Company. The data the view returns will be a combination of customer (first and last name, preferred status, and job title) and company (name and account code) information. This is all the data you expect users to need. You add to your list of design test questions this query: Does this customer have a company affiliation, and if so, what company is it, and what is his/her position?
Of course, you must wait until you build and populate your database and user tables to confirm whether your basic view code runs correctly. If your code runs successfully, you can easily convert it to a stored procedure that an application can call. So, by integrating questions and views into your design process from the beginning of your project, you can improve the initial database design, test the design, and get a head start on writing stored procedures to support your users.
Now that you've seen how useful views can be as an informal design test tool, I want to add a word of caution. Be sure to give your users access to stored procedures, but keep views and view names for yourself. Over time, users' modifications and nesting of views can cause serious performance problems. For an explanation of the problems that can arise with nested views, see Brian Moran's SQL Server Perspectives article "What Are Your Nested Views Doing?" July 2004, InstantDoc ID 43329.
Even if you don't have time to use a formal testing methodology, don't ignore database design testing. Not testing will inevitably result in a database that fails to meet requirements, is difficult (or impossible) to extend, can't scale, or has all of these problems. Integrate testing during design creation by using this question-and-view method. And don't be put off by this technique's unstructured form. Remember, unstructured testing is better than no testing at all.