AdventureWorks Database: table structure and schema overview
The AdventureWorks database (SQL Server) is a sample dataset that models business processes of a fictional manufacturing company.
This page presents table structure, key columns, and relationships used for practical SQL learning and query practice.
The AdventureWorks database contains 10 main tables.
AdventureWorks DB ER diagram
More about the AdventureWorks database: schema, sample queries and all exercises →
List of tables
Address - table of addresses.
- AddressIDunique identifier for each address (PK)
- AddressLine1the first line of the address
- AddressLine2the second line of the address
- Citycity
- StateProvincestate or province
- CountryRegioncountry
- PostalCodepostal code
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (AddressID)
| AddressID |
AddressLine1 |
AddressLine2 |
City |
StateProvince |
CountryRegion |
PostalCode |
rowguid |
ModifiedDate |
| 9 |
8713 Yosemite Ct. |
null |
Bothell |
Washington |
United States |
98011 |
268AF621-76D7-4C78-9441-144FD139821A |
2006-07-01 00:00:00.000 |
Customer - table of customers.
- CustomerIDunique identifier for each customer (PK)
- NameStyle0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order. Default: 0
- Titletitle
- FirstNamename
- MiddleNamemiddle name
- LastNamelast name
- Suffixsuffix
- CompanyNamecompany name
- SalesPersonSalesPerson
- EmailAddressE-mail
- Phonephone number
- PasswordHashpassword hash
- PasswordSaltsalt
- rowguidrowguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (CustomerID)
| CustomerID |
NameStyle |
Title |
FirstName |
MiddleName |
LastName |
Suffix |
CompanyName |
SalesPerson |
EmailAddress |
Phone |
PasswordHash |
PasswordSalt |
rowguid |
ModifiedDate |
| 1 |
0 |
Mr. |
Orlando |
N. |
Gee |
[null] |
A Bike Store |
adventure-works\pamela0 |
orlando0@adventure-works.com |
245-555-0173 |
L/Rlwxzp4w7RWmEgXX+/A7cXaePEPcp+KwQhl2fJL7w= |
1KjXYs4= |
3F5AE95E-B87D-4AED-95B4-C3797AFCB74F |
2005-08-01 00:00:00.000 |
CustomerAddress - customer to address relations.
- CustomerIDidentifier of client in the Customer table
- AddressIDidentifier of address in the Address table
- AddressTypeaddress type
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (CustomerID, AddressID)
- FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
- FOREIGN KEY (AddressID) REFERENCES Address(AddressID)
| CustomerID |
AddressID |
AddressType |
rowguid |
ModifiedDate |
| 29485 |
1086 |
Main Office |
16765338-DBE4-4421-B5E9-3836B9278E63 |
2007-09-01 00:00:00.000 |
Product - table of products.
- ProductIDunique identifier for each product (PK)
- Nameproduct name
- ProductNumberarticle number
- Colorproduct color
- StandardCostproduct price
- ListPriceproduct price in the catalogue
- Sizeproduct size
- Weightproduct weight
- ProductCategoryIDforeign key pointing to ProductCategory table
- ProductModelIDforeign key pointing to ProductModel table
- SellStartDatetimestamp of the sales start date
- SellEndDatetimestamp of the sales end date
- DiscontinuedDatetimestamp of the sales end date
- ThumbNailPhotothumbnail photo of the product
- ThumbnailPhotoFileName
name of the photo thumbnail file
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (ProductID, ProductCategoryID, ProductModelID)
- FOREIGN KEY (ProductCategoryID) REFERENCES ProductCategory(ProductCategoryID)
- FOREIGN KEY (ProductModelID) REFERENCES ProductModel(ProductModelID)
| ProductID |
Name |
ProductNumber |
Color |
StandardCost |
ListPrice |
Size |
Weight |
ProductCategoryID |
ProductModelID |
SellStartDate |
SellEndDate |
DiscontinuedDate |
ThumbNailPhoto |
ThumbnailPhotoFileName |
rowguid |
ModifiedDate |
| 680 |
HL Road Frame - Black, 58 |
FR-R92B-58 |
Black |
1059.3100 |
1431.5000 |
58 |
1016.04 |
18 |
6 |
2002-06-01 00:00:00.000 |
[null] |
[null] |
[binary] |
no_image_available_small.gif |
43DD68D6-14A4-461F-9069-55309D90EA7E |
2008-03-11 10:01:36.827 |
ProductCategory - table of product categories.
- ProductCategoryIDunique identifier for each product category (PK)
- ParentProductCategoryIDID of the parent product category
- Namename of the product category
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (ProductCategoryID)
- FOREIGN KEY (ParentProductCategoryID) REFERENCES ProductCategory(ProductCategoryID)
| ProductCategoryID |
ParentProductCategoryID |
Name |
rowguid |
ModifiedDate |
| 1 |
[null] |
Bikes |
CFBDA25C-DF71-47A7-B81B-64EE161AA37C |
2002-06-01 00:00:00.000 |
ProductDescription - table of product descriptions.
- ProductDescriptionIDunique ID for record (PK)
- Descriptionproduct description
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (ProductDescriptionID)
| ProductDescriptionID |
Description |
rowguid |
ModifiedDate |
| 4 |
Aluminum alloy cups; large diameter spindle. |
DFEBA528-DA11-4650-9D86-CAFDA7294EB0 |
2007-06-01 00:00:00.000 |
ProductModel - table of product models.
- ProductModelIDunique ID for each record (PK)
- Namename of the product model
- CatalogDescriptiondescription in XML format
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (ProductModelID)
| ProductModelID |
Name |
CatalogDescription |
rowguid |
ModifiedDate |
| 1 |
Classic Vest |
[null] |
29321D47-1E4C-4AAC-887C-19634328C25E |
2007-06-01 00:00:00.000 |
ProductModelProductDescription - table of product models descriptions.
- ProductModelIDID of client in the ProductModel table
- ProductDescriptionIDID of address in the ProductDescription table
- Culturelanguage code in ISO format
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (ProductModelID, ProductDescriptionID)
- FOREIGN KEY (ProductModelID) REFERENCES ProductModel(ProductModelID)
- FOREIGN KEY (ProductDescriptionID) REFERENCES ProductDescription(ProductDescriptionID)
| ProductModelID |
ProductDescriptionID |
Culture |
rowguid |
ModifiedDate |
| 1 |
1199 |
en |
4D00B649-027A-4F99-A380-F22A46EC8638 |
2007-06-01 00:00:00.000 |
SalesOrderDetail - table of sales orders details.
- SalesOrderIDforeign key referencing the SalesOrderHeader table
- SalesOrderDetailIDunique identifier of record in the table
- OrderQtyquantity
- ProductIDa foreign key referencing the Product table
- UnitPriceprice per unit of goods
- UnitPriceDiscountprice per unit of product with a discount
- LineTotaltotal amount by line
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (SalesOrderID, SalesOrderDetailID, ProductID)
- FOREIGN KEY (SalesOrderID) REFERENCES SalesOrderHeader(SalesOrderID)
- FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
| SalesOrderID |
SalesOrderDetailID |
OrderQty |
ProductID |
UnitPrice |
UnitPriceDiscount |
LineTotal |
rowguid |
ModifiedDate |
| 71774 |
110562 |
1 |
836 |
356.8980 |
.0000 |
356.898000 |
E3A1994C-7A68-4CE8-96A3-77FDD3BBD730 |
2008-06-01 00:00:00.000 |
SalesOrderHeader - product sales orders.
- SalesOrderIDunique identifier of record in the table (PK)
- RevisionNumberrevision number
- OrderDatetimestamp for creating the order date
- DueDatetimestamp of the order payment date
- ShipDatetimestamp of the date the order was shipped
- Statusorder status
- OnlineOrderFlagonline order (yes/no)
- SalesOrderNumberorder number
- PurchaseOrderNumberpurchase number
- AccountNumberaccount number
- CustomerIDforeign key referencing the Customer table
- ShipToAddressIDforeign key referencing the Address table defines the delivery address
- BillToAddressIDforeign key referencing the Address table defines the account address
- ShipMethoddelivery method
- CreditCardApprovalCode
credit card confirmation code
- SubTotalsubtotal
- TaxAmttaxes
- Freightdelivery cost
- TotalDuetotal
- Commentcomment
- rowguidguid
- ModifiedDatetimestamp of row creation or last update
- PRIMARY KEY, btree (SalesOrderID, CustomerID, ShipToAddressID, BillToAddressID)
- FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
- FOREIGN KEY (ShipToAddressID) REFERENCES Address(AddressID)
- FOREIGN KEY (BillToAddressID) REFERENCES Address(AddressID)
| SalesOrderID |
RevisionNumber |
OrderDate |
DueDate |
ShipDate |
Status |
OnlineOrderFlag |
SalesOrderNumber |
PurchaseOrderNumber |
AccountNumber |
CustomerID |
ShipToAddressID |
BillToAddressID |
ShipMethod |
CreditCardApprovalCode |
SubTotal |
TaxAmt |
Freight |
TotalDue |
Comment |
rowguid |
ModifiedDate |
| 71774 |
2 |
2008-06-01 00:00:00.000 |
2008-06-13 00:00:00.000 |
2008-06-08 00:00:00.000 |
5 |
0 |
SO71774 |
PO348186287 |
10-4020-000609 |
29847 |
1092 |
1092 |
CARGO TRANSPORT 5 |
[null] |
880.3484 |
70.4279 |
22.0087 |
972.7850 |
[null] |
89E42CDC-8506-48A2-B89B-EB3E64E3554E |
2008-06-08 00:00:00.000 |