Generated Northwind database schema in MySQL

Inside This Article

1.	Export Northwind Access database to MySQL via ODBC
2.	Make northwind a true relational database in MySQL
3.	Restore northwind database from SQL dump

4.	Northwind database schema
5.	Implement file directory based image management

Northwind database schema

List of database tables in MySQL Northwind. This schema is generated by SQLyog Community Edition- MySQL GUI v6.16 for MySQL - 5.0.37.

Log into SQLyog.
Right click northwind database node.
Select Create Schema For Database In HTML... in the popup menu. See screenshot below.
When the Create Schema window is opened, click Create button.

Generate Northwind MySQL database schema by SQLyog

Create Northwind MySQL database schema by SQLyog

categories
customers
employees
order_details
orders
products
shippers
suppliers

categories

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
CategoryID	tinyint(5) unsigned	(NULL)	NO	PRI	(NULL)	auto_increment	select,insert,update,references
CategoryName	varchar(15)	utf8_unicode_ci	NO	UNI			select,insert,update,references
Description	mediumtext	utf8_unicode_ci	NO				select,insert,update,references
Picture	varchar(50)	utf8_unicode_ci	NO				select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
categories	0	PRIMARY	1	CategoryID	A	2	(NULL)	(NULL)		BTREE
categories	0	Uidx_categories_category_name	1	CategoryName	A	2	(NULL)	(NULL)		BTREE

Top

customers

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
CustomerID	varchar(5)	utf8_unicode_ci	NO	PRI			select,insert,update,references
CompanyName	varchar(40)	utf8_unicode_ci	NO	MUL			select,insert,update,references
ContactName	varchar(30)	utf8_unicode_ci	NO		Unknown		select,insert,update,references
ContactTitle	varchar(30)	utf8_unicode_ci	NO				select,insert,update,references
Address	varchar(60)	utf8_unicode_ci	NO				select,insert,update,references
City	varchar(15)	utf8_unicode_ci	NO	MUL			select,insert,update,references
Region	varchar(15)	utf8_unicode_ci	NO	MUL			select,insert,update,references
PostalCode	varchar(10)	utf8_unicode_ci	NO	MUL			select,insert,update,references
Country	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
Phone	varchar(24)	utf8_unicode_ci	NO				select,insert,update,references
Fax	varchar(24)	utf8_unicode_ci	NO				select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
customers	0	PRIMARY	1	CustomerID	A	91	(NULL)	(NULL)		BTREE
customers	1	idx_customers_company_name	1	CompanyName	A	91	(NULL)	(NULL)		BTREE
customers	1	idx_customers_city	1	City	A	91	(NULL)	(NULL)		BTREE
customers	1	idx_customers_region	1	Region	A	45	(NULL)	(NULL)		BTREE
customers	1	idx_customers_postalcode	1	PostalCode	A	91	(NULL)	(NULL)		BTREE

Top

employees

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
EmployeeID	int(10) unsigned	(NULL)	NO	PRI	(NULL)	auto_increment	select,insert,update,references
LastName	varchar(20)	utf8_unicode_ci	NO	MUL			select,insert,update,references
FirstName	varchar(10)	utf8_unicode_ci	NO				select,insert,update,references
Title	varchar(30)	utf8_unicode_ci	NO				select,insert,update,references
TitleOfCourtesy	varchar(25)	utf8_unicode_ci	NO				select,insert,update,references
BirthDate	datetime	(NULL)	NO				select,insert,update,references
HireDate	datetime	(NULL)	NO				select,insert,update,references
Address	varchar(60)	utf8_unicode_ci	NO				select,insert,update,references
City	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
Region	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
PostalCode	varchar(10)	utf8_unicode_ci	NO	MUL			select,insert,update,references
Country	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
HomePhone	varchar(24)	utf8_unicode_ci	NO				select,insert,update,references
Extension	varchar(4)	utf8_unicode_ci	NO				select,insert,update,references
Photo	varchar(50)	utf8_unicode_ci	NO				select,insert,update,references
Notes	mediumtext	utf8_unicode_ci	YES		(NULL)		select,insert,update,references
ReportsTo	int(10) unsigned	(NULL)	YES	MUL	(NULL)		select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
employees	0	PRIMARY	1	EmployeeID	A	2	(NULL)	(NULL)		BTREE
employees	1	idx_employees_lastname	1	LastName	A	2	(NULL)	(NULL)		BTREE
employees	1	idx_employees_postalcode	1	PostalCode	A	2	(NULL)	(NULL)		BTREE
employees	1	idx_ReportsTo	1	ReportsTo	A	2	(NULL)	(NULL)	YES	BTREE

