Joins/Categories
Limits of List/View
Our List/View application is fine when we only have a few items, for example listing the muffins in a bakery. To be able to display a large number of items via our list page, we added Paging, to span the items over several pages.
But what if I have thousands of items? Paging will help us to span the records over many pages, but users would like to filter down the number of items to what is relevant. We can do this by searching, and returning a list page, similar to a Google Search, or we can categorize our items in advance.
Filtering By Category
When we work with data in a list, we may wish to allow users to 'filter' (limit) the data shown to a certain category. For example, Amazon has millions of books, but I may only want to see books related to 'PHP', which can be thought of as the 'Category'. Books that are 'Westerns' or 'Romance' may not be of interest, so it makes sense to limit the books I wish to see to PHP books or perhaps other programming books. We'll be using 'books' as our data model for these demos.
Category Text Field
For our first attempt to solve this problem, we'll include a 'Category' field to our table:
create table Books(
BookID int unsigned not null auto_increment primary key,
BookTitle varchar(120),
Authors varchar(120),
Category varchar(30),
ISBN varchar(30),
Edition varchar(20),
Description text,
Rating float(1,1),
Price float(6,2)
);
The Category field is a varchar data type, so text like 'PHP' or 'Programming' can be inserted in that field. This strategy has the benefit of simplicity, but is not flexible. When the Category is included as a text field in this way, administrators can make typographical errors when applying a Category to existing items. Also, changing the Category could involve an update to thousands of records. Last, there is no way to further define our Category, for example to provide a 'description' field for the Category.
Categories Table
In our next solution, we'll create a Categories table, and then place a integer field named CategoryID in our Books table to identify the Category of our books.
create table Categories(
CategoryID int unsigned not null auto_increment primary key,
Category varchar(120),
Description text
);
Now we can include the ID number of the category in our Books table:
create table Books(
BookID int unsigned not null auto_increment primary key,
BookTitle varchar(120),
Authors varchar(120),
CategoryID int DEFAULT 0,
ISBN varchar(30),
Edition varchar(20),
Description text,
Rating float(1,1),
Price float(6,2)
);
When we do this we have created an implied link between the tables, based on the CategoryID that appears in both tables. The Category table defines a data domain of valid selections for categories of books. The CategoryID, which is the primary key (unique identifier) in the Categories table appears in the Books table as a foreign key, meaning it is a key to a different, related table.
Linking tables via a foreign key is fundamental to creating a relational database. In our case, since a CategoryID number can appear only once in the Categories table, but can appear many times in the data of the Books table, the relationship thus created is called a one to many relationship.
Entity Relationship Diagram
We can create a diagram to represent our one to many relationship between our Categories table, and Books table:
This diagram depicts an entity relationship model. The one to many relationship is designated by the single line one the one side, and the multiple lines (sometimes called 'crows foot') on the many side.
Here are a couple of tutorials to learn more about creating database tables: Designing Databases
Here is a tutorial about relational database design: Database Design Tutorial
Virtual Table
Now when we return data to our page, we'll want to create a 'virtual table', which means a table of data that is created temporarily by joining two or more tables. We do this because we want the Category from the Categories table, but must use the link between the Categories & Books tables created by the CategoryID placed in both of them:
select BookID, BookTitle, Price, Category from Categories inner join Books on Categories.CategoryID = Books.CategoryID where Books.CategoryID = 1;
Join Syntax
The statement above is called a 'join' statement because it temporarily joins the two tables. The significant syntax that does this is included in the 'from' portion of the SQL statement above:
from Categories inner join Books on Categories.CategoryID = Books.CategoryID
There are several types of join statements, the most common of which is the inner join, used above. In it we identify the two tables we wish to join, then identify the column that matches in the tables:
from Categories inner join Books on Categories.CategoryID = Books.CategoryID
To complete the task, we can identify a single CategoryID in our where statement to them:
select BookID, BookTitle, Price, Category
from Categories inner join Books on Categories.CategoryID = Books.CategoryID
where Books.CategoryID = 1;
The above statement is broken into three parts, select, from & where. The where portion of the statement limits the SQL statement to one (or more) categories, usually by passing the CategoryID into the application via the querystring, similar to what we did with the view page.
Dot Syntax
Notice the use of the dot syntax in the SQL above. This is necessary because there is a 'CategoryID' column in both involved tables. When we work with columns that exist in more than one table, we must fully identify which table each column comes from:
Books.CategoryID
If we fail to do this in a SQL statement, we could get an 'ambiguous column name' warning from MySQL.
Aliases
When we start to add table names to our columns, the length of our SQL statements can grow considerably. This is enough to use an optional feature called an 'alias' to rename key pieces in our SQL statement, on the fly. Here is the same SQL fragment, in which we alias the table name Categories as the letter "c" and the table name Books as the letter "b":
from Categories as c inner join Books as b on c.CategoryID = b.CategoryID
In an alias, even the word 'as' is optional, and frequently eliminated, to tighten up our SQL statement:
from Categories c inner join Books b on c.CategoryID = b.CategoryID
When we use an alias, every item in the table that normally requires the dot syntax will now require the alias. View the following SQL:
select b.CategoryID from Categories c inner join Books b on c.CategoryID = b.CategoryID
It's frequently a good idea to alias the column name in the select portion of the SQL statement as well. This helps us identify the correct name of the column (field) in our while statement in PHP:
select b.Description Description from Categories c inner join Books b on c.CategoryID = b.CategoryID
In the above statement we've aliased b.Description back to our logical name, Description! This looks odd in our SQL statement, but is behaves exactly as we would expect in our PHP page.
Here is some further discussion on using table aliases
Test Your SQL Statements
It's usually a good idea to test (and store) candidate SQL statements via the MySQL command line before placing them in your applications. Here are the first two we can use:
select BookID, BookTitle, Price
from Categories c inner join Books b on c.CategoryID = b.CategoryID
where c.CategoryID = 1;
The above is a sample SQL statement for our new list page. Below is a SQL statement for our new categories page:
select CategoryID, Category, Description from Categories order by Category asc;
Note how much eaiser the categories SQL statement is on the eyes! This is because the categories page only needs to reference one table.
Categories Page
Adding the categories page to our List/View application changes it to Categories/List/View, and in that order. The categories page becomes the entry point to the application, replacing the list page. The categories page will provide links to the list page via loaded querystring links to the list page, just as the list page provides loaded querystring links to the view page. The list & view pages will need to be changed to accommodate the loaded querystring links, since the list page is no longer the entry point.
