How to manage MySQL database relations
PHP CRUD Tutorial

With a well structured MySQL database, the relationships between the tables can be used to take full advantage of PHPCG's potential.


The relationships between the different tables in a MySQL database must reflect the logical data structure.

It is therefore important to understand the different kinds of relationships.

PHP CRUD Generator is able to analyze your database and understand all types of relationships, direct and indirect.

Once your relationships are properly established, you will be able to display all the data you need in your Bootstrap Admin Panel's data tables.


Here we'll explain the different kinds of MySQL relationships.

To learn how to setup your database relations please visit How to build-up your database with MySQL Workbench


The different kinds of relationships in a MySQL database

Let's consider a database containing products, customers and their orders.

Each order belongs to a customer, and may contain one or more purchased products. The same product may have been purchased by several different customers, or by the same customer in several orders.

Here's how our Database shema looks like:

MySQL database shema example with direct and indirect relationships
MySQL database shema example with direct and indirect relationships

Direct and indirect relationships

Direct relationships (internal relationships)one-to-one, one-to-many

A direct relationship between two tables A and B is a relationship where table A is directly connected to table B by a foreign key, mandatory or not. This kind of relationship can be one-to-one or one-to-many.

In our example, the orders.customers_id field is a direct relationship to customers.id.
The orders.customers_id is a foreign key.

This relationship makes it possible to associate each order with a customer.

With PHP CRUD Generator, it will allow us to display the customer name of each order in the orders list.
In the CRUD Generator Build List tab, you'll see the relationship shown this way: CRUD Generator direct relationship

Indirect relationships (external relationships)many-to-many

An indirect relationship is used to link several records in table A to several records in table B. For this purpose, we use an intermediate table, usually made up of two foreign keys, each allowing us to identify the records of the A and B tables. This relationship is a many-to-many relationship.

In our example, the products_orders table is the intermediate table. It allows us to associate the ordered products with the orders.
The products_orders.products_id and products_orders.orders_id are both foreign keys.

With PHP CRUD Generator, it will allow to display all the products of each order in a nested table in the orders list.

The Generator also allows you to choose the products fields that you want to display, and if the Admin Panel users are allowed to add/edit/delete products directly from the orders READ list.

With PHP CRUD Generator, it will allow us to display the customer name of each order in the orders list.
In the CRUD Generator Build List tab, you'll see the relationship shown this way:

PHP CRUD Generator indirect relationship

Identifying and non-identifying relationships

Identifying relationships

An Identifying relationship can be defined when two tables are joined by the same identifier. In other words, the records in the second table are inherent elements or characteristics of the first table.

For example: a book is made up of chapters. Each chapter is part of a unique identified book. In this case the foreign key chapters.books_id will be listed as the second primary key of the chapters table.

This type of relationship is nowadays very rarely used. Instead, an indirect relationship is generally used, which is more generalist and less restrictive.

Non-identifying relationships

A Non-identifying relationship is a relationship where two tables are joined by a common membership or attributes or an inclusion.

For example: each order belongs to a customer; each order contains one or more products.

Back to the PHP CRUD Tutorials