One of the next steps, after designing your tables is designing your views.  You’ll often need to create them so that programmers will have access to the data in several tables, but you want to provide them an easier way to pull out the data.  It’s a lot easier for them to use:

SELECT * 
FROM vCustomer 
WHERE 
   FirstName = 'Shannon' 
   AND LastName='Lowder'

Than it is to use:

SELECT 
   c.FirstName, c.LastName, pn.PhoneNumber
FROM Customer c
INNER JOIN PhoneNumber pn
   ON c.customerID = pn.CustomerID
WHERE
   c.FirstName = 'Shannon'
   AND c.LastName = 'Lowder'

Isn’t it?

Designing your views is usually easier than designing your tables.  During the discussion and planning process you generally discuss how the data is going to be used. Then during the table design phase you’re breaking that down into atomic parts, and building your tables from that.

Once your tables are built, you’ll then build the queries that will combine the related data back into a single view.  It’s these views you’ll give to your programmers (unless you build out a system of stored procedures…and we’ll talk more about that option later.)

Case Study

In our current case, we have tables for customers, addresses and phone number.  But our use case is we need to retrieve the customer’s first and last name, along with their home phone, cell phone, and work phone.

When we’re drawing this out, I draw it just like a do my tables, but I make sure to begin the name with a “v”.

Also, in my designs i usually don’t include any ID columns.  Since these are used to retrieve data, I’ll only include the data they need to retrieve.  If you dig around my site you may find information on materialized views or indexed views, in those you will find keys defined.  But you need to understand when you’d want to materialize a view.  For the purposes of this class, we’re only talking about simple data views.  Nothing fancy here, just define what columns you need to see, and make them happen.  Later you’ll have to actually write the queries to build these views.

For now, you’re only designing them on “paper”.

Summary

Views are an important part of your database design.  They’re there to give people read access to data in your database without requiring them to build complex T-SQL statements to retrieve that data.  I’m a big fan of building views and stored procedures so that your programmers never have to directly connect to your tables.  Doing that buys you a lot of flexibility in the future.  You could make changes to your table, and your programmers won’t have to make a single change to their code.

It’s a worthwhile endeavor!

As always, if you have any questions, please…send them in.  I’m here to help!