Relational Data Modeling
A nifty feature in Zoho Reports is the way it supports relational data modeling. You can create or import a database consisting of multiple tables which have relationships defined between them as in a relational database. In relational databases world, tables are related using Foreign Key relationships. In Zoho Reports, this is done using the Lookup Column feature where a column in one table points to a column in another table.
Relating tables with Lookup columns
Let us try to explain this by creating a sample Employee database. Say the Employee DB consists of two tables Department and Employee. The Department table has two columns - Department Name and Department ID.
The Employee table has the Employee Names and their respective Employee IDs. The Department to which each Employee belongs to is referred by the third column Department ID.
We define the Department ID in the Department table as the column that is being looked up by the Department ID column of the Employee table. Click on the Edit Design button in the Employee table to define this relationship.
Double-clicking on the cell in the Lookup Column field, lists the different
tables & their columns in a drop-down box. In our example, we choose the Department
table's Department ID.
Also, when a new row is added in a table that looks up a column from another table, all the values get listed automatically. Let's say a new Employee is to be added. The Department IDs get listed in the Department column and you can choose the appropriate department the new Employee belongs to.
Joining Tables with Query Table
Database
tables
which are connected to each other through such relationships
allow you to fetch data combining the related tables. In Zoho Reports you
can combine the data in such related tables by creating a Query
Table (How to Create a Query Table Video) which contains a SQL Select Join query. For example, a query combining the Employee and Department tables can be made as shown below.
The example query above joins the Employee & Department tables,
getting the department name mapped to each employee. Over the query
table that you have created by joining the necessary tables, Zoho
Reports allows you to create any type of reports for analysis and
visualization.
Enforce Data Integrity using Cascade On Delete
To ensure that the integrity of the data is maintained when data rows
get deleted from related tables, Zoho Reports supports
Cascade-on-Delete feature. That is, when rows are deleted in a parent
table, then all the corresponding rows in the child table will be
deleted automatically. (Parent and Child tables are related in Zoho Reports using the Lookup column feature discussed above.)
In the above example, if any department gets
deleted in the
Department table (parent table), then all the employees will get deleted automatically from the
Employee table (child table).
For example, let's say the Accounts department (row) is deleted.This will result in the corresponding Employee rows getting deleted.
