A typical database defines many data tables, and many fields (columns) within each table. Hiding and showing fields, within and across tables, is a frequent operation in Ultorg.

The Field Selector

To pick fields to display in a perspective, open the context menu on any already-visible field in the relevant subquery, and click Fields & Joins. This opens the field selector popup, where adjacent fields can be hidden and shown:

You can quickly open the field selector with Ctrl+K (⌘K on Mac), and use the Up/Down arrow keys and Space to select and check relevant fields. Holding down Shift while pressing arrow keys lets you select multiple fields at once (see Keyboard Shortcuts).

You can type the first few characters of a field name to move the selection to said field.

Checkbox Behavior

A checkbox () controls the visibility of each field within the current perspective. Checking and unchecking Select All reverts to a default set of visible fields.

It is possible to expand a subquery in the field selector without immediately making it visible. In this case you may see child fields with a dimly checked checkbox (). These are fields that would become visible if their parent subquery is made visible.

Icons in the Field Selector

In the field selector, the following icons are used for different kinds of fields:

	A subquery associated with a data table.
	A subquery not associated with a data table (a Custom Group).
	A suggested join that can be added as a new subquery.
	A primitive field pulling data from a column in the containing subquery's data table.
	A primitive field calculating values with a formula.
	A primitive field displayed together with a joined subquery in a compact join.

Less commonly:

	A new available table column, which only recently appeared in the data source.
	A compact join on a calculated field.

The Fields Sidebar

A field selector is also shown in the permanent Fields sidebar. It highlights the field where the cell cursor is currently located.

Showing Additional Properties

You can display additional information about each field by clicking the property selection button () in the corner. For example, you can show the data type of each table column:

The sidebar version of the field selector is useful for quick glances at the field list. For changing field visibilities, the popup version in the Fields & Joins action is usually more effective, as it keeps your visual focus (eye gaze and mouse pointer) closer to the actual data.

Hide and Hide Other

The Hide and Hide Other actions in the context menu can be used to quickly hide selected or unselected fields, without having to go through the field selector.

The Hide actions are often used with multiple selection, as illustrated above.

Semantics of Hidden Fields

In most cases, hiding or showing a field (primitive or subquery) has no effect on the semantics of a perspective query, beyond the change in visibility itself. The exceptions are:

• In a Custom Group, grouping is done based on visible primitive fields only.
• For data tables without a primary key or physical row ID, associated subqueries will retrieve unique rows only, based on visible primitive fields.
• For subqueries with no visible primitive fields, and at most a single visible subquery, retrieved rows will be collapsed into one.
  

  (In the example above, individual rows in the Sections and Instructor Assignments tables are collapsed because no primitive fields are visible in the associated subqueries. The Instructors subquery can still retrieve multiple rows, as it has visible primitive fields.)

• For the rules above, primitive fields that are sorted on are treated as if visible.

For subqueries with an associated data table and primary key, and at least one visible primitive field, each row displayed in Ultorg will correspond to a single row in the underlying data table. In the other cases detailed above, an Ultorg subquery behaves like a SELECT DISTINCT in SQL.

Semantic effects aside, hiding a subquery may speed up certain queries.

Joins between Tables

In database queries, a join is an operation that combines data from two tables, based on common identifiers such as email addresses, room numbers, or customer IDs. Ultorg perspectives are structured around joins and the relationships they expose between tables.

Many databases provide an explicit list of useful joins, known as foreign key relationships. When you invoke Fields & Joins to open the field selector, Ultorg will show not only the fields of the current table (), but also suggested joins () based on known foreign key relationships. You can click the corresponding checkbox to make the join visible in your perspective:

The example above is from the demo database, which you can access via Add Data Source→Connect to Demo Database. In this perspective, we show a list of university courses and, for each course, its reading list.

Joins and One-to-Many Relationships

By making a few more fields visible, we can see how joins work in Ultorg:

Here, the Course ID field in the Readings table is matched against the ID field in the Courses table. This exposes a one-to-many relationship: each course can have many readings. Most foreign key relationships are also one-to-many relationships.

The presence of a join condition is indicated by the join icon () after the field name Course ID in the table header. The full join condition is shown in the field selector. If you don't need to see the ID fields, you can hide them; this will not affect the semantics of the database query.

Suggested Joins

The previous example showed a join that was automatically suggested based on a foreign key relationship. You can also specify joins manually, using the Custom Join action. After a join has been added manually once, it will be included in the field selector's subsequent suggestions.

Suggested joins appear in the field selector from either direction, i.e. with either table in the join as either the parent or the child subquery.

Primary Key Columns

A data table may designate a one or more columns as its primary key. Each row is uniquely identified by the value in the primary key column(s). For example, a “Customers” table might have a primary key column named “ID” that holds customer IDs.

In the Fields sidebar, primary key columns are indicated with the key icon ().

Certain Ultorg features require, or benefit from, the presence of a primary key. For example:

