Add 'Table Link Preferences' Option to Column Menu

[ghislaineguerin]

Context

In solving the design for foreign key constraints, we identified the need for defining table-level preferences. These settings would store the different user's preferences in displaying, retrieving, or using a specific table and its records within Mathesar.
One example of a table preference is 'Search Columns,' which stores the list of columns queried when searching records from that table. This preference applies to all instances where the table is being referenced.

In discussing the details for table preferences, there were some concerns about not letting users select a different set of columns when retrieving records for a particular column in another table. In other words, override the default 'Search Columns' that were set in the referenced table.

Overriding Defaults

While user defaults are best for maintaining a consistent user experience, we want to allow flexibility in situations where we think the user will gain efficiency, meaning we either reduce the number of steps or make a repetitive action easier. Retrieving records is likely going to be a constant task for a user setting up their data tables.

So, if we were to override these defaults, the design would have to introduce a new level of preferences for the columns of a table.
While we already have some column-level properties (e.g., type, name), we don't really differentiate between those and other more specific sets of preferences. In this case, these preferences would only be accessible if the column has a link to another table. So rather than having a general column preferences section, we might have a 'Table Link Preferences' section that contains options like record previews, search columns, and more.

This would eliminate my concern about overriding defaults from multiple places in the app and not having a single location to store, retrieve and modify them. The user would then know where to go if, let's say, they wanted to reset those columns to the default preference set in the reference table.

Proposed Menu Structure

Table Menu

• Table Properties (name, constraints)
• Table Commands (actions like duplicate, delete, etc.)
• Table Preferences (display options, search columns)

Column Menu

• Column Properties (name, type)
• Column Commands (actions like duplicate, delete, etc.)
• Column Preferences (applies to all column states)
• Table Link Preferences (only shows if the column has foreign key constraint applied)

Questions

• Does the idea of having a 'Table Link Preferences' section make sense?
• Do you envision any issues in creating a separate set of preferences for columns that link to a table?
• Do you think it might be better to have a 'Column Preferences' section instead and have 'Table Link' as a section?

[seancolsen]

To make sure I understand correctly

A contrived example:

1. We have an artist table.
2. We have tables album, concert, and interview that each have a FK column to artist.
3. We configure the artist table to allow all referencing FK columns to search by artist.name.
4. But for the interview table specifically we want to search on artist.name and artist.nickname, so we need a way to set that customization.

Is that correct?

Thoughts

• I think we're probably going to be fine for quite a while with the assumption that all referencing tables (i.e. album, concert, and interview) behave in the same manner when choosing a record in the referenced table (i.e. artist). Even if we did want to set the customization in step 4, I think we'd still want to maintain the preferences set in step 3. So for the sake of prioritizing our tasks, I'd propose we hold off on pursuing the more granular preferences until a we have a Mathesar user that really needs it.
• Once we do need this level of customization, I think it would be best done on the column level -- i.e. set the custom preferences on interview.artist_id (not on interview). Why? Because one table may have multiple FK columns to the same target table which would need different customizations.

[kgodey]

When I was thinking about this, I wasn't thinking that we'd be able to override search columns at the column level. I just think we need a way for users to change their (global) preferences while they're searching at the column level. e.g. from @seancolsen's example - the user might have only set the search columns to artist.name, but they realize while looking for artists in the interview table that they also want to be able to search by artist.nickname. So they set the search columns to artist.name and artist.nickname, but it's a global setting. So if they are now in the concert table searching for an artist, they will now be able to search by both those columns. Does that make sense?

[seancolsen]

@kgodey when you say "global" you mean a setting at the level of the artist table, right? (i.e. Not "global" in the sense that the setting would be at the level of the schema or the database, right?) If I'm interpreting your usage of "global" correctly, then those "table-level" preference should already be covered by our discussion in #835 where we basically agreed to adopt the strategy you're describing. Or maybe I'm misunderstanding something about your comment or one of these discussion threads.

[kgodey]

@seancolsen Yes, that's what I mean by "global". If that's what we're doing, that's fine. I was a little confused by what @ghislaineguerin meant here:

We'd just have instances where we override those at the column level.

I don't think we should implement having column-level preferences that are different from the table-level preferences.

[ghislaineguerin]

@kgodey That was my initial idea as well. I wasn't sure why we'd want to override the settings for a particular instance so I started this discussion. If we don't have any strong reasons to support overriding, I'm perfectly fine with not having an additional level of preferences, it would simplify implementation too.

[kgodey]

@ghislaineguerin Sounds like we're in agreement about not supporting overriding, let's do that.