Database views are the result of a stored SQL query on the data stored in tables. Views can involve combining data from multiple tables, filtering, sorting, aggregating (grouping), computing data from other views or tables, and so on.
We would like to steer users towards using views rather than tables to work with their data. Fundamentally, Mathesar is aimed to help users use data to answer questions. Tables that are properly normalized are not the most useful for seeing the most relevant information in a single place.
In the future, we will use views to also build visualizations (e.g. calendar, map, kanban, etc.).
To think about the difference between views and tables, imagine a user who wants to track their movie watches in Mathesar. Questions they might be thinking about are:
In order to track the data necessary to answer these questions, here’s a possible structure for the user’s tables:
|ID||Title||Release Year||Primary Language|
|13||Thelma & Louise||1991||English|
|22||Meet Joe Black||1998||English|
|33||Crouching Tiger, Hidden Dragon||2000||Mandarin|
|41||Crazy Rich Asians||2018||English|
|ID||Movie ID||Actor ID|
As is obvious, none of these tables answer the user’s question by themselves, even when filters, sorts, or aggregations are applied to an individual table. Also, when a user watches a new movie, logging it would involve adding data to four separate tables. The job of views is to simplify that.
Imagine a view with this structure:
|ID||Title||Actors||Last Watched||Times Watched||Language||Release Year|
|13||Thelma & Louise||Brad Pitt, Geena Davis||2021-10-13||2||English||1991|
|22||Meet Joe Black||Brad Pitt||2021-10-01||1||English||1998|
|33||Crouching Tiger, Hidden Dragon||Michelle Yeoh, Zhang Ziyi||2021-09-23||1||Mandarin||2000|
|41||Crazy Rich Asians||Michelle Yeoh||2021-08-12||1||English||2018|
Once they have set up this view, the user could filter and sort to get answers to their questions, or add new movie watches easily by adding all relevant data from one place.