CYCLE_STORE Database Schema Reference

The CYCLE_STORE database is a SQL Server schema that holds the product catalog for the Legacy Cycle Store application. It is modelled after the AdventureWorks sample database and organizes all product data under a dedicated Production schema. Three tables form the backbone of the catalog: ProductCategory groups products into top-level categories such as Bikes or Accessories, ProductSubcategory subdivides those categories into finer groupings like Road Bikes or Helmets, and Product stores the individual SKUs with their pricing, physical attributes, and lifecycle dates. All tables include rowguid and ModifiedDate columns to support replication and audit tracking.

This schema is a targeted subset of the full AdventureWorks Production schema. It includes only the three tables required to power the cycle store product catalog. Tables present in the original AdventureWorks database — such as WorkOrder, TransactionHistory, BillOfMaterials, and manufacturing-related tables — are intentionally omitted.

Database and Schema

CREATE DATABASE [CYCLE_STORE]
CREATE SCHEMA [Production]

The Production schema acts as a namespace that groups all product-related objects together within the CYCLE_STORE database. Using a named schema rather than the default dbo schema serves two purposes: it mirrors the structure of the source AdventureWorks database, making it straightforward to port queries and stored procedures from the reference dataset, and it allows database permissions to be granted at the schema level so that application roles can be scoped to Production objects without touching unrelated tables.

Production.ProductCategory

ProductCategory is the top-level grouping table. Each row represents a broad product category such as Bikes, Components, Clothing, or Accessories.

CREATE TABLE [Production].[ProductCategory](
    [ProductCategoryID] [int] NOT NULL PRIMARY KEY,
    [Name] [nvarchar](50) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)

Column	Type	Nullable	Description
`ProductCategoryID`	`int`	NOT NULL (PK)	Surrogate primary key that uniquely identifies the category. Referenced by `ProductSubcategory.ProductCategoryID`.
`Name`	`nvarchar(50)`	NOT NULL	Human-readable category name, e.g. `Bikes`, `Accessories`.
`rowguid`	`uniqueidentifier`	NOT NULL	Globally unique identifier for the row, used to support merge replication and row-level tracking across distributed databases.
`ModifiedDate`	`datetime`	NOT NULL	Timestamp of the most recent update to this row. Maintained by the application layer or an `UPDATE` trigger.

Production.ProductSubcategory

ProductSubcategory refines the top-level categories into more specific groupings. For example, the Bikes category contains subcategories such as Road Bikes, Mountain Bikes, and Touring Bikes. Every subcategory belongs to exactly one parent category.

CREATE TABLE [Production].[ProductSubcategory](
    [ProductSubcategoryID] [int] NOT NULL PRIMARY KEY,
    [ProductCategoryID] [int] NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)

Column	Type	Nullable	Description
`ProductSubcategoryID`	`int`	NOT NULL (PK)	Surrogate primary key for the subcategory. Referenced by `Product.ProductSubcategoryID`.
`ProductCategoryID`	`int`	NOT NULL (FK)	Foreign key to `ProductCategory.ProductCategoryID`. Determines which top-level category this subcategory belongs to.
`Name`	`nvarchar(50)`	NOT NULL	Human-readable subcategory name, e.g. `Road Bikes`, `Helmets`.
`rowguid`	`uniqueidentifier`	NOT NULL	Globally unique identifier for the row, used for replication.
`ModifiedDate`	`datetime`	NOT NULL	Timestamp of the most recent update to this row.

Production.Product

Product is the central table of the catalog. Each row represents a single purchasable SKU with its full set of physical, pricing, and lifecycle attributes. Products may optionally belong to a subcategory; products without a subcategory are treated as uncategorized items.