• A subquery that joins on its primary key will always yield at most one row per parent row. This makes references to the subquery from a formula permissible in more cases, without the use of an aggregate function.
• The Compact Join feature works specifically for joins against a single-column primary key.
• Data editing is supported only on data tables that define a primary key.
• When a subquery has no explicit sorting, Ultorg may sort on its table's primary key.
• For tables with a primary key, Ultorg may generate more efficient SQL queries.

A foreign key relationship, as mentioned earlier, has foreign key column(s) in one table that reference another table's primary key.

If you need to change the set of primary key columns for a data table, you must do so at the data source level, e.g. with the ALTER TABLE command in SQL. For Excel and Google Sheets connections, the first column of each table is used as a primary key if its values are unique.

For tables without a primary key, a physical row ID may sometimes be available instead, such as ctid on PostgreSQL. In these cases, Ultorg will add the physical row ID to the field list, and use it as a primary key for grouping and row identification purposes.

Join Visualization in Ultorg

The joins in an Ultorg perspective work differently than the traditional “flat” joins encountered in SQL and other query tools. Rather than duplicating rows from one table for each matching row in the other, Ultorg displays parent rows and a set of matching rows under each.

Expanding the example, we can add more joins under the Courses table, by including additional suggested joins () from the field selector. In addition to the reading list for each course, we display Sections and, under the latter, Meetings:

Each Course is now displayed with both a Reading list and a Section list, and each Section list in turn has a list of Meetings. By default, a Course is displayed even if its Reading list or Section list is empty, as with the course “Neurobiology” above. The latter behavior can be adjusted with the Hide Parent If Empty () setting.

This is a useful visualization that cannot be produced directly with SQL, since the output of the query has a nested rather than flat tabular data structure.

Nevertheless, Ultorg's nested data model is ideal for visualizing SQL queries, since database queries are themselves composed of nested structures such as joins, aggregates, and subqueries. See our SIGMOD paper[2] for details.

Data Tables, Joins, and Subqueries

As previously explained, the bold field names in the table header (Courses, Readings, etc.) indicate the hierarchy of subqueries in the perspective. Each subquery can pull data from a single data table, and each subquery can have join conditions against its parent subquery.

In the Folders sidebar, a blue arrow () indicates which table the data currently under the cell cursor is coming from:

Show Hierarchy

By default, the field selector popup shows both primitive fields and subqueries. By clicking the Show Hierarchy () button, you can show only the hierarchy of subqueries and suggested joins, starting from the root and automatically expanded as space permits:

This mode serves as a quick way to visualize available foreign key relationships, and to make related tables visible. It is a compact, tree-structured version of a schema diagram.

“Via” Indications

In some cases, there may be more than one way to join a table against another. In this case a disambiguating “via” notation may be shown in grey after the name of each subquery:

You can rename the subqueries if desired. When names are distinct, the grey text disappears.

Many-to-Many Relationships

In the database literature, a distinction is often made between one-to-many and many-to-many relationships. Two data tables have a many-to-many relationship if rows in either table can associate with any number of rows from the other table.

For example, a database of Orders and Products may associate any number of products with each order, and associate any number of orders with each product:

In a database schema, a many-to-many relationship is created using two one-to-many relationships and an extra table in the middle, known as the “junction table” or “join table”.

In the example above, the junction table is called “Line Items”. It holds foreign keys to Orders and Products, as well as data related to the relationship itself, such as the quantity ordered.

In Ultorg, a many-to-many relationship will typically be viewed from one of its two ends. Depending on your preferred perspective, you could see, for example:

• A list of orders, and for each order, a list of products, as shown above, or...
• A list of products, and for each product, a list of orders.

The Move to Root action, discussed next, lets you switch between these kinds of grouping hierarchies. The Custom Group action handles other kinds of groupings.

Move to Root

The Move to Root (or Move towards Root) action is available when you open the context menu on a subquery's bold table heading. This action lets you reorganize the order of joins in your perspective, as if you had started building it from a different table.

For usability reasons, the Hide Parent If Empty setting () becomes active by default from the former root subquery. You can clear it with the Clear Filter action.

Terminology Recap

By now, we have introduced a fair amount of terminology. Here is a summary:

• Data table: A table of raw data in a database or other data source.
• Join: A database operation that combines data from two data tables, based on matching identifiers in specified columns.
• One-to-many relationship: A possible result of joining two data tables, when each row in one table can match multiple rows in the other.
• Foreign key relationship: A pre-defined suggestion of how to join two data tables, defined by the data source. Usually a one-to-many relationship.
• Perspective (Ultorg concept): The spreadsheet-like data grid that you spend most of your time interacting with in Ultorg. Represents a database query.
• Subquery (Ultorg concept): One level in the hierarchical structure of a perspective. Shown with a bold heading in the table header, and a subquery icon () in the field selector.
  • Each subquery can pull data from one data table.
  • Each subquery represents a join against its parent subquery.
  • Subqueries contain fields which can either be primitive fields or other subqueries.