In relational databases using foreign keys is a standard approach of implementing the data normalization, connecting several tables to each other and avoiding storing of repeating blocks of information.
For example, let’s say you have a table where you store orders data. Each order refers to a client, a product, probably to a provider or employee. If we store this data in the same table, and if a same client does several orders, or if a same product is purchased several times, we would need to store the full names multiple times – this is very ineffective, slows down search, makes listing the names problematic and generally isn’t an optimal ‘normal’ form of storing data in a database.
What is usually done instead, is ‘normalizing’ this data and splitting it to several tables: clients, products, employees, orders, etc. Each table would store only unique data, and the main table would refer others through unique identifiers – such as client_id, product_id, employee_id, etc. Such ‘connecting’ fields are called foreign keys as they refer to data from foreign tables.
A definition of foreign keys on Microsoft’s website:
“In the context of relational databases, a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table or the same table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table.”
wpDataTables also has an implementation for connecting several tables to each other, by the Foreign key concept. Let’s go through an example to see how it works.
In this example we will be using famous ‘Northwind sample database‘ which is widely used for SQL tutorials. You can download the dump of the database that we used on this link if you would like to reproduce the steps of this tutorial.
Our version of ‘Northwind’ database has 4 MySQL tables:
- employees – stores information about employees – ID, First name, Last name, Email address, and other fields,
- shippers – stores information about shippers / delivery companies – ID, company name, and other fields,
- customers – stores information about customers – ID, company, first and last names, job title, and other fields,
- orders – ‘summary’ table about the orders, which stores information about the orders (order date, shipped date, taxes and fees), with “links” to the other three tables through ID fields – employee_id, customer_id, shipper_id. These will be our foreign keys.
To configure these tables and foreign keys in wpDataTables, first we need to create first three wpDataTables based on MySQL queries. You can follow the instructions described in the tutorial on creating MySQL-based tables, and use these queries:
- SELECT * FROM employees;
- SELECT * FROM shippers;
- SELECT * FROM customers;
As a result you should have 3 wpDataTables – Employees, Shippers and Customers.
Now we can create the “orders” table the same way, based on the “SELECT * FROM orders” MySQL query. We will see IDs in the columns related to the customers, employees, and shippers. This is not very useful if you want to edit it in the frontend or to delegate editing of this table to someone who doesn’t have access to other tables, as it’s not clear what do all these numbers stand for – so let’s configure table relations to replace these with something more meaningful.
To create a relation for a specific column, and to show data from a different wpDataTable in it, first click on “Column settings” button for this column or click “Complete column list” button from where you can open column configuration panel.
In the the column settings sliding panel navigate to the “Data” tab and change column type from “Integer” to “String”.
After you change the column type to String, a “Possible values for column” dropdown will appear. Choose the “Use values from another wpDataTable (foreign key)” option.
Under “Use values from another wpDataTable” click on the button to start configuring the relations.
In the modal that pops up you will find three dropdowns:
- Choose a source wpDataTable – the remote or ‘foreign‘ table from which we want to pull the data. For example, for ‘employee_id‘ column the remote table will be ‘Employees‘ that we previously configured.
- Display value – column from the chosen remote table, values from which will be shown in the current column instead of the IDs. For example for Employees we may want to show the employee’s last name – so we can choose the last_name field.
- Store value – column from the chosen table, values from which will be used as the identifier, or foreign key. Values from this column will be stored in the current table (e.g. in the Orders table in our example) – usually these columns are called id, or wdt_ID. In this example it will be id.
When you choose the values in all 3 inputs, you can click ‘Save‘ to save the relation and close this modal.
All three relation parameters are shown in this section.
After you click Apply and close the modal you will see that data in that column is changed and shows employee’s last name instead of id – this means that the relation is configured.
Let’s repeat the same for the customer_id field to show customer’s last name instead of the ID, and for the shipper_id field to show shipping company name instead of ID. When you do that the table becomes much more readable. Sorting will work naturally, based on the remote values, not on the stored ID integer values.
It is very important to note that foreign key relations do not just allow to show the data from the remote table. If a column is configured as a foreign key, possible values for the advanced filter will be pulled from a remote table, and the filtering will work based on the remote column that was configured to be displayed. To try that just choose the Selectbox, Checkbox in the filter type selectbox for this column. The options will be populated from the remote table, and the filtering will work naturally as for all other tables. Also the default ‘text‘ filter will also work naturally, and filter by the remote values and not by the stored id’s.
One more important notice is that editing for columns that are configured as foreign keys is also supported out of the box. The only limitation is that the only possible editor input type for such columns is the single-value selectbox, as the options are limited with what the remote table contains.
If you enable editing for a column configured as a foreign key, and choose a single-value selectbox as en editor input type, editing will work naturally, and the IDs will be stored in the column.