Are we planning to eventually support reordering table columns?

[seancolsen]

Observations:

• Support for creating a view based on an existing table or view #782 says:

  The user may try to reorder columns in a table. At this point, they should be prompted to create a view of the table...

• The columns API returns an index for each column, not an id, implying that its position is immutable.

• Reordering Postgres columns is hard.

• AirTable and NocoDB both support reordering table columns.

I see that in the short term users won't be able to reorder table columns. Is that just a consequence of our task prioritization? Or is part of our long-term product vision?

@kgodey @mathemancer @pavish @ghislaineguerin @silentninja

[seancolsen]

Personally, I'd like to see us aim to eventually allow users to reorder table columns without creating a view. For the implementation, I would lean towards storing a weight field (or similar) for each column in some other table. So at the DB table level the user would not be able to re-order the columns. But at the Mathesar table level, the user would be able to reorder the columns. The Mathesar table would look a bit different from the DB in this regard. But I think that'd still be a better overall user experience than requiring the user to create a view just to re-order columns.

[pavish]

@seancolsen

implying that its position is immutable

This does not hold true. Column index changes whenever a column is added/removed (It also changes for all columns after the added/removed column). I remember having a detailed discussion around this with @kgodey and @mathemancer, inorder to have an id for column, which is a combination of name and index. I'm not able to find that discussion (maybe it was in an issue).

Edit:
I found the discussion. It's here: #392

To answer the question,

Yes, we should eventually be able to reorder columns directly on the tables/views in my point of view. We can store the column order related information in our database, similar to the approach you've mentioned in #803 (comment). I remember talking about this and I assumed we have just de-prioritized this for now, and everyone is in agreement.

Edit:
Reading #782, I'm not so sure if everyone is in agreement, I think we need to decide on this.

I think ordering, resizing, hiding/showing columns etc., should be stored at the user level, for each mathesar user, and we shouldn't require creating views for them.

[mathemancer]

implying that its position is immutable

This does not hold true. Column index changes whenever a column is added/removed (It also changes for all columns after the added/removed column). I remember having a detailed discussion around this with @kgodey and @mathemancer, inorder to have an id for column, which is a combination of name and index. I'm not able to find that discussion (maybe it was in an issue).

This was a mistake on my part. In my defense, the docs are very fuzzy about the relevant attribute and system catalog table. However, the actual situation is that:
Each column in a DB can be identified by a (table_oid, attnum) pair. The table_oid is the unique identifier of the table containing a column. The attnum is a sequential ID number assigned to each column as they are created, from left to right (not counting the PostgreSQL system columns). The attnum does not change when columns are dropped or added. We plan to move from "index" to "id" for columns for this reason.

W.r.t. immutable positions of columns: The order is immutable at the DB level. The only way to simulate reordering columns in a table at the DB layer would be to create new columns in sequence of the desired order and drop the old versions of columns. I don't recommend this for tables, since views are much more appropriate for that.

[kgodey]

Generally, we're trying to steer users towards the purpose of tables as only for storage / source of truth of the data and views being the main way they see data presented and work with it day-to-day. Reordering columns is a presentational thing, and as such, I think it belongs in views. I think it might help the user get the concept better if we guide them towards making a view if they want to save presentational aspects of a table. Since views and tables look so similar, I'm worried that the user won't understand the difference between them very easily.

Note that this is only if they want their reordering to be persistent. If they just want to reorder/hide/show columns on tables temporarily, I think we should support that on the frontend.

[seancolsen]

we're trying to steer users towards the purpose of tables as only for storage

I think this "steering" will really come down to an amalgamation of many small UX choices. If we're able to do it well, then I agree that the user won't need to worry about table column order. But in attempting that steering we'll be fighting against deeply ingrained instincts that the user has acquired from other tools which do let them reorder columns. So we'll need to steer quite hard.

Reordering columns is a presentational thing

