Linking Databases Together

Insites uses 'Data Source' fields to implement foreign key constraints. These fields create smart links between your databases, keeping your data organized and accurate. The 'Data Source' fields allow you to build databases with the following features:

• Accuracy (Referential integrity):

  The foreign key 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 two tables based on a shared column or set of columns. For example, the system prevents assigning an order to a customer who does not exist.

• Data consistency:

  The foreign key helps maintain data consistency by preventing orphaned or invalid data. With referential integrity enforced by the foreign key, you cannot insert a value into the child table's foreign key column that does not exist as a primary key in the parent table. For example, if a customer’s ID changes, the system ensures their orders remain correctly connected.

• Easy reporting (Joins):

  The relationship established by the foreign key enables efficient data retrieval through joins. Joins combine data from multiple related tables based on their foreign key and primary key relationships. By joining tables, you can retrieve related data and perform complex queries across multiple tables.

• Automted updates (Cascading actions):

  Foreign key constraints can also define cascading actions. These actions 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.

Conceptual example

To understand how these links work, let's look at a simple store setup with two tables:

• The parent table : Holds the master list of people.
• The child table : Holds the list of purchases.

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 orders table can then be associated with a specific user from the customers table.

No orders can enter the system without a valid user being associated with them. The referential integrity and data consistency provided by database linking 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 .

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 link databases using foreign key constraints (referred to as 'Data Source' fields in this system):

1. Select Data from the main menu.
2. Select Databases.
3. Locate the database where you want to add a foreign key constraint ('Data Source' field) and select its Configuration row action link.
4. Navigate to the 'Fields' tab.
5. Select ADD FIELD.

6. Fill out the information required on the form. You may review the input requirements by visiting the following links:

   • Data Source
   • Data Source (Multiple)
7. Select SAVE FIELD to save your foreign key constraint.
8. Select SAVE to finalize your changes.