Foreign Keys
The details in this page are work in progress and not officially supported yet.
The input data may or may not be available in a denormalized format. When it is not
yet denormalized, it is possible to create a logical view using joins (SQL.join()).
However, joins have the following disadvantages
- They are slightly advanced for a casual user
- They result in columns from all tables which then needs projection.
- They need aliasing tables to refer to the column names.
Instead, imagine the ability to specify that a field is a foreign key to another table and that automatically starts displaying the data from the other table.
Foreign Keys can be added only to base DataFrames.
1-to-1, 1-to-Many
Both 1-to-1 and 1-to-many relationships are supported.
Some ORMs such as sequelize refer to these as Associations. Other low-code SAAS platforms such as Airtable refer to them as linked records.
Single FK
When creating an FK, it is also optionally possible to use lookup() API to specify what
value to show in the UI For the FK. In the following example, the product_name of the products
table is going to be displayed instead of the numeric product_id values.
Operations such as sorting and filtering happen using the lookup value of the fk field if specified without having to worry about how to create an advanced SQL query to achieve the same.
Compound FK
The createFK API can take multiple fields to create a compound FK. When the field names in both tables are not matching,
then instead of a single field name, it is possible to provide field names of both tables as an array pair
such as ["product_id","id"] where the FK column name is product_id and the corresponding id in
the FK table is just id.
Self-Referencing FK
It is possible to define FK relationships from a DataFrame to itself. In the following example, the manager id
is setup as a foreign-key to the same table, employee.
It is possible to create a meaningful calculated field such as full name and use that as the display field for the calculated field.
Many-to-Many
These relationships can be thought of us two separate 1-to-Many relationships.
Lookups
Once an FK is setup, apart from the optioanl lookup for the FK, it is possible to lookup other
fields from the FK table using the lookupField() API.
Lookup an FK
It is possible to lookup other FKs.
Lookups are inexpensive once the associated FK is resolved. Hence, it is possible to have multiple fields from the FK table to be available via multiple lookups without performance concerns as the FK is resolved only once.
Also FK Ids
Sometimes the FK Ids are also used by end users and they use these IDs to lookup the data.
Hence, it may be required to work with both the FK lookup as well as the original ID. SQL Frames makes
this possible with the df.fkIdField() API that provides access to the internal id of the FK.
Array Field FKs
It is possible to define FKs for array fields.
Unresolved FK Ids
Unresolved FK Ids are displayed as (FK: <id>) in the UI. In the above example the value seller
is not resolved.
Foreign Keys in DataFrames
One of the key advantages of providing FK relationships in SQL Frames is that this relationship is propagated to all the different DataFrames within the data transformation pipeline.
Filter by FK
Open the filter builder and notice how the FK field will have a list of value to choose from.
Join by FK
Group by FK
PIVOT by FK
Union & FKs
Hierarchy by FK
Slicers by FK
Eager vs Ondemand
For 1-to-1 relationships only one side of the relationship can be computed eagerly. For 1-to-Many relationships, only the side that resolves to a single record is computed eagerly. The other side of the relationsips are available ondemand using the contextual menu action, Related data, where all the available relationships are listed (see the Products tab of the first example above to try this out).
Circular FK References
A special case of eager vs ondemand computation is the circular FK references.
SQL Frames doesn't support circular foreign key references at this time. While this is possible in databases, they are usually hard to deal with especially during data migration. In general it is good to avoid circular FK references but most real life applications require them.
SQL Frames requires all DataFrames to be computed in a specific order and the DataFrame is computed in its entirety and not row by row. This poses a challenge to deal with circular FKs.
One exception to the above is the self-reference FKs which are supported by SQL Frames.
Limitations
Current implementation of FKs have the following limitations.
- As mentioned in the above section, circular FKs are not supported.
- Downloaded data only contains the id and not the lookup value of the FK. This may be addressed in the future by providing an option while downloading to indicate the choice of id or the lookup.
- The SQL generation logic is not FK aware at all. This may be addressed in the future by creating more complex SQLs that could resolve the FKs on the server-side.
- A Calculated Field can't be made into an FK.