Joins/Categories

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 solution to 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. When the Category is included as a text field in this way, administrators can make typographical errors when applying a category to a book.

Data Domains/Linked Tables

In order to promote consistent data, we can place all our categories in a separate table:

create table Categories(
CategoryID int unsigned not null auto_increment primary key,
Category varchar(120)
);

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