Custom Search
 


How to export Northwind Access database to MySQL


Inside This Article
1Export Northwind Access database to MySQL via ODBC
2. Make northwind a true relational database in MySQL
3Restore northwind database from SQL dump
   
4Northwind database schema
5Implement file directory based image management

Make northwind a true relational database in MySQL



We have so far migrated Northwind database from MS Access to MySQL. If you look closely into the table structures of the migrated tables, you notice that many relational database constraints are either missing or incorrectly created by the migration process. These include incorrect data type for table columns, missing foreign key constraints, no primary keys in tables, no unique constraints.

In this article, we are going to turn these tables into true relational database tables with proper constraints. In addition, some pratical database design tips and best practice are also discussed. They are mainly drawn from my personal experience of working with MySQL in a web environment. Plus some are from a few years of using SQL Server and Oracle.

We use SQLyog free Community Edition for all the changes we are going to do here to turn our primitive MySQL Northwind into a true relational MySQL database.

The very first thing we do is rename table name `order details` to order_details. MySQL does allow spaces to be put in table names but that's not a good practice.

1. Change table type from MyISAM to InnoDB for all tables InnoDB table type is required when creating foreign key relationships. Follow steps below to convert each table to InnoDB type. Below illustrates the steps to convert categories table to InnoDB type. Do the same to all other tables.

  1. Open SQLyog and display all tables in northwind database.

  2. Right click on table name categories and select Alter Table... in the menu. See screenshot below.

    Alter table categories in northwind

  3. When the Alter Table window is displayed, click Advanced Properties button.

    Alter table Advanced properties

  4. In Table type dropdown box, select InnoDB and click OK to confirm.

    Alter table Advanced properties

  5. Then click Alter to complete the change.

2. Change data types Data types define the type of data, data length, and a few other attributes which are specifically associated with the type of data in a column. They are the lowest level of mechanisms that the database engine enforces on data integrity.

Data types in northwind database can be changed by using Alter Table in SQLyog. For example, to change data types in products table, follow these steps:

  1. Log into SQLyog.
  2. Expand northwind database node and display all tables.
  3. Right click table name products in the list.
  4. Select Alter Table... in the popup menu to open the Alter Table window.

The screenshot below is the Alter Table window for table products.

Change northwind data types in products table

A few points worth to be mentioned about data types

  1. When defining integer data type, always make it as unsigned as long as it makes sense. Unsigned integer basically means positive whole numbers only. In most cases negative numbers are not really useful for a column such as ProductID.

  2. Column Discontinued in products table is defined as enum('y', 'n'). It means only two values are acceptable in this column: y for yes and n for no.

    A bad practice I have seen in many cases is that tinyint or char(1) is used to define True/False type of data. Don't define column like that as they allow other values to be store in the column and that defeat the purpose of having the lowest level of data integrity check by MySQL database engine.

  3. Do we really need to use blob data type?

    The Photo column in employees table and Picture column in categories table use mediumblob as data type. Maybe that suits its purpose of use in Access based Northwind database. I changed it to varchar(50) data type in our MySQL based Northwind database. This column is used to store only the image file name (eg. seafood.gif) of the employee photo or product photo. We will then store the actual image file in a file directory such as /photo/emp/ for employee photos and /photo/product/ for product photos.

    This practice increases database performance and simplifies our database maintenance. Also, programming against this type of design is extremely simple. For example, we select the Seafood category image file name from categories table and append it to image directory path - we then have /photo/product/seafood.jpg ready to be used.

    We don't save the photo directory path to the table because the path could change in the future. Define a constant for the path in your preferred scripting or programming languages.

    Here is how to implement file directory based image management for Northwind database.

  4. When you define a column as datetime data type such as OrderDate in orders table, bear in mind that, you can change datetime data type to int or bigint data type and achieve the same result.

    The advantages are mainly related to coding flexibility and increased query performance with indexes on int data type columns.

    For example, if you code a northwind database application front-end in PHP, you can define any point in time as timestamp value which is an integer value containing the number of seconds between the Unix Epoch (January 1 1970 00:00:00 GMT) and the time specified, we can store the integer in OrderDate column.

    When using the data in OrderDate column, we convert them back to date and time format for displaying purpose by using the rich Date and Time Functions in PHP. These integer values are also extremely easy for calculation when we need to add or subtract certain time from it.

    Here are some of the integer values for addition or subtraction calculation up to the accuracy of a second.

    1 minute = 60 seconds
    1 hour = 3,600 seconds = 60 minutes * 60 seconds
    1 day = 86,400 seconds = 24 hours * 60 minutes * 60 seconds
    1 week = 604,800 seconds = 7 days * 24 hours * 60 minutes * 60 seconds

    For detailed information about using int or bigint data type for date and time values, refer to article How to use Date and Time data as integer value in PHP and MySQL.

  5. When defining string type of data, consider its length in practical use.

    For example, if column always stores fixed length of data such as MD5 32-character long hashed value, define the column as char(32) rather than varchar(32) or other length. Indexes on char column always make queries more efficient than indexes on varchar columns.

  6. This one is not related to northwind database but worth mentioning as it's one of my favorite MySQL features.

    When you create an IP address column, always define it as unsigned int data type. This can greatly improve query performance and make IP range calculation and comparison much easier.

    For information about how to convert from IP address to integer representation and from integer back to dotted IP address, refer to article How to enforce data type constraint for more information on this topic.

    Integers also make IP range comparison much easier. It deserves another article on its own - I will link to it from here when it's done.