CREATE TABLE [Production].[Product](
    [ProductID] [int] NOT NULL PRIMARY KEY,
    [Name] [nvarchar](50) NOT NULL,
    [ProductNumber] [nvarchar](25) NOT NULL,
    [MakeFlag] [bit] NOT NULL,
    [FinishedGoodsFlag] [bit] NOT NULL,
    [Color] [nvarchar](15) NULL,
    [SafetyStockLevel] [smallint] NOT NULL,
    [ReorderPoint] [smallint] NOT NULL,
    [StandardCost] [money] NOT NULL,
    [ListPrice] [money] NOT NULL,
    [Size] [nvarchar](5) NULL,
    [SizeUnitMeasureCode] [nchar](3) NULL,
    [WeightUnitMeasureCode] [nchar](3) NULL,
    [Weight] [decimal](8, 2) NULL,
    [DaysToManufacture] [int] NOT NULL,
    [ProductLine] [nchar](2) NULL,
    [Class] [nchar](2) NULL,
    [Style] [nchar](2) NULL,
    [ProductSubcategoryID] [int] NULL,
    [ProductModelID] [int] NULL,
    [SellStartDate] [datetime] NOT NULL,
    [SellEndDate] [datetime] NULL,
    [DiscontinuedDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
)

Column	Type	Nullable	Description
`ProductID`	`int`	NOT NULL (PK)	Surrogate primary key that uniquely identifies the product SKU.
`Name`	`nvarchar(50)`	NOT NULL	Full product name as displayed in the storefront, e.g. `HL Road Frame - Black, 58`.
`ProductNumber`	`nvarchar(25)`	NOT NULL	Alphanumeric product code used for inventory and ordering, e.g. `FR-R92B-58`.
`MakeFlag`	`bit`	NOT NULL	`1` = manufactured in-house; `0` = purchased from a vendor.
`FinishedGoodsFlag`	`bit`	NOT NULL	`1` = the product is a saleable finished good; `0` = a component or subassembly not sold directly.
`Color`	`nvarchar(15)`	NULL	Product color, e.g. `Black`, `Silver`, `Red`. `NULL` for products that have no specific color.
`SafetyStockLevel`	`smallint`	NOT NULL	The minimum inventory quantity that should be on hand before a replenishment order is triggered.
`ReorderPoint`	`smallint`	NOT NULL	The inventory level at which a purchase order or work order should be initiated.
`StandardCost`	`money`	NOT NULL	The internal manufacturing or procurement cost per unit, used for margin calculations.
`ListPrice`	`money`	NOT NULL	The retail selling price displayed to customers. A value of `0.00` indicates the product is not sold individually.
`Size`	`nvarchar(5)`	NULL	Product size descriptor, e.g. `58`, `M`, `XL`. Unit of measure is given by `SizeUnitMeasureCode`.
`SizeUnitMeasureCode`	`nchar(3)`	NULL	Three-character unit of measure code for the `Size` column, e.g. `CM` for centimetres.
`WeightUnitMeasureCode`	`nchar(3)`	NULL	Three-character unit of measure code for the `Weight` column, e.g. `LB` for pounds or `G` for grams.
`Weight`	`decimal(8,2)`	NULL	Product weight. `NULL` for products where weight is not tracked.
`DaysToManufacture`	`int`	NOT NULL	Number of days required to manufacture the product. `0` for purchased components.
`ProductLine`	`nchar(2)`	NULL	Broad product line classification. `R` = Road, `M` = Mountain, `T` = Touring, `S` = Standard/Other. `NULL` for products not assigned to a line.
`Class`	`nchar(2)`	NULL	Quality tier. `H` = High, `M` = Medium, `L` = Low. `NULL` for unclassified products.
`Style`	`nchar(2)`	NULL	Target customer style. `W` = Womens, `M` = Mens, `U` = Universal. `NULL` for products without a style designation.
`ProductSubcategoryID`	`int`	NULL (FK)	Foreign key to `ProductSubcategory.ProductSubcategoryID`. `NULL` for products not assigned to a subcategory.
`ProductModelID`	`int`	NULL	Identifier linking the product to a model group (e.g., all frame sizes of the same design share one `ProductModelID`). Not enforced by a FK constraint in this subset schema.
`SellStartDate`	`datetime`	NOT NULL	The date the product became available for sale. Used to filter the active product catalog.
`SellEndDate`	`datetime`	NULL	The date the product was retired from sale. `NULL` for currently active products.
`DiscontinuedDate`	`datetime`	NULL	The date the product was fully discontinued and removed from the system. `NULL` for active products.
`rowguid`	`uniqueidentifier`	NOT NULL	Globally unique identifier for the row, used for replication and external system integration.
`ModifiedDate`	`datetime`	NOT NULL	Timestamp of the most recent update to this row.

Relationships

The three tables form a simple hierarchy connected by foreign key relationships.

ProductCategory
    └── ProductSubcategory (ProductCategoryID → ProductCategory.ProductCategoryID)
            └── Product (ProductSubcategoryID → ProductSubcategory.ProductSubcategoryID)

ProductSubcategory.ProductCategoryID → ProductCategory.ProductCategoryID Every subcategory must belong to a parent category. This is a mandatory (NOT NULL) foreign key, so orphaned subcategories cannot exist.
Product.ProductSubcategoryID → ProductSubcategory.ProductSubcategoryID A product may optionally be assigned to a subcategory. Because ProductSubcategoryID is nullable, products can exist without a subcategory assignment — for example, raw materials or internal components that are not part of the retail hierarchy.

When querying the full catalog hierarchy, use a LEFT JOIN from Product to ProductSubcategory and a further INNER JOIN from ProductSubcategory to ProductCategory to correctly include products with no subcategory while always returning a valid category for those that have one.

Seed Data

The CYCLE_STORE_Schema_data.sql script bundled with the repository contains INSERT statements that populate all three tables with a representative set of AdventureWorks products. Running this script after schema creation gives you a working dataset of bikes, components, and accessories that the MVC application can immediately browse, filter, and display without requiring you to manually enter product records. To apply the seed data after deploying the schema, connect to the RDS instance with SQL Server Management Studio or sqlcmd and execute:

USE [CYCLE_STORE];
GO
-- Run the contents of CYCLE_STORE_Schema_data.sql here

Overview

Getting Started

Application Deep Dive

Modernization Path

Reference

Database and Schema

Production.ProductCategory

Production.ProductSubcategory

Production.Product

Relationships

Seed Data

Build docs developers (and LLMs) love

Overview

Getting Started

Application Deep Dive

Modernization Path

Reference

Documentation Index

​Database and Schema

​Production.ProductCategory

​Production.ProductSubcategory

​Production.Product

​Relationships

​Seed Data

Build docs developers (and LLMs) love

Database and Schema

Production.ProductCategory

Production.ProductSubcategory

Production.Product

Relationships

Seed Data