Foreign Key Relationships

FK Id	Reference Table	Source Column	Target Column	Extra Info
FK_employees_reports_to	employees	`ReportsTo`	`EmployeeID`

Top

order_details

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
ID	int(10) unsigned	(NULL)	NO	PRI	(NULL)	auto_increment	select,insert,update,references
OrderID	int(10) unsigned	(NULL)	NO	MUL			select,insert,update,references
ProductID	int(10) unsigned	(NULL)	NO	MUL			select,insert,update,references
UnitPrice	double unsigned	(NULL)	NO		0		select,insert,update,references
Quantity	smallint(5) unsigned	(NULL)	NO		1		select,insert,update,references
Discount	float unsigned	(NULL)	NO		0		select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
order_details	0	PRIMARY	1	ID	A	1976	(NULL)	(NULL)		BTREE
order_details	0	Uidx_OrderID_ProductID	1	OrderID	A	1976	(NULL)	(NULL)		BTREE
order_details	0	Uidx_OrderID_ProductID	2	ProductID	A	1976	(NULL)	(NULL)		BTREE
order_details	1	FK_order_details_productid	1	ProductID	A	152	(NULL)	(NULL)		BTREE

Foreign Key Relationships

FK Id	Reference Table	Source Column	Target Column	Extra Info
FK_order_details_orderid	orders	`OrderID`	`OrderID`	,
FK_order_details_productid	products	`ProductID`	`ProductID`

Top

orders

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
OrderID	int(10) unsigned	(NULL)	NO	PRI	(NULL)	auto_increment	select,insert,update,references
CustomerID	varchar(5)	utf8_unicode_ci	NO	MUL			select,insert,update,references
EmployeeID	int(10) unsigned	(NULL)	NO	MUL			select,insert,update,references
OrderDate	datetime	(NULL)	NO				select,insert,update,references
RequiredDate	datetime	(NULL)	YES		(NULL)		select,insert,update,references
ShippedDate	datetime	(NULL)	YES	MUL	(NULL)		select,insert,update,references
ShipVia	int(10) unsigned	(NULL)	NO	MUL			select,insert,update,references
Freight	double	(NULL)	NO		0		select,insert,update,references
ShipName	varchar(40)	utf8_unicode_ci	NO				select,insert,update,references
ShipAddress	varchar(60)	utf8_unicode_ci	NO				select,insert,update,references
ShipCity	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
ShipRegion	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
ShipPostalCode	varchar(10)	utf8_unicode_ci	NO	MUL			select,insert,update,references
ShipCountry	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
orders	0	PRIMARY	1	OrderID	A	795	(NULL)	(NULL)		BTREE
orders	1	FK_orders_shipvia	1	ShipVia	A	5	(NULL)	(NULL)		BTREE
orders	1	FK_orders_employeeid	1	EmployeeID	A	17	(NULL)	(NULL)		BTREE
orders	1	idx_orders_shipped_date	1	ShippedDate	A	795	(NULL)	(NULL)	YES	BTREE
orders	1	idx_orders_ship_postalcode	1	ShipPostalCode	A	198	(NULL)	(NULL)		BTREE
orders	1	FK_orders_customer_id	1	CustomerID	A	198	(NULL)	(NULL)		BTREE

Foreign Key Relationships

FK Id	Reference Table	Source Column	Target Column	Extra Info
FK_orders_customer_id	customers	`CustomerID`	`CustomerID`	,
FK_orders_employeeid	employees	`EmployeeID`	`EmployeeID`	,
FK_orders_shipvia	shippers	`ShipVia`	`ShipperID`

Top

products

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
ProductID	int(10) unsigned	(NULL)	NO	PRI	(NULL)	auto_increment	select,insert,update,references
ProductName	varchar(40)	utf8_unicode_ci	NO	MUL			select,insert,update,references
SupplierID	int(10) unsigned	(NULL)	NO	MUL			select,insert,update,references
CategoryID	tinyint(5) unsigned	(NULL)	NO	MUL			select,insert,update,references
QuantityPerUnit	varchar(20)	utf8_unicode_ci	NO				select,insert,update,references
UnitPrice	double	(NULL)	NO		0		select,insert,update,references
UnitsInStock	smallint(5) unsigned	(NULL)	NO		0		select,insert,update,references
UnitsOnOrder	smallint(5) unsigned	(NULL)	NO		0		select,insert,update,references
ReorderLevel	smallint(5) unsigned	(NULL)	NO		0		select,insert,update,references
Discontinued	enum('y','n')	utf8_unicode_ci	NO		n		select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
products	0	PRIMARY	1	ProductID	A	77	(NULL)	(NULL)		BTREE
products	1	FK_products_categoryid	1	CategoryID	A	19	(NULL)	(NULL)		BTREE
products	1	FK_products_supplierid	1	SupplierID	A	77	(NULL)	(NULL)		BTREE
products	1	idx_products_product_name	1	ProductName	A	77	(NULL)	(NULL)		BTREE