3. Set up primary keys Primary keys are defined by using Alter Table menu command in SQLyog. See example and screenshot above.

Table below shows the primary keys we need to create for the tables:

Table Primary key column Column Attribute
categories CategoryID int(10) unsigned NOT NULL auto_increment
customers CustomerID varchar(5) NOT NULL
employees EmployeeID int(10) unsigned NOT NULL auto_increment
suppliers SupplierID int(10) unsigned NOT NULL auto_increment
shippers ShipperID int(10) unsigned NOT NULL auto_increment
products ProductID int(10) unsigned NOT NULL auto_increment
orders OrderID int(10) unsigned NOT NULL auto_increment
order_details ID int(10) unsigned NOT NULL auto_increment

These primary keys are mainly created by following the primary key setups in Access Northwind database. I made only one change here which was in order_details table.

I created a new column named ID in order_details table which is used as primary key with auto_increment attribute. We will later create a composite unique index on CustomerID and EmployeeID column. This way, we created a single column primary key rather than a composite primary key on both CustomerID and EmployeeID. This is the best practice in database design - always have a single column auto incremented artificial primary key (also known as surrogate key).

Even though order_details is not used as a primary key table per se, it could be later used as so when business requirements change. So having a stupid auto increment column as primary key could simplify our foreign key relationship setup as well as programming in the future for whatever applications you are going to build based on northwind database.

Possible change

Another change I could possibly make is in customers table. I can create a new column named ID and use it as primary key. But here for the reason of simplicity, I kept using CustomerID column as primary key (the same as the one in Access northwind database).

Bear in mind that this is not the best database design practice. We should always have an integer column as primary key in almost all cases. Here CustomerID is a 5-character long column and it has intrinsic business rule built into it. What if our business requirements change in the future that a 10-character long CustomerID is needed? Then we'll have to go thru all sorts of trouble of changing our current primary key data and foreign key data. Should we have had a stupid integer column as primary key in the first place, the change in business requirements would not have any impact on our database structure.

4. Create unique index As its name suggests, unique index enforces that the data in a column is always unique, no duplicates can occur.

There are two unique indexes to be created in our Northwind MySQL database:

  1. First unique index is created on CategoryName column in categories table.

  2. Second unique index is created on a combination of two columns - OrderID and ProductID in order_details table.

Note that I created a new ID column in order_details as primary key. So we need to add unique index on OrderID and ProductID because the composition of them is not primary key anymore as in the case of Access Northwind database.

How to create unique index in SQLyog

Below are steps to create unique index on OrderID and ProductID column in order_details table.

  1. Log into SQLyog.
  2. Expand northwind database node and display all tables.
  3. Right click on table name order_details in the list.
  4. Select Manage Indexes... in the popup menu.
  5. When the Manage Indexes window is opened, click button New. See screenshot below.
  6. When the Create New Indexes window is opened, tick OrderID and ProductID column, tick Unique checkbox, and then name the index as Uidx_OrderID_ProductID. See screenshot below.
  7. Click Apply button to create the unique index.

Manage Indexes menu:
Menu item to create index in northwind order_details table

Create new index:
Create new index in northwind MySQL database

Create unique Index:
Create new unique index in northwind order_details table

5. Set up foreign keys Foreign key constraint defines how referential integrity is enforced between two tables.

Referential integrity refers to the requirement that if a foreign key in the referencing table (foreign key table) is not null, it must match a primary key in the referenced table (primary key table). This means that relationship between the primary key and the foreign key must always be maintained so that we will never have orphaned records in the referencing table.

Here are some of the common terms used by database designers when defining foreign key relationships:

  • Primary key table = Referenced table = Parent table = Master table

  • Foreign key table = Referencing table = Child table = Detailed table

  • Source column = Foreign key column in foreign key table

  • Target column = Primary key column in primary key table

Here is a summary of foreign keys we created for our MySQL Northwind database.

Foreign key table (Source column) Primary key table (Target column)
employees (ReportsTo) employees (EmployeeID)
orders (CustomerID) customers (CustomerID)
orders (EmployeeID) employees (EmployeeID)
orders (ShipVia) shippers (ShipperID)
products (CategoryID) categories (CategoryID)
products (SupplierID) suppliers (SupplierID)
order_details (OrderID) orders (OrderID)
order_details (ProductID) products (ProductID)

