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
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:
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:
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.