Foreign Key Relationships

FK Id	Reference Table	Source Column	Target Column	Extra Info
FK_products_categoryid	categories	`CategoryID`	`CategoryID`	,
FK_products_supplierid	suppliers	`SupplierID`	`SupplierID`

Top

shippers

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
ShipperID	int(10) unsigned	(NULL)	NO	PRI	(NULL)	auto_increment	select,insert,update,references
CompanyName	varchar(40)	utf8_unicode_ci	NO				select,insert,update,references
Phone	varchar(24)	utf8_unicode_ci	NO				select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
shippers	0	PRIMARY	1	ShipperID	A	3	(NULL)	(NULL)		BTREE

Top

suppliers

Fields

Field	Type	Collation	Null	Key	Default	Extra	Privileges	Comment
SupplierID	int(10) unsigned	(NULL)	NO	PRI	(NULL)	auto_increment	select,insert,update,references
CompanyName	varchar(40)	utf8_unicode_ci	NO	MUL			select,insert,update,references
ContactName	varchar(30)	utf8_unicode_ci	NO				select,insert,update,references
ContactTitle	varchar(30)	utf8_unicode_ci	NO				select,insert,update,references
Address	varchar(60)	utf8_unicode_ci	NO				select,insert,update,references
City	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
Region	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
PostalCode	varchar(10)	utf8_unicode_ci	NO	MUL			select,insert,update,references
Country	varchar(15)	utf8_unicode_ci	NO				select,insert,update,references
Phone	varchar(24)	utf8_unicode_ci	NO				select,insert,update,references
Fax	varchar(24)	utf8_unicode_ci	NO				select,insert,update,references
HomePage	varchar(255)	utf8_unicode_ci	NO				select,insert,update,references

Indexes

Table	Non unique	Key name	Seq in index	Column name	Collation	Cardinality	Sub part	Packed	Null	Index type	Comment
suppliers	0	PRIMARY	1	SupplierID	A	29	(NULL)	(NULL)		BTREE
suppliers	1	idx_suppliers_product_name	1	CompanyName	A	29	(NULL)	(NULL)		BTREE
suppliers	1	idx_suppliers_postalcode	1	PostalCode	A	29	(NULL)	(NULL)		BTREE

Top

On next page, we'll talk about how to implement file directory based image management for Northwind database - photos for employees and product categories.

Copyright© GeeksEngine.com

Inside This Article

1.	Export Northwind Access database to MySQL via ODBC
2.	Make northwind a true relational database in MySQL
3.	Restore northwind database from SQL dump

4.	Northwind database schema
5.	Implement file directory based image management

Related Articles:

1.	Steps to install PHP 5.x on Windows as a development machine
2.	How to install Apache 2.x web server on Windows
3.	How to connect two different versions of MySQL server on the same computer
4.	How to configure MySQL server 5.1 on Windows
5.	How to install MySQL server 5.1 on Windows with screenshots
6.	Five ways to create include path for PHP
7.	How to use Date and Time data as integer value in PHP and MySQL
8.	How to use Apache Virtual Host to run multiple local websites on Windows
9.	Install all PEAR packages by yourself
10.	How to install PEAR on Windows
11.	How to use PHP and Microsoft SMTP Virtual Server to send emails
12.	How to install PHP server-side scripting language on Windows
13.	How to install Apache 1.3 web server on Windows
14.	How to install two different versions of MySQL server on the same PC
15.	How to configure MySQL server 4.1 on Windows
16.	How to install MySQL server 4.1 on Windows with screenshots
17.	Export Northwind Access database to MySQL via ODBC

Other Recent Articles from the Database SQL category:

1.	Get single records when duplicate records exist
2.	Find duplicate values or non-duplicate values in a table
3.	How to get Top 1 record from Oracle by using PL/SQL
4.	How to get Top N rows from Oracle by using SQL
5.	How the data types in Access Northwind are converted to Oracle
6.	How to do cross table update in Oracle
7.	Export Northwind Access database to MySQL via ODBC

	Custom Search