Viewing Views – Column Data Types and Provenance

[ppii775]

This is the second of a series of new discussions for #466 Support for viewing a database view. This discussion is related to discussions #624 and #838, and the first of this series #911. Feel free to let me know if I missed anything or there's anything incorrect, since I'm new to the group and was not part of the last discussions. The Figma prototype that I'm using might contain outdated styles and components beyond the scope of this discussion.

The Problem

2. The user should be able to see the data type of a column. Column data type
   i. Column data types could be the same data types as tables, or they could be unknown data types.

3. The user should be able to see which column and/or table are referenced in each column, if that data is available. Please note that the column name in the view could be different from the column name of the table, even if the data is the same. There are many possible states for column information:
   i. The view column is the same as a specific column from a specific table/view.
   ii. The view column is derived from a specific column from a specific table/view.
   iii. The view column is derived from multiple columns from the same table/view.
   iv. The view column is derived from multiple columns from different table/view.
   v. The view column is computed and not derived from any columns from any tables/views.
   vi. The view column's provenance is unknown.

Based on Views wiki page and the discussion in #838 Column types for Views, it seems columns in views can have the same data types as tables or different data types (e.g., list), and the provenance of a view column is different from a table foreign key. We've been also having discussions about how to effectively communicate with non-technical users about the differences between tables and views, which made me think whether we should use different visual language for table vs. view data types and provenance indication.

Possible Solutions

1. A universal visual language for both tables and views.

I really like what Ghislaine has currently in the prototype for tables, where users can see the data type as an icon and the foreign key constraints as text underneath the column name. We can replace the "key" symbol with a different symbol to indicate view column provenance, and use a different typeface to indicate computed data. I think this design language can be applied to both tables and views, but the concern would be how to incorporate types that are unique to views and how to tell users the difference between tables and views.

Table column data types and foreign key constraints

View column data types and provenance
A similar visual language as table foreign key constraints.

Tooltips would show up for detailed data types and provenance. This could be especially useful when column names have been customized and the data were computed. Potentially we could also show example queries.

2. A distinct visual language for views.

As mentioned, if we are worried about users confused by table and view concepts, one of the way to highlight the difference is to use two similar yet distinct design systems for table and view respectively.

View column data types and provenance
A new circle visual language indicating unique types for views. Provenance is shown underneath the view column name and users could potentially access the original table by clicking on the table button.

Tooltips would show up for detailed view data types and provenance.

Questions

Another question that I'm having given my limited database knowledge is about the difference between views and tables and how different they are. In #838:

List, Maximum and Count would be View-specific Mathesar data types mapping to a certain type of SQL query.

Do people only use views to compute these data types or could they do a Count aggregation in tables as well? The reason I'm asking is that if views provide many different functions from tables, it is even more important to communicate the differences. However, if users can perform similar functions in both and they could opt to convert their work in a table to a view (even maybe vice versa), we only need to educate users about the fundamental difference ("views are the result of a stored SQL query on the data stored in tables"). On the other hand, if we'd love users to primarily use views, we could potentially adjust the interface layout to prominently locate the view panel and the view working area as well, or maybe even a different set of design language for views. In this way, non-technical users like myself may be able to grasp the difference right away.

[kgodey]

Do people only use views to compute these data types or could they do a Count aggregation in tables as well?

Only Views can have computed data types. Views will be quite different from tables in terms of data entry and types of columns supported.

However, if users can perform similar functions in both and they could opt to convert their work in a table to a view (even maybe vice versa)

Users can save their filters, sorts, etc. on tables as views, but vice versa doesn't work as well.

On the other hand, if we'd love users to primarily use views, we could potentially adjust the interface layout to prominently locate the view panel and the view working area as well, or maybe even a different set of design language for views. In this way, non-technical users like myself may be able to grasp the difference right away.

We would like users to primarily use views. I think any design decisions we can make to guide users in this direction would be great.

---

General notes:

• I'll let @ghislaineguerin weigh in on the preferred option and overall thought process.
• I'm working on defining View data provenance a bit better so you should have more concrete details on that to work with when I finish that work. I think it makes sense to represent data provenance as a separate field from the data type, though.

[ghislaineguerin]

I like the idea of creating a common language to represent columns across Mathesar. One of the main differences between spreadsheet UI and database is that in a spreadsheet, users can interact with cells independently, whereas, in a database, cell behavior is conditioned by the column. For this reason, I think you are right to consider a standard set of elements that we can use for columns. I think based on what you have here, we could start with:

• Column Name
  • Data Provenance
  • Source Table
  • Source Data Type
  • Source Name
• Computed Data
  • Type of computation
  • Source of computed data
• Is filtered
• Is sorted
• Is grouped

Exploring cell-level indicators

Now at the cell level, and because it's conditioned by the column, I'm wondering if we could have some sort of interaction where if we hover over, for example, we can get additional information at the cell level. See the following example, where hovering over certain parts of the code reveals additional information:

Also in google sheets, you can explore formulas at the cell level:

Other example

Additional indicators to consider

In views, it will be important that users know they are looking at a subset of data, so thinking about including the filter indicator would be a good addition to the column header design.

[kgodey]

• Column Name
  • Data Provenance
  • Source Table
  • Source Data Type
  • Source Name
• Computed Data
  • Type of computation
  • Source of computed data

I think that all columns (computed or not) should have the following fields:

• Column Name
• Data Type
• Data Source/Provenance - this could be either a table/view column or a function or query
  • This should cover "Type of computation" as well
• Source Tables/Views
  • This could be either one table/view or multiple, depending on how the column is generated

Non-computed data could have an additional field for Source Column Name