Note that ReportsTo column in employees table foreign key which points back to EmployeeID column in the same table. This is self referencing. Apparently in Northwind company, the boss is Andrew Fuller of EmployeeID 2. This is because his ReportsTo data is NULL which means he reports to no one.

How to create foreign key relationships in SQLyog

Below are steps to create foreing key relationship between products and categories table.

  1. Log into SQLyog.
  2. Expand northwind database node and display all tables.
  3. Right click on table name products in the list.
  4. Select Relationships/Foreign Keys... in the popup menu. See screenshot below.
  5. When the Manage Relationships window is opened, click button New. See screenshot below.
  6. When the Create Relationships window is opened, select categories in the Referenced table drop down box. See screenshot below.
  7. Select CategoryID in both Target Column and Source Column.
  8. Name the foreign key relationship as FK_products_category_id.
  9. Click Create button to create it.

Relationships/Foreign Keys menu:
Menu item to create foreign key relationships in northwind products table

Create new foreign key relationship:
Create new foreign key relationship

Create foreign key relationship between products and categories table:
Create foreign key relationship between products and categories table

Consider the following facts when you create foreign key relationships in MySQL

When you create Relationship/Foreign keys, bear in mind that the foreign key can be successfully created only when all the following conditions are met. That is, these conditions must exist before a foreign key constraint can be successfully created.

  • Source column and target column must be the same data type.

    Note that a common pitfall that could catch you from time to time is that, if the target column is int data type, Source column must also be int data type. It can't be tinyint or smallint data type.

  • Source column and target column must use the same charset and collation.

  • The foreign key table must have index(es) already created on the Source column(s).

  • Both tables must be InnoDB, SolidDB, or PBXT table type and they must not be TEMPORARY tables.

  • If the Source column already has data in it, the exact same data must also exist in target column in primary key table.

  • The name of the foreign key must not exist when you create the relationship.

    This is another common catch that could easily be overlooked.

    Each foreign key created in MySQL has a unique name. If you have a foreign key named as FK_orders, you can't have another foreign key with the same name. It's very simple but can be easily overlooked when you create foreign key relationship using SQLyog.

  • The primary key table and target column(s) must already exist.

  • If the foreign key is based on more than one column in the foreign key table, the index created on both foreign key table and primary key table must meet these conditions:

    1. The index created on foreign key table must contain all the columns that are used in foreign key constraint.
    2. The index created on primary key table must contain all the same columns that are used in foreign key constraint of foreign key table.
    3. The order of columns in the index of both foreign key table and primary key table must be the same.
    4. In the foreign key table, there must be an index where the Source columns are listed as the first columns in the same order.
    5. In the primary key table, there must be an index where the target columns are listed as the first columns in the same order.

    Isn't that killing your brain? Well, maybe not for Google's engineers. I was nearly lost when I was writing this, not to mention I tested them with no confidence that I have covered all possibilities - I can't be bothered. My suggestion is you'd better not to create a foreign key relationship that involves more than one column.

    That's why I removed the primary key which involved OrderID and ProductID column in order_details table and created a new ID column as primary key with auto increment int data type. The fact that order_details is not used as primary key table in northwind database does not guarantee it will not be used as primary key table in the future when business requirements change.

6. Set up column null values and default values Use SQLyog Alter Table window to set up NULL values and default values for columns.

I always try to avoid the use of null values in a table. If possible, I create a meaningful default value for the column and then make the column NOT NULL.

The default value is used when no value is provide for an insert sql statement. Each column can have only one default value.

If a column allows NULL value, null (the absence of a value) will be added as row data for the column when insert or update a column's data.

Note that NULL is allowed for ReportsTo in employees table because Andrew Fuller reports to no one. In this case, NULL is the only choice.

7. Create Check constraints

MS Access Northwind database has some Validation Rules on columns that limit the values that can be entered into columns (called fields in Access).

Here is how MS Access Northwind database defines Validation Rule on BirthDate field in Employees table.

Validation Rule in Access is known as CHECK constraint in other database systems such as Oracle. However, MySQL does not support CHECK constraint up to MySQL version 5.x. Please note that, MySQL does allow you to define CHECK constraints in CREATE TABLE statement and the satement is parsed fine but MySQL silently ignore it by all MySQL storage engines.

To achieve the same effect as CHECK constraint, you can implement triggers to mimic the behavior of a CHECK constraint. You may define a BEFORE INSERT trigger on the table you want to create this CHECK constraint. This type of triggers has not been implemented in our MySQL Northwind database here.

8. Northwind database schema and database script download Here you can download Northwind database SQL script in MySQL format:

Download northwind database MySQL script

On next page, we will talk about how to restore and backup Northwind database in MySQL.


Copyright© GeeksEngine.com




Inside This Article
1Export Northwind Access database to MySQL via ODBC
2. Make northwind a true relational database in MySQL
3Restore northwind database from SQL dump
   
4Northwind database schema
5Implement 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

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy