Skip to main content

Foreign Keys

Work in progress

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

  1. They are slightly advanced for a casual user
  2. They result in columns from all tables which then needs projection.
  3. 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.

Base Dataframes

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.

Associations/Linking

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.

Loading...
info

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.

Loading...
Calculated fields

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.

Loading...

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.

Loading...

Lookup an FK

It is possible to lookup other FKs.

Loading...
Multiple lookups

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.

Loading...

Array Field FKs

It is possible to define FKs for array fields.

Loading...

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.

Loading...

Join by FK

Loading...

Group by FK

Loading...

PIVOT by FK

Loading...

Union & FKs

Loading...

Hierarchy by FK

Loading...

Slicers by FK

Loading...

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.

Self-reference 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.

  1. As mentioned in the above section, circular FKs are not supported.
  2. 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.
  3. 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.
  4. A Calculated Field can't be made into an FK.