Product Scenario

SQL Server 2008 Books Online (October 2009)

Product Scenario

This topic provides details about the product information that is represented in the AdventureWorks sample database, a list of product-related tables, and sample queries that demonstrate common table relationships.

Product Overview

As a bicycle manufacturing company, Adventure Works Cycles has the following four product lines:

Bicycles that are manufactured at the Adventure Works Cycles company.
Bicycle components that are replacement parts, such as wheels, pedals, or brake assemblies.
Bicycle apparel that is purchased from vendors for resale to Adventure Works Cycles customers.
Bicycle accessories that are purchased from vendors for resale to Adventure Works Cycles customers.

Product Tables

The following table contains a brief description of the data stored in the product-related tables.

Schema.Table	Contains this kind of content	Comment
Production.BillOfMaterials	A list of all the components used to manufacture bicycles and bicycle subassemblies.	The ProductAssemblyID column represents the parent, or primary, product, and ComponentID represents the child, or individual, parts used to build the parent assembly.
Production.Culture	The languages used in localized product descriptions.	Product descriptions are available in Arabic, English, French, Hebrew, Simplified Chinese, and Thai.
Production.Location	A list of the locations within Adventure Works Cycles where products and parts are stored as inventory. For example, paint is stored in both the Paint Storage location in the warehouse and in the manufacturing work center, Paint Shop, where the bicycle frames are painted.
Production.Product	Information about each product sold by Adventure Works Cycles or used to manufacture Adventure Works Cycles bicycles and bicycle components.	The FinishedGoodsFlag column indicates whether a product is sold. Products that are not sold are components of a product that is sold. For example, a bicycle would be sold, but the sheet of metal used to create the bicycle frame would not.
Production.ProductCategory	The most general classification of products. For example, bike or accessory.
Production.ProductCostHistory	The cost of products over time.
Production.ProductDescription	A full description of products in various languages.	Product descriptions are provided in Arabic, English, French, Hebrew, Simplified Chinese, and Thai.
Production.ProductInventory	The inventory level of products by their location. See Production.Location previously mentioned.
Production.ProductListPriceHistory	The list price of products over time.
Production.ProductModel	The product models associated with products. For example, Mountain-100 or LL Touring Frame.	The CatalogDescription column contains additional product information by using the xml data type. The Instructions column contains product manufacturing instructions by using the xml data type.
ProductModelProductDescriptionCulture	Cross-reference between product models, product descriptions, and the languages the description has been localized to.
Production.ProductPhoto	Images of products sold by Adventure Works Cycles.	The images are stored by using the varbinary(max) data type.
Production.ProductReview	Customer reviews of Adventure Works Cycles products.
Production.ProductSubcategory	Subcategories of product categories. For example, Mountain, Road, and Touring are subcategories of the category Bike.

Examples

You can use the following queries to view product data and to become familiar with the product-table relationships.

A. Viewing products by category, subcategory, and model

The following example lists all products by category, subcategory, and model. Products that are not categorized are not included. To include all products, change the join on ProductCategory to a full join.

USE AdventureWorks;
GO
SELECT PC.Name AS Category, PSC.Name AS Subcategory,
    PM.Name AS Model, P.Name AS Product
FROM Production.Product AS P
    FULL JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelID
    FULL JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
    JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
ORDER BY PC.Name, PSC.Name ;
GO

B. Viewing product descriptions by product model

Product descriptions are created for each product model. Each description is available in multiple languages. The following example displays each product description in each language.

Note:
Some languages may not display correctly unless the supplemental language support files for complex script and East Asian languages have been installed. To install these files, see the Windows documentation on Regional and Language Options.

USE AdventureWorks;
GO
SELECT PM.ProductModelID, PM.Name AS [Product Model], Description, PL.CultureID, CL.Name AS Language
FROM Production.ProductModel AS PM 
    JOIN Production.ProductModelProductDescriptionCulture AS PL 
        ON PM.ProductModelID = PL.ProductModelID
    JOIN Production.Culture AS CL ON CL.CultureID = PL.CultureID
    JOIN Production.ProductDescription AS PD 
        ON PD.ProductDescriptionID = PL.ProductDescriptionID
ORDER BY PM.ProductModelID ;
GO

C. Viewing single single-level bill of materials list for a parent product

The following example displays all the components that are used to create a specific parent product: ProductAssemblyID.

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

A. Viewing products by category, subcategory, and model

B. Viewing product descriptions by product model

C. Viewing single single-level bill of materials list for a parent product

Concepts

Other Resources

Help and Information