Table of Contents
Video tutorial
Configuring table relations with foreign keys
So, what is a foreign key? In relational databases, using foreign keys is a standard approach to data normalization, by connecting several tables to each other and avoiding the necessity of storing repeating blocks of information.
For example, if you have a table where you store orders data, and each order refers to a client, a product, a provider, or an employee. If we store this data in the same table, and if the same client makes several orders, or if the same product is purchased several times, we would need to store the full names multiple times. This is a very ineffective approach. It slows down search, makes listing the names problematic, and in general is not the optimal or ‘normal’ form of storing data in a database.
What is usually done instead, is ‘normalizing’ this data and splitting it among several tables: clients, products, employees, orders, etc. Each table therefore, would store only unique data, and the main table would refer to the others through unique identifiers – such as client_id, product_id, employee_id, etc. These ‘connecting’ fields are called foreign keys, as they refer to data from foreign tables.
A foreign key definition can be found 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. 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 method 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 the well-known ‘Northwind sample database‘, which is widely used for SQL tutorials. You can download the dump of the database that we used with this link if you would like to reproduce the steps of this tutorial.
Our version of ‘Northwind’ database has 4 MySQL tables:
- employees – which stores information about employees – ID, First name, Last name, Email address, and other fields,
- shippers – which stores information about shippers / delivery companies – ID, company name, and other fields,
- customers – which stores information about customers – ID, company, first and last names, job title, and other fields, and
- orders – a ‘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, we first need to create the 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.
Employees:
Customers:
Shippers:
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 particularly 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 all these numbers stand for. So let’s configure table relations to replace these numbers 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 the “Column settings” button for this column 
or click “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 changing 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 – this is a remote or ‘foreign‘ table from which we want to pull the data. For example, for the ‘employee_id‘ column the remote table will be ‘Employees‘ that we previously configured.
- Display value – a 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 – a 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 (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 the data in that column is changed and shows employee’s last name instead of id. This indicates that the relation is configured.
Let’s repeat the process for the customer_id field, to show the customer’s last name instead of the ID, and for the shipper_id field to show shipping company’s name instead of its ID. By doing that, the table becomes much more readable. The sorting will work naturally, based on the remote values, not on the stored ID integer values.
Foreign keys in advanced filter
It is important to note that foreign key relations do not only allow the data from the remote table to be shown. If a column is configured as a foreign key, the 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, simply 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 for all the other tables. Also, the default ‘text‘ filter will work naturally, filtering by the remote values, and not by the stored id’s.
Edit columns related to other tables
An additional important note 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 those columns is the single-value selectbox, since the options are limited with respect to what the remote table contains.
If you enable editing for a column configured as a foreign key and you choose a single-value selectbox as an editor input type, editing will work naturally, and the IDs will be stored in the column.
From version 2.5 wpDataTables you can use checkbox option where you will be able to choose possible values for the current user or all possible values from a foreign key table for editable tables when the Users can see and edit their own data feature is enabled.
