Views Product Spec #1015
Replies: 9 comments 34 replies
-
OverviewPrompts:
|
Beta Was this translation helpful? Give feedback.
-
|
@ghislaineguerin I can't speak for Dom, but I'd definitely want that when using the product personally. It lets you create the view you eventually want iteratively. As for the prompt, there's an assumption in the first paragraph of the page that users will generally be working with a well normalized database schema. I don't think that's necessarily true. Not only will many users not necessarily understand the value of normalizing their data (and so wouldn't do it), but some may actually have a good reason for a _de_normalized setup (e.g., if the main purpose of their DB is serving OLAP needs). While I didn't notice any big red flags in the more detailed points, we should be careful not to build that assumption into our goals and the designs that flow from them. It's just as valid to use views to process a big denormalized table as it is to get a denormalized view of a bunch of well normalized tables. |
Beta Was this translation helpful? Give feedback.
-
yes, the auxilliary views are in-between steps in the transformation towards the final view. The idea is to comfortably do the transformation from original tables to the final view in more than one step.
Yes, selected verbatim and also used to build/derive new columns. Like, I might have a view that cleans some data (e.g. removes currency symbols from number strings and converts them to the number type) and then a dependent view that adds those number columns together, or something along those lines. To add to what @mathemancer said, I think it would be a normal use-case to use views to normalize some data and then to denormalize it again according to one's needs. That's been a common scenario for me; the "initial normalization" of obtained data is often not optimal. In such cases you end up with a lot of transformation steps, which, depending on the query engine, can end up as a lot of views. |
Beta Was this translation helpful? Give feedback.
-
|
Auxiliary views are addressed in this comment in the "Assumptions and Limitations" discussion.
The main assumption is actually that we will rely on foreign keys (for now) to join data between tables. If the table is already denormalized, no foreign keys are necessary and everything should work fine. We can support joining on natural keys in the future, but I wanted to keep the scope small for the alpha release.
For now, you'd have to do that all in the same view. Perhaps we could have an option to hide columns in the alpha release to make it smoother (but easy to implement). |
Beta Was this translation helpful? Give feedback.
-
They make good sense to me |
Beta Was this translation helpful? Give feedback.
-
Yes, they make sense |
Beta Was this translation helpful? Give feedback.
-
Assumptions and Limitations01. Assumptions and Limitations Prompts:
|
Beta Was this translation helpful? Give feedback.
-
@dmos62 I don't know what you're referring to here, can you elaborate? What is the mechanism you're referring to and how is it brittle?
I'm also not sure what you mean here. |
Beta Was this translation helpful? Give feedback.
-
Yes, they do to me.
Could we mention somewhere/have an alert that says all these decisions are for the alpha and we might re-evaluate them after that? This applies to all views pages. |
Beta Was this translation helpful? Give feedback.
-
Sorry for the ambiguity. I meant to say that I won't talk about alternatives, because we're already invested in this way of handling views. The reflection mechanism I was talking about is the one that helps users find related data that could be joined into a view. By brittle I meant that it fails or is difficult to implement for some use cases, for example when using with views, which is hence unsupported in alpha. By pointing out that it's brittle, I was hinting that it might be difficult to build upon, and so it would be good to keep it in mind during future refactors. |
Beta Was this translation helpful? Give feedback.
-
|
@dmos62 I think this is a good point to discuss alternatives. The spec is up for discussion before it's finalized so that we can consider alternatives. Even if we don't end up taking an alternative approach, it would be good to know what kinds of use cases the initial version does not handle well. One of the goals for this implementation is to build a foundation that we can hopefully easily extend to handle other use cases in the future.
This is also part of the spec that's under discussion. Do you have a suggestion for an algorithm for how to find related data in other views? |
Beta Was this translation helpful? Give feedback.
-
|
@kgodey I may not have thought about this enough, but how about letting the user pick arbitrary columns to join on and provide best-effort non-authoritative suggestions on top of that? Similarly to what we did with |
Beta Was this translation helpful? Give feedback.
-
Feature RequirementsPrompts:
|
Beta Was this translation helpful? Give feedback.
-
Nitpick: not sure it's useful to single out formulas when saying that they'll be translated to SQL expressions. Other things on the list will be translated to SQL expressions too. Also, you used the term SQL functions, which might be a bit too specific: for example |
Beta Was this translation helpful? Give feedback.
-
I think so. |
Beta Was this translation helpful? Give feedback.
-
I think it does. |
Beta Was this translation helpful? Give feedback.
-
The Query BuilderPrompts:
|
Beta Was this translation helpful? Give feedback.
-
Having aggregations at the input step would mean that the table is summarised by default. Since we have discussed allowing the denormalised table input and only then applying a summarisation step, I think aggregation would apply only to the summarised output rather than input. |
Beta Was this translation helpful? Give feedback.
-
Some. See below.
This section makes a lot of assumptions that the main goal will be to aggregate data spread across multiple tables (or at least that's where most of the emphasis of the document lies). I think this is a valid use of querying, but another (very common) is to create "normalized" views (starting as queries) of a denormalized table, both just for viewing and also for consideration when trying to actually normalize a data model. The user needs to be able to look for and assess repetition, not just within a single column, but in tuples composed of multiple columns for that task. For example, if I have a big wide denormalized "roster" table, it would be pretty useful to be able to build a query that shows unique |
Beta Was this translation helpful? Give feedback.
-
The idea with summarization is that you would choose a single "group by" column and the rest of the columns would be aggregated. We choose a default method of aggregation and you can change it as needed on a column-by-column basis (e.g. you can set the aggregation method to "average" for number columns). I'd like to start off with distinct (set) aggregations so that we have fewer things to convey in the UX. Since we'll have the framework set up for different types of aggregations, we can extend it to bag aggregations in the future if users request it.
Could you provide an example schema and SQL query? I'll need to consider what abstraction to use to represent it in the UX. |
Beta Was this translation helpful? Give feedback.
-
ScenarioYou have a
Simulated normalizationWe want to check for errors, and a normalized schema would help. If we don't actually want a normalized schema (for performance reasons; way we want to avoid joins in our large(ish) table), we might still pluck out what the students table would be with the query: SELECT DISTINCT student_id, student_name, student_email
FROM roster
ORDER BY student_id, student_name, student_email;This would result in (using only the subset of data above):
Even without actually creating the table and linking with a foreign key, it's easy to see probable mistakes from this view. In cases where you have a denormalized schema, and are therefore subject to data integrity problems, these sorts of queries are super common tasks. AnalyticsAnother common task given the above table would be to get, say, the average grade for each student: SELECT student_id, avg(grade) FROM roster GROUP BY student_id;This gives (using our subset above again)
I think aggregations of this sort based on groupings of data within a table are super common, even more common than aggregating based on links to other tables. |
Beta Was this translation helpful? Give feedback.
-
|
Thanks for this, it was very helpful. The average grade for each student is already possible based on the "summarization" feature (which is basically group by). We don't have a way of selecting multiple distinct columns in the current spec (simulated normalization), but it should be easy to extend the "summarization" feature to do this (currently I have artificially limited to 1 group by, we could instead allow any number of columns). I'll update the spec accordingly. |
Beta Was this translation helpful? Give feedback.
-
Formulas04. Formulas. There are also a number of sub-pages defining different types of formulas. Prompts:
|
Beta Was this translation helpful? Give feedback.
-
|
I'm trying to imagine how the various elements of this system will come together. The way we've been using It seems that a formula is conceptually identical to that, except that it also does a). It can look at more than one row at a time, but it still doesn't decide the value of more than one column (and doesn't change the number of rows). Maybe this train of thought will be useful. Edit: in section 07 formulas are reffered to as window functions, which is a better way to put it (than I put it). |
Beta Was this translation helpful? Give feedback.
-
|
I was thinking we'll end up extending |
Beta Was this translation helpful? Give feedback.
-
That should work. I'm not yet sure what the various SQL expressions that we'll need to generate will look like, but essentially |
Beta Was this translation helpful? Give feedback.
-
Views05. View Structure Prompts:
|
Beta Was this translation helpful? Give feedback.
-
|
This looks good. It would be good also if users could see the views in which a particular table is being referenced. Not sure if we'd store that information though. |
Beta Was this translation helpful? Give feedback.
-
Would this be when the user is looking at the table? |
Beta Was this translation helpful? Give feedback.
-
Yes, it makes sense |
Beta Was this translation helpful? Give feedback.
-
Extra Information07. Breaking Down DB Queries No specific prompts here, but feel free to leave feedback. |
Beta Was this translation helpful? Give feedback.
-
2022-03-08 Spec UpdatesHere's a GitHub diff of the spec updates I made today in response to feedback: mathesar-foundation/mathesar-wiki@f8df0e0...db399a6
I'm still waiting on an example schema from @mathemancer to figure out if we need to create any new concepts to resolve this concern.
Edited to add:I also added some new variable types and filters in mathesar-foundation/mathesar-wiki@db399a6...18e58e6 @ghislaineguerin This should address the type of formula we talked about where you can apply a filter to a column and get a boolean result. |
Beta Was this translation helpful? Give feedback.
-
2022-03-09 Spec UpdatesI've updated the requirements for the "summarization" feature in mathesar-foundation/mathesar-wiki@016d74e to include the ability to summarize by multiple columns and add @mathemancer's example scenario. This should allow users to find distinct data more easily. @ghislaineguerin This also affects design requirements, so FYI for you. Edited to add:With that, I'm counting the spec as closed. |
Beta Was this translation helpful? Give feedback.
-
I've completed a large revision of the Product Spec for Views and I'd like everyone to read and review it. The data explorer and Views are core features that will define how we do a lot of things in Mathesar and I'd like to catch problems with it early on.
I'll create a topic for each section below. Please feel free to provide any feedback you might have, including half-baked thoughts. I will provide specific prompts in each section in case that helps stimulate thoughts. You are also welcome to start your own topics if they don't fit into a specific page or if you have a lot to say.
Previous discussion
Beta Was this translation helpful? Give feedback.
All reactions