Microsoft "strongly urges" book authors to abandon Northwind as the default sample database in favor of SQL Server (SQLS) 2005's updated AdventureWorks (AW) OLTP database. AW has a very complex, highly normalized structure of 68 tables. The Visio 2003 and HTML versions of an AW database diagram require printing to at least a 17-inch x 22-inch sheet to be readable.
AW uses SQLS 2005's new user-schema separation feature, which lets you substitute an arbitrary prefix for the traditional database owner's name (dbo by default). Thus the fully-qualified name changes from SQLS 2000 and earlier's ServerName.DatabaseName.OwnerName.TableName to ServerName.DatabaseName.SchemaName.TableName. The SchemaName lets multiple users own a single schema based on their membership in roles or Windows groups. AW has five schemas: HumanResources, Person, Production, Purchasing, and Sales.
Note: An Accounting schema and Accounting.Invoices table are conspicuous by their absence. Apparently, the firm accepts and fulfills orders but doesn't issue invoices or charge credit cards for goods shipped. Northwind also lacks an Invoices table.)The Person.AddressType table has six types of addresses: Billing, Home, Main Office, Primary, Shipping, and Archive. The table contains foreign-key values for StateProvince and CountryRegion tables. Thus a T-SQL query to return all columns for an individual customer address is (from the Sales.vIndividual view):
SELECT i.[CustomerID], c.[Title], c.[FirstName], c.[MiddleName], c.[LastName], c.[Suffix], c.[Phone], c.[EmailAddress], c.EmailPromotion], a.[AddressLine1], a.[AddressLine2], a.[City], StateProvinceName = sp.[Name], a.[PostalCode], CountryRegionName = cr.[Name], i.[Demographics], c.[AdditionalContactInfo]
FROM [Sales].[Individual] i INNER JOIN [Person].[Contact] c ON c.[ContactID] = i.[ContactID] INNER JOIN [Sales].[CustomerAddress] ca ON ca.[CustomerID] = i.[CustomerID] INNER JOIN [Person].[Address] a ON a.[AddressID] = ca.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]
WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] FROM [Sales].[Customer] WHERE [Sales].[Customer].[CustomerType] = 'I')
Creating (or attempting to create) an updatable typed DataSet for individual customers requires six DataTables and generates a DataSet designer file of more than 6,000 lines. I haven't yet attempted to write FillBy... methods for a parameterized details form that would enable updates and inserts with bound text boxes and dropdown lists.
Following is a simple DataGridView of the Sales.vIndividual view. Click the thumbnail to open it in a resizable window.
Note: One of the book's sample projects lets readers populate a Northwind.SalesOrders table—an updated clone of the Northwind.Orders table—with an unlimited number of rows containg randomized data for sequential dates, a specified average number of line items per order, and random ProductID values.