Linking Databases Together

The 'Data Source' field type connects database tables by defining relationships between them. This field type enables users to build databases with the following features:

• Referential Integrity:

  The 'Data Source' field ensures that the data in one table (referred to as the child table) corresponds to the data in another table (referred to as the parent table). They define a relationship between the two tables based on a shared column or columns. The 'Data Source' field in the child table references the primary key in the parent table, establishing a link between the two.

• Data Consistency:

  The 'Data Source' field helps maintain data consistency by preventing orphaned or invalid data. With referential integrity enforced by the 'Data Source' field, you cannot insert a value into the child table's 'Data Source' column that does not exist as a primary key in the parent table. This constraint ensures that data in the child table remains linked to valid and existing data in the parent table.

• Joins and Data Retrieval:

  The 'Data Source' field enables efficient data retrieval and querying through joins. Joins allow you to combine data from multiple related tables based on the 'Data Source' and primary key relationships. By joining tables, you can retrieve related data and perform complex queries involving data from multiple tables.

• Cascading Actions:

  The 'Data Source' field can also define cascading actions that automatically propagate changes across related tables. For example, you can specify actions such as cascading deletes or updates, where modifying or deleting a row in the parent table automatically affects the corresponding rows in the child table.

Creating a Data Source field

Before we proceed with creating a 'Data Source' field, note that there are two 'Data Source' field types:

• Data Source: Saves the value as a String and only saves one value per field.
• Data Source (Multiple): Saves values as an Array and saves multiple values per field. Used to set relations.

To connect database tables using the 'Data Source' field, follow the steps outlined below:

1. Select Databases on the main menu.
2. Go to All Databases.
3. Find the database you want to add a 'Data Source' field to and press its Configuration row action link.
4. Navigate to the 'Fields' tab.
5. Click on ADD FIELD. Fill out the information required on the form. Field requirements and descriptions are displayed in a table after this step-by-step guide.
6. Press SAVE FIELD to save your 'Data Source' field.
7. Press SAVE to finalise your changes.

The field requirements and descriptions for adding a 'Data Source' field are displayed in the following table:

The field requirements and descriptions for adding a 'Data Source (Multiple)' field are displayed in the following table:

Conceptual example

Let's use two tables called and as an example. The table of orders () has a user column with a foreign key (data source) attribute that links it to the column in . Each row in the table of orders can then be associated with a specific user from the table of customers.

No orders can enter the system without a valid user being associated with them. The referential integrity and data consistency afforded by linking databases enable multiple users to place orders, check their status, and retrieve their order histories simultaneously. While this happens, other users can also update or cancel their orders as desired.

table_customer

table_orders

In this example, is the parent table while is the child table. 'CustomerID' in is the data source field used to link the two tables together. It references the 'CustomerID' field in .