Here's an interesting counter-example: In #796 we seem to be coalescing on a decision to use the first 5 (ish) columns for a special purpose.

Note that this is only if they want their reordering to be persistent. If they just want to reorder/hide/show columns on tables temporarily, I think we should support that on the frontend.

I don't think it will be very useful to reorder columns unless that reordering persists, so I don't think we should spend time building that sort of functionality on the front end unless we can build a way to store it on the back end

[mathemancer]

Generally, we're trying to steer users towards the purpose of tables as only for storage / source of truth of the data and views being the main way they see data presented and work with it day-to-day.

I agree with this completely. I also think we should avoid introducing a service(or ui)-layer Table that is anything more than a minimal, thin tool for reflecting the database.

• some users will use both Mathesar and other clients. For them, the UI is not solely the representation in the web browser, but the combination of those multiple clients. They will need to see the same thing in both places as much as possible.
• We have a goal of accurately portraying DB concepts. While I want to avoid dogma for its own sake, I think that's a good goal because it reduces friction and confusion in the long run.

[pavish]

I think this boils down again to who we consider our primary target users are. Some of our design discussions revolve around simplifying things as much as possible for new users who are unaware of any db terminologies, and in cases like this we are actually making it complex for the same users.

Strictly speaking from a user standpoint alone, I would lose interest and switch a product if it requires me to do a bunch of configurations for what I consider a relatively simple usecase, which in this scenario is the ability to reorder, hide/show, resize columns etc., Even if we try to simplify it in a click of a button, it will create a new view for each table I modify which will only increase my confusion on the purpose of views in the first place.

I understand all the above arguments on the preference towards views, but I strongly feel we should not be too strict especially in case of features that users expect to work out-of-the-box with minimal/no configuration.

[ghislaineguerin]

I tend to agree with @pavish on this one. I also feel column reordering will be significant for our use cases, considering that our target users might start the process not from a blank table but most likely from imported data (as it is the case for our dabble DB inspiration).

[mathemancer]

I agree that would be nice if it were easy and obvious for users to work with data in real-time with minimal friction for reordering, hiding, filtering, etc. But, I think saving to a view (or even saving to a new table) should be more significant. I don't suggest we create a new view every time a user moves a column in the UI, but rather whenever they want to save some configuration of columns and rows that they're happy with.

That does leave open the question of how to help them understand when it makes sense to save a view. Think of editing a document. There's "autosave" every few seconds that keeps you from losing work if something crashes (theoretically). But then, when you're happy with a document state, or at least want to persist a given state indefinitely, you save the doc. If we can somehow portray an "autosave" that's essentially a stored query in the UI (eventually a service model), and then a "proper save" that persists a view on the data layer, I think that would be awesome. Unfortunately, I think this mental model will still create some confusion, since the view won't actually take much space. I.e., some users may try to avoid saving many different views of a given table through a misguided assumption that each view contains a copy of the table data.

Note @silentninja: I'm only talking about non-materialized for simplicity at the moment; I imagine we'll add "deep store" or "maximize speed" functions that will use materialized views later.

[mathemancer]

A few notes that didn't fit into a reply to others' comments:

• The user can already "reorder" columns in a table using exactly the process we would use on the DB: copying and dropping them in an appropriate sequence. We do need to make sure the copy is robust enough to handle all cases (but we need to ensure this anyway).
• I think a really powerful way of working with, organizing, and versioning views would be preferable to simulating DB concepts that don't really exist (reordering columns, etc.)
• If we really want to reorder table columns, I think we should try to do it in the DB layer, and we should try to do it in a way that portrays the actual process to the user (i.e., the process of adding new columns to the table in the appropriate order, setting up constraints on those columns if needed, copying data to the new columns, and finally dropping the old columns).

