Working with Columns

[ghislaineguerin]

The specs related to issue #324 are ready for review. Please take a look at the attached document and follow the review guidelines.

Document Link

https://wiki.mathesar.org/en/design/specs/working-with-columns

@mathemancer @kgodey @pavish, your feedback is required.
The deadline for feedback is set to a week from now, the first review round will be completed by Friday 23rd. Please plan your review so that at least one round of changes can be made and reviewed if needed.

[kgodey]

@ghislaineguerin This looks great. Thoughts and questions:

• I think the "Table" menu name is a little confusing. It's hard to know what it does from the name since it's so general. Maybe something like "Table Actions"?
• I like the flow for adding a new column, it's simple and the user doesn't have to make decisions unless they want to.
• I like the column menu. We might want to add icons next to the menu items at some point to make distinguishing between them easier.
• I can't find the warning for deleting a column in the prototype – am I missing something, or is it not specified because we already have other warning popups in previous specs?
• Regarding changing column types, we are providing a list of compatible types for each column (once Add column type targets to API #366 is merged) so we will only show the list of compatible types when the user wants to change their column type, not the full list.
• Feedback on the "allow null" functionality.
  • Right now, the design shows that "allow null" gets toggled and we show them the null rows in red. However, the behavior will actually be that "allow null" does not get saved at all. It's not like "allow null" gets saved and some records enter into an unsaved state, which is what the current design shows.
  • We need to indicate to the user that their choice to not allow nulls was not saved and they need to remove the NULL values and try updating the settings for the column again.
• The same feedback applies to "allow duplicate".
• I do really like the "Help me fix this problem" flow, I'd keep that. I like that we are anticipating the user's needs.
• If users set some columns not to allow duplicates, then when the table constraints menu is opened, we need to show that those columns have unique constraints set.
• When the user is replacing values (null or duplicate), we need to show them some confirmation of how many rows and values will be changed. If we don't do that, the user might lose data, especially in large tables where they can't visually see everything.
• I think the multi-column selection flow in the replacing values and editing constraints modals needs to be defined better. @pavish might have an opinion on this.

[ghislaineguerin]

• Good point regarding the 'Table Menu' I think adding actions as a label makes sense. I'm trying to be very intentional about separating record actions vs table actions so that as we have more advanced functionality we can clearly locate the controls.
• Good idea re: icons on column menu, and menus in general.
• About the warning, I haven't included it, I was thinking to include this when working with relationship data types. I will add it anyway for completeness.
• Thanks for the clarification on allowing null functionality, I will make the changes.
• Agree on better defining multi-column select, I wanted to make sure we need this component before investing more time in it. Thanks

[mathemancer]

I agree with @kgodey that the adding column flow is nice, and I really like that they don't have to choose anything at first. I think the choice of a TEXT type as the default column type is a good one.

A few feedback items:

• The drop-down arrow (sorry, I'm not sure of the terminology) to get to the column settings menu and the one to choose the column type are very close together, but have fundamentally different meanings. I.e., one is kind of a replacement for typing in a new type name, and the other opens a menu unrelated to the column name next to it. I think this will be a bit confusing.
• I think having two very different ways to set up uniqueness constraints is confusing, i.e., one from the column settings menu and the other from the table settings menu item. This is especially confusing since a uniqueness constraint on two separate columns, separately and two separate columns as a pair will be indistinguishable from the column settings menus of the respective columns in the table view, but will have completely different behavior in practice. I assume the difference would be apparent from the view reachable via the table menu.
• I'm not sure about the 'remove duplicate values' table menu entry. Will this be used only for removing completely identical rows? In the write-up, there's a sentence about choosing columns, and removing duplicate entries that clash for those columns. I think this will need a more involved user flow. If the selected columns define a set of duplicate tuples, but the non-selected columns have entries that aren't duplicates, we can't automatically remove anything. The user would need to choose which of the rows contain the values they want in the non-selected columns.
• Another difficult issue arises when trying to replace null values. It's possible (related to our discussion on the matrix channel) to have more than one row with a null value in a column that doesn't allow duplicate entries. This is because multiple null values don't violate UNIQUE constraints. But then, if we want to replace all null values with, e.g., empty strings, we would violate the uniqueness constraint, and we'd have to disallow the action. I don't think this will be possible for a novice user to solve without a more involved flow when replacing nulls.

[ghislaineguerin]

• I've been having some doubts about having a separate menu for this, initially I wanted to keep data type labels visible and text-based rather than icons for extensibility purposes, but thinking that doesn't necessarily require a menu separation.
• I agree with consolidating constraint settings.
• I think we do need to discuss remove duplicates a bit more, I think making it work for completely identical columns is too restrictive, we might need a step where they decide which columns to discard.
• Good point. But is replacing values still a valid way to eliminate nulls in columns that have no uniqueness constraints?

[mathemancer]

• Good point. But is replacing values still a valid way to eliminate nulls in columns that have no uniqueness constraints?

Yes. It should be possible to replace all nulls (given no uniqueness constraints) in a column with a "default" value.

[ghislaineguerin]

@kgodey I have updated the prototype based on what we've discussed.
@mathemancer @pavish for context:

• We'll use filters to help users resolve problems with nulls or duplicates
• We have removed the types cell and now use icons to represent the types with additional details available within the menus

[kgodey]

@pavish @mathemancer please re-review the spec.

[pavish]

@ghislaineguerin Sorry about the late review. A single menu with all the essential options for the column looks good to me!

I am still not convinced about the column related options in 'Table Actions' menu. It is confusing for me as a user to understand what they do, right away. I think it's best to just have these options individually on each column.

The chances of users trying to remove duplicate values/replace values/set constraints across several columns in a single action is very rare. It is much more intuitive and easier to do them column by column (similar to how we can only change the type of a column/delete column, one at a time).

[kgodey]

@pavish Could you clarify which column related options you're referring to?

As far as setting constraints, a place to set multi-column constraints was part of the design requirement. Please see #324. These can't be set from an individual column because they involve multiple columns.

[pavish]

@kgodey I agree that we need options to set constraints on single/multiple columns. It makes sense to place on the table options menu.

I am not sure of the functionality of the following:

• Remove duplicate values
  • Re-reading the spec, this seems to remove the entire row. I assumed, it removed duplicate values on the chosen columns i.e made them null. I think it would be better to have only the option to find duplicates (using filters) and then let the user manually delete it, rather than a direct option to remove duplicates. Similar to how we do it from the column menu.
• Replace values
  • I think this should be done one column at a time.

[kgodey]

@pavish Thanks, makes sense.

I think the "remove duplicate values" and "replace values" are leftovers from the previous design, we won't be using them. @ghislaineguerin and I discussed this on Friday and decided there was too much complexity to address in the MVP.

The planned flow now is to use filters. You can see this flow in the prototype if you try to switch "Allow empty" and "Allow duplicates" in the column settings menu. We'll just filter the rows and the user can then correct them on their own.

[kgodey]

@ghislaineguerin when you're back, please update the spec (both text and prototype) to reflect the new filtering flow.

[ghislaineguerin]

@kgodey @pavish @mathemancer
I have updated the specs based on the feedback and collated the review notes, posted below for reference:

Labeling of Menus

Menus that list options for different objects, such as tables, columns, and records, should be easily discovered by users. We have simplified the column-related options to a single menu that users can access in context from the column header. As for table-related actions, we have updated the label to 'Table Actions' for increased clarity. Once all controls and options for the MVP are in place, we will revisit all labels to ensure we are applying the same consistent patterns across the entire app.

NULL, Empty, Blank Fields

To simplify the way we handle the filtering of columns based on the content of a field, we have nested the NULL condition under the 'Empty' definition, which also includes empty strings.

Help me fix this problem

Whenever possible, we want to guide users towards resolving problems that hinder their goals within Mathesar. We are introducing a pattern for specific warnings to present suggested actions based on the context.

Column vs. Table Constraints

There were some concerns over having both table-level and column-level constraints. We are introducing a help/more info icon next to some options so that users can learn more about them. Once we have the design for the complete set of functionality for the MVP, we can look at the different ways we can educate users within Mathesar.

[ghislaineguerin]

@kgodey I've fixed the document and also clarified what I meant with "Help me fix this problem".
As for table-level constraints, Is the idea that first a constraint is selected, and after that, a group of columns must be assigned as targets? I will modify the design to fit this scenario

[kgodey]

Yes, you set the type of constraint and the columns that the constraint applies to.

The constraint can also have a name, although for the sake of keeping things simple, we can omit showing the name for this version.

[ghislaineguerin]

@kgodey I have updated the flow for adding constraints. I did include the name as I think it is needed for the list and edit of specific constraints.

[kgodey]

@ghislaineguerin It looks good, but I'm still not convinced about having the name. The name is optional and is auto-generated if we don't provide one, and I think it might confuse non-technical users.

I assume editing will involve the same form, and that's why it's not defined?

[ghislaineguerin]

@kgodey I have removed the name field. I have read more about constraints naming conventions and yes, I can see how we might just generate the names and not have the user do it.

[ghislaineguerin]

@kgodey @pavish @mathemancer
Updated review notes:

Labeling of Menus

Menus that list options for different objects, such as tables, columns, and records, should be easily discovered by users. We have simplified the column-related options to a single menu that users can access in context from the column header. As for table-related actions, we have updated the label to 'Table Actions' for increased clarity. Once all controls and options for the MVP are in place, we will revisit all labels to ensure we are applying the same consistent patterns across the entire app.

NULL, Empty, Blank Fields

To simplify the way we handle the filtering of columns based on the content of a field, we have nested the NULL condition under the 'Empty' definition, which also includes empty strings.

Providing options to solve problems when presenting warnings (Help me fix this problem)

Whenever possible, we want to guide users towards resolving problems that hinder their goals within Mathesar. We are introducing a pattern for specific warnings to present suggested actions based on the context. For example, in this case, we allow users to navigate to pre-filtered views to solve issues that prevent them from changing column settings.

Column vs. Table Constraints

There were some concerns over having both table-level and column-level constraints. We are introducing a help/more info icon next to some options so that users can learn more about them. Once we have the design for the complete set of functionality for the MVP, we can look at the different ways we can educate users within Mathesar.

Filters

Filters, as presented in this document, are no longer consistent with the latest design updates. The panel component has proven to be incompatible with many of our use cases, and we have opted for a dropdown component instead.

[seancolsen]

I have a suggested change to the "Working with columns" Figma prototype, and I hope this is an appropriate place to raise this issue.

This part of the prototype seems to be specifying one modal on top of another.

I'd prefer that we assume the app will always have a maximum of one modal open. Implementing multiple open modals, while possible, will be more work due to the additional edge cases involved. Plus I think it's a sufficiently uncommon pattern so risk becoming confusing usability-wise. I'd suggest we either:

• design the "Add Constraint" form to appear inline, at the top of the "Table Constraints" modal.

  or

• take the "Table Constraints" UI out of a modal somehow. It could potentially live in a new tab where the user would be able to see and edit all of the meta data for the table. But that would be a substantial divergence from the UI patterns we've already established, so I'm guessing we don't want to go down that road.

Thoughts @ghislaineguerin @pavish @kgodey ?

[ghislaineguerin]

@seancolsen I agree with limiting open modals to a maximum of one. I think the best solution is to have the form appear inline and apply the same principle for all modal contents.

[pavish]

I remember discussing this earlier with @ghislaineguerin, and the opening of a modal over the other was due to a limitation in figma. I agree with having only one modal open at a time. In this case, the content of the same modal would change.

[seancolsen]

Another question

I see there was some previous discussion here about "table constraints" vs "column constraints", but it's hard to me to quickly tell whether there was a decision which pertains to my current task.

When the user opens the "Table Constraints" modal, should I:

• (A) display all the constraints for the whole table

  or

• (B) display only the constraints which pertain to multiple columns (because column-level constraints will be available through the column's interface)

Option (A) seems a bit more intuitive to me (and more consistent with other GUI DB tools I've used), but the Figma prototype shows a modal that seems to suggest (B).

@ghislaineguerin @pavish @kgodey

[kgodey]

It should be (A)

[seancolsen]

Another question

Within the Table Constraints modal... When the user clicks the "Delete" button, what's the expected behavior? Do we want to confirm before delete? I'm not sure.

It's not that hard to re-create the constraint. We could even get fancy and offer an undo.

If we confirm the delete, then we ideally need a way to do that without throwing up another modal. I would lean towards doing this confirmation within the row that displays the constraint. So the row would transform from constraint info to a delete confirmation. This would be a great place for some subtle animation in my opinion. If I'm to head this direction it'd be helpful to have a mockup showing the modal with two constraints where one is in the state of confirming its deletion. But I'm happy to proceed without a mockup and make some of the design choices on my own if needed.

@ghislaineguerin @pavish @kgodey

[kgodey]

@seancolsen I think your idea sounds good. I'd recommend proceeding without a mockup and making design choices on your own (since @ghislaineguerin is neck deep in Views designs at the moment), we can open a new issue for adjusting styling as needed.

[pavish]

@seancolsen As an added note, please feel free to improvise minor design changes during implementation. We may have several of those since they usually come up only during/before we begin implementation. We have the stability milestones where @ghislaineguerin would look at the staging server and sync the UI and UX, and take decisions on the finalizing the design.