The relational system allows users to work more closely with phpMyAdmin, as we will see in the following chapters. This chapter explains how to define inter-table relations.
When application developers use PHP and MySQL to build web interfaces or other data manipulation applications, they usually establish relations between tables using the underlying SQL queries. Examples of this would be queries to "get an invoice and all its items" and "get all books by an author".
In the earlier versions of phpMyAdmin, the relational data structure (how tables relate to each other) was not stored within MySQL. Tables were programmatically joined by the applications to generate meaningful results.
This was considered a shortcoming of MySQL by phpMyAdmin developers and users. Therefore, the team started to build an infrastructure to support relations for MyISAM tables, which is now called the phpMyAdmin configuration storage. The infrastructure evolved to support a growing array of special features such as query bookmarks and MIME-based transformations.
Now-a-days, relations between tables are normally defined natively with the FOREIGN KEY feature of the InnoDB and PBXT storage engines. phpMyAdmin supports both this type of relations and those defined for MyISAM.
InnoDB (http://www.innodb.com) is a MySQL storage engine developed by Innobase Oy, a subsidiary of Oracle. Prior to MySQL 5.5, this storage engine may not be available as it must be activated by a system administrator; however, it's the default storage engine in version 5.5.
The PrimeBase XT storage engine or PBXT (http://www.primebase.org) is developed by PrimeBase Technologies. The minimum MySQL required version is 5.1, as this version supports the pluggable storage engine API that is used by PBXT and other third parties to offer alternative storage engines. This transactional storage engine is newer than InnoDB. It is usually installed after downloading it from their website and then going through a compilation step. For some operating systems, a precompiled binary is available—please visit the aforementioned website for download and installation instructions.
When considering the relational aspect, here are the benefits of using the InnoDB or PBXT storage engine for a table:
They support referential integrity based on foreign keys, which are the keys in a foreign (or reference) table. By contrast, using only phpMyAdmin's internal relations (discussed later) brings no automatic referential integrity verification.
The exported structure for InnoDB and PBXT tables contains the defined relations. Therefore, they are easily imported back for better cross-server interoperability.
The foreign key feature of these storage engines can effectively replace the part of phpMyAdmin's configuration storage that deals with relations. We will see how phpMyAdmin interfaces with the InnoDB and PBXT foreign key system.
Note
The other parts of phpMyAdmin's configuration storage (for example, bookmarks) have no equivalent in InnoDB, PBXT, or MySQL. Hence, they are still required to access the complete phpMyAdmin feature set. However, in MySQL 5.x, views are supported, and have similarities with phpMyAdmin's bookmarks.