I do see @pavish's point, however, about some display options being stored by the front end (or service layer), and that extending this to column order might seem reasonable to many users. I think we should make it clear (if we go that direction) that such a representation isn't an actual VIEW or TABLE. This is because conflating these concepts would create inconsistencies in behavior between different objects being called, e.g., tables. Unfortunately, I don't have a good name at hand for that UI layer concept.

[pavish]

Just as an added clarification to my points, I do not think these display options should be persisted/configured at the columns API.

It's best if the columns API returns all columns and does not support ordering and hiding/showing, staying true to the db. I would prefer these operations (column order, resizing, hide/show) to be strictly treated as display properties and should not affect how the API itself works.

I am suggesting a completely different user specific API which stores user specific settings (@silentninja gave an example of theme). These configurations are best placed there, and would only reflect on the UI. If specifying user specific settings is not in scope of the initial release, we can store it on the localstorage for now.

[silentninja]

I am okay with it as long as it doesn't affect the table, else this would end up as a false representation. But at the same time, we have to make sure the users don't get accustomed to it. Should be treated as a one-off configuration

[seancolsen]

As a user, if I reorder the columns in a table, I would expect other users to see those changes too.

I really like the idea of user-specific functionality. For example, I've been frustrated in the past that when I filter a google spreadsheet, it filters for all users. I think views would be a more appropriate place to handle user-specific functionality like this. If we're aiming to steer the user towards thinking of the table as the underlying source of truth atop which other functionality is built, then it would seem more appropriate to build the user-specific functionality higher in that stack -- at the view layer, not the table layer. Additionally, all user-specific functionality should be very obviously user-specific.

[mathemancer]

It would be possible, once we have user-management set up at the DB layer, to have different users own different views, and also to have some views accessible via group roles. This would enable something like "my views", and it would have the added benefit of being consistent between the Mathesar UI and other clients.

[kgodey]

Thanks for contributing to the discussion so far, everyone. I've done a bunch more thinking and I stand by my original answer - I don't think it makes sense to support reordering in tables at the database level.

I wrote up a document about the purpose of views on the wiki here: https://wiki.mathesar.org/en/product/concepts/views, which will hopefully help clarify my thinking around it. There were also some good points raised in the discussion above that I'll respond to individually:

I think this "steering" will really come down to an amalgamation of many small UX choices. If we're able to do it well, then I agree that the user won't need to worry about table column order. But in attempting that steering we'll be fighting against deeply ingrained instincts that the user has acquired from other tools which do let them reorder columns. So we'll need to steer quite hard.

I think the most important thing we need to steer the user towards is that Views represent the concept of tables/spreadsheets that they associate with other tools.

Here's an interesting counter-example: In #796 we seem to be coalescing on a decision to use the first 5 (ish) columns for a special purpose.

This is a good point and I don't think we should rely on table order in that case either; I've left feedback in mathesar-foundation/mathesar-wiki#12 accordingly.

Strictly speaking from a user standpoint alone, I would lose interest and switch a product if it requires me to do a bunch of configurations for what I consider a relatively simple usecase, which in this scenario is the ability to reorder, hide/show, resize columns etc., Even if we try to simplify it in a click of a button, it will create a new view for each table I modify which will only increase my confusion on the purpose of views in the first place.

A new view will be created only if you want to save the changes to that table permanently in the database, which the user should not need to do. In the example in the Views wiki page, there is no need for the user to look at the Movie, Actor, Movie Actor Map or Movie Watch tables once they have set up the view. Our job is to build the product so that creating the view becomes the most natural thing for the user to do.

I also feel column reordering will be significant for our use cases, considering that our target users might start the process not from a blank table but most likely from imported data (as it is the case for our dabble DB inspiration).

This is why I think we should have some sort of flow that lets users know while they're browsing a table that if they want to sreorder the columns, they can create a View.

---

I'm open to considering user-specific ordering of table columns in the future, but I don't think we need it for our alpha release. I'd rather put our design efforts into trying to steer users towards Views and see how far we can go with that.