Inside This ArticleMake 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.
Open SQLyog and display all tables in northwind database.
Right click on table name categories and select Alter Table... in the menu. See screenshot below.
When the Alter Table window is displayed, click Advanced Properties button.
In Table type dropdown box, select InnoDB and click OK to confirm.
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:
- Log into SQLyog.
- Expand northwind database node and display all tables.
- Right click table name products in the list.
- Select Alter Table... in the popup menu to open the Alter Table window.
The screenshot below is the Alter Table window for table products.
A few points worth to be mentioned about data types
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.
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.
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.
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.
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.
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:
First unique index is created on CategoryName column in categories table.
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.
- Log into SQLyog.
- Expand northwind database node and display all tables.
- Right click on table name order_details in the list.
- Select Manage Indexes... in the popup menu.
- When the Manage Indexes window is opened, click button New. See screenshot below.
- 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.
- Click Apply button to create the unique index.
Manage Indexes menu:
Create new index:
Create unique Index:
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.
- Log into SQLyog.
- Expand northwind database node and display all tables.
- Right click on table name products in the list.
- Select Relationships/Foreign Keys... in the popup menu. See screenshot below.
- When the Manage Relationships window is opened, click button New. See screenshot below.
- When the Create Relationships window is opened, select categories in the Referenced table drop down box. See screenshot below.
- Select CategoryID in both Target Column and Source Column.
- Name the foreign key relationship as FK_products_category_id.
- Click Create button to create it.
Relationships/Foreign Keys menu:
Create new foreign key relationship:
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:
- The index created on foreign key table must contain all the columns that are used in foreign key constraint.
- The index created on primary key table must contain all the same columns that are used in foreign key constraint of foreign key table.
- The order of columns in the index of both foreign key table and primary key table must be the same.
- In the foreign key table, there must be an index where the Source columns are listed as the first columns in the same order.
- 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
Related Articles:
Other Recent Articles from the Database SQL category:
|
