Editing values for columns with a foreign key constraint applied

[ghislaineguerin]

How can we make it easy for users to add linked records?

A foreign key constraint limits the data that the user can enter for a column to the values in the referenced table. In the case of tables created within Mathesar, the values should match the ID of the referenced table record, as they are set as the primary key.

We aim to make linking records easy for users so that they don't have to create complex tables or duplicate data and so that we can instead encourage them to use views to tie together related data.

The problem

We want users to only add the primary key values to the foreign key column, as this would match the values from the database. However, not all input methods are helpful when it comes to limiting value options.
A fundamental part of making this task more efficient will be in helping the user retrieve the records from the referencing table. For this purpose, we want to provide the user with a list of records from the referenced table whenever a foreign key field is on edit mode. However, to prevent errors in entering these values, we must restrict users to only set their values to those within the list.

To restrict values, we have two choices:

• We allow users to start typing and validate the value when the input loses focus
• We only allow users to select values from the list

In all cases:

• The user can select a cell and paste a valid value
• The user can copy the content of a selected cell
• The user can start typing as soon as the cell is selected and find matching records

Option 1: Allowing the user to type in the values (cell turns into input)

This first option allows the user to enter values by typing into the cell and provides an autocomplete feature. However, the user is not blocked from entering other non-listed values, which might trigger an error.

1. We start from a newly created column with a foreign key constraint applied to it. The foreign key constraint references the 'artist' table and is set to reference values from the 'artist' table primary key (as it is the default).

2. From here, the user can start editing the values of each cell in that column. As they toggle edit mode by double-clicking on a cell, a list of records is displayed.

3. As the user types, the list gets filtered.

4. If the user selects any of the options, the value is added.

---

Option 2: Enforcing selection from the list of records only (my preference)

This option removes the ability to type directly into the cell and instead restricts the input methods to list selection only.

1. We start from a newly created column with a foreign key constraint applied to it. When empty, the cells will have a 'search' icon.
   

2. Clicking on the cell will reveal the list of records for the referenced table.
   

3. As the user starts searching, the list narrows

3. If only one record is left, it is automatically selected, the user can press enter to confirm

4. The added values are shown in a tag-like style. When the cell is selected, the user can clear its contents by deselecting the list item or hitting the delete key.

Option 3: Allowing the user to type in any value and only save if valid, but provide a button to display the records list menu

This option could be a hybrid between options 1 and 2 but might be more slow and tedious for users that want to start typing directly into the cell. It also fails to prevent errors as efficiently as option 2.

---

Additional Problems to Solve

Displaying user-friendly values instead of ID numbers

Id numbers can be long and hard to read or differentiate from others. For that reason, we might opt to show the values for the first next column after the Id. This is not the optimal solution, but it might work until we implement lookup field functionality.

[seancolsen]

Overall

• This is a question which presents some interesting design problems and I'm glad we have a thread for it now because I've been pondering it for a bit!

Reactions

• I'm confused by the striking similarity between option 1 and option 2. I read them in side-by-side in two separate windows and, from what I can tell, the difference between them boils down to some very subtle auto-complete behavior. Option 3, being a hybrid between 1 and 2, only further confuses me. I feel like I'm missing something.

Additional challenges

• The target table might contain multiple relevant columns to search/display

  For example:

  • person

    id	first_name	last_name
    1	'Sean'	'Colsen'

  • email

    id	person_id	email
    11	1	...

  When adding the email record, I'd like to search for a person by typing either "Sean" or "Colsen" or both, and I'd like for both to display as I'm selecting a record.

• The target table might not have any suitable columns to search/display.

  • movie

    id	running_time	year
    1	105	2015
    2	87	2019

  • movie_name

    id	movie_id	language_code	name
    11	1	'no'	'Bølgen'
    12	1	'en'	'The Wave'
    13	2	'en'	'The Wave'

  • review

    id	movie_id	user_id	rating	body
    101	1	1001	5	...

  I this case it would be extra cool if, when editing review.movie_id and typing "The Wave", I could see an auto-complete with the following options:

  • The Wave (2019)
  • Bølgen AKA The Wave (2015)

Thoughts, opinions

• I'm less concerned with the nuances of the auto-complete UX than I am about the larger questions of how to gracefully handle the more interesting schemata.

• I also recognize the need to build features incrementally, and I don't want to slow us down by thinking too far ahead.

• But I'm a little worried that if we build the auto-complete on the premise of the table having one and only one search/display column (with distinct values) that we'll end up steering users away from creating the best schema for their data. I'm somewhat wondering whether it would be the best use or our resources to hold off on adding this feature at all until we can add it in a more robust way. That would mean that for the time being, a user could edit a cell in a FK column but if they don't enter the id number correctly they'll get an error. Terrible UX obviously, but perhaps holding off on this feature for now might be a quicker path towards implementing the more robust solution that I'm after.

Ideas

• I think it would be neat if the user could define a view to be used when selecting records for each FK. The first column in the view would give the value to be used (i.e. the ID). The seconds column in the view would be used to search (and display) the auto complete. Once we've figured out how to do more complex views with groupings and such, we could most likely handle that second case above.

[mathemancer]

• I think it would be neat if the user could define a view to be used when selecting records for each FK.

Love it.

[pavish]

I think it would be neat if the user could define a view to be used when selecting records for each FK

I assume by 'view', you mean a database view. I don't think that is a good idea for this particular usecase. Trying to define a view just for lookup will involve the user to define the lookup configuration (what view to use for each table), and creating a separate view. This would be too complex and confusing for a new user who just wants to link two tables. And the view by itself would be pointless to use elsewhere, considering that it is a db object.

But by view, if you mean opening up a modal or a larger dropdown, with the ability to select what columns to display while editing the cell, then I think it's a good idea.

[seancolsen]

Yes, I did mean 'view' to refer to the eventual Mathesar 'view' feature, including the UI to define the view and the underlying Postgres view as well. But perhaps we can work out a UX that has some "views-like" features, while not being a fully-fledged view. I'll describe a proposal for such a UX at the end.

@pavish you've raised several good concerns with my approach that have also been on my mind. To speak to them a bit...

• @pavish said:

  And the view by itself would be pointless to use elsewhere, considering that it is a db object.

  • I agree that we wouldn't want views like that cluttering up the view/table namespace and displaying within the left pane. But the user actually might want to re-use that view logic when linking to the same target table from another FK in another table. I'd want to have a way of auto-naming these "record selector views" and hiding them from the left pane.

  • There are still a lot of UX questions to answer about this whole idea like "how do I create/edit a record selector view?", "how do I choose an existing record selector view when adding a new FK?".

• @pavish said:

  opening up a modal or a larger dropdown, with the ability to select what columns to display while editing the cell, then I think it's a good idea.

  • If I'm understanding this idea correctly, it still wouldn't help the user in my second example above because the target table lacks any suitable columns for search/display.

• @pavish said:

  This would be too complex and confusing for a new user who just wants to link two tables

  • For simple cases like one artists table with a name column containing unique values -- yes -- I agree that it would be overly complex to require the user to define a view. We want to favor "convention over configuration", which means doing our best to set defaults that work in most cases.

  • If our default record selector relies on the second column in the target table as a search/display column, that will be great default behavior -- for some schemata. In others it will fail miserably. So, in what percentage of cases will it work well enough? I'd guess 60%. To me that's not high enough to be suitable as the default behavior.

  • What behavior would be suitable as a default? I'm not sure. It's a really tricky question.

• I wonder if we could build on your "modal" idea a bit to make a "default record selector" that would well for more schemata.

  Let's call this the "Modal selector" idea. First, just to flesh out this idea a bit, I'll get specific.

  1. User double clicks on cell to edit.
  2. Modal opens displaying data from all columns in target table.
  3. A search field displays above the table and has focus.
  4. User enters search query.
  5. Rows in table are filtered based on the search query. There are lots of interesting ways we could approach this problem. Best approach TBD.
  6. A "Choose" button displays within each row.
  7. When user has found their desired row, they click the "Choose" button. Keyboard navigation is also available via arrow keys and Enter to select.
  8. After selecting a row, the modal would close and the value would be set within the cell.

  From what I can tell, this approach would not present any additional clicks or keystrokes. It just takes up a lot more screen space. It comes at the cost of being slightly more overwhelming. But the benefit is that it better handles some more cases.

  Additionally:

  • As @pavish has suggested, the user could customize the columns that display from within the modal.

    The user could hide columns. When a column is hidden, it won't be used for search purposes or display purposes.

  • Plus, the user could add columns to display (and search) from linked tables.

    So in my multi-name movies example, the user would begin selecting movie records. They'd see

    	id	running_time	year
    [Choose]	1	105	2015
    [Choose]	2	87	2019

    Then they'd add movie_name.name. Then they'd see:

    	id	running_time	year	movie_name.name
    [Choose]	1	105	2015	Bølgen
    [Choose]	1	105	2015	The Wave
    [Choose]	2	87	2019	The Wave

    Note the duplicate entries for movie 1 because of the join.

[pavish]

But the user actually might want to re-use that view logic when linking to the same target table from another FK in another table. I'd want to have a way of auto-naming these "record selector views" and hiding them from the left pane.

If our entire autocomplete depends on user doing a bunch of configurations, it would make it really hard for new users.
If we automate it, it goes against our core idea of not doing anything on the database that the user might not be aware of. I wouldn't use Mathesar on a production db if it created db objects without my knowledge.

it still wouldn't help the user in my second example above because the target table lacks any suitable columns for search/display

I do think this is an interesting usecase and it would be great if we can get it right. However, I don't think we should be doing it by default. Maybe we can think of an advanced section that allows the user to do cool stuff like this, after our alpha release.

I wonder if we could build on your "modal" idea a bit to make a "default record selector" that would well for more schemata.

We can think further on this. I've responded in a separate comment thread here: #796 (comment)

[mathemancer]

I'm less worried about auto-complete in this case, and more concerned about repetition. Imagine you're going through your track list, and organizing them into albums. One of the advantages of a proper DB is that you don't have to type the album name over and over, so it would be cool if the UI guided the user towards using the primary key the next time they want that album.

Another idea: Maybe a UI flow that lets the user choose a given row from the referenced table, and then choose multiple rows in the referencing table (e.g., by ctrl-clicking or something), and then "enter" to do it all at once. This also really solidifies the foreign key concept for them.

Finally, for missing foreign records, it would be really cool, once we have the concept of record form input implemented, to use it in that case. That way, they'd avoid having to navigate away to the other table for input. They could just input the needed record right there, and keep moving.

[kgodey]

@mathemancer I read through your comments on all the different threads in this discussion a few times and I'm finding it difficult to understand what you're suggesting or to compare it to the new solution proposed by @ghislaineguerin here.

[mathemancer]

if they were copying and pasting that ID number, it would create the link as soon as they paste the value, right?

Correct (not implemented yet, but it would either be that, or a batched version of that).

My point is that the ID number in most cases will have no actual meaning. The meaning is the row that the ID represents, and so the idea that the user needs to find, copy, and paste the data they want in that cell is misleading. The thing they're copying and pasting isn't actually the data they want to link (again, in the most common case). So, I suspect many users who are used to finding data they want, copying that data, and pasting it in the place they want will be confused by a "copy-paste" paradigm that doesn't involve actually moving data from place to place.

I am curious to hear more about your suggestion of a UI where they can select rows and link them to other records and how do you feel that compares to the actual process of linking as you'd typically do it in a database setting

In a typical DB setting, to emulate the specific flow in question here, you'd try to come up with a query that described a subset of the data that should be linked to a given foreign record, and do an update on rows matching that query. It would, TBH, be a huge pain. More typically, you'd either have the data input in a denormalized state, then normalize it, creating the foreign keys from preexisting data. Or, you'd be inputting new rows with the foreign key on insert. It would be pretty rare (and kind of a sign of previous mistakes) to be in a situation of adding a foreign key column to a preexisting table full of data, then going down the column linking to other records. However, I think (as I'm sure we all do) that we should be trying to help users overcome any such mistakes as they work on their database schema.

As for UI, elements of this are mentioned in other places, but I think a flow that would mitigate performance impacts (mentioned in other places) and offer a way to portray the "linking action" instead of "copy-paste" would be to cache previously searched rows, and let the user easily add those for further records. For example, when attaching tracks to albums, it's likely you'll have tracks from a subset of your albums you're working with at one time. That is, unless you're quite unlucky, you shouldn't really need to keep searching all the albums to find the one you want as you go down the rows. With that in mind, I'll try to suggest a flow. For this, the "main" table is the one the user is entering data into, and the "foreign" table is the one with the records being linked.

1. You want to link a row from the foreign table to the main one. Find a relevant foreign row. This is shown, and once chosen is stored somewhere visual
   • maybe some kind of mini-table?
   • multiple rows could be there, the most recent 5 perhaps (see my other comments).
2. with that foreign row visually highlighted somehow, choose multiple foreign key entries in the main table that should be linked to that row (maybe by clicking the foreign key column cells in the main table).
3. If you move to a section of data in the main table that should instead be linked to one of the previous 5 (still shown in the foreign mini-table), you could click that foreign record and then link it to the cell in the main table.

I hope that makes sense; I'm trying to avoid having to get out my M$ paint skills :)

[kgodey]

@mathemancer I think I understand what you mean now, but I'm concerned that we'd be optimizing our entire UI for a use case where the user needs to work with recently-used foreign keys.

A use case that doesn't follow this pattern:

• I've added a new "Director" field to my table of movies and I'm adding directors to each movie. Directors are not often repeated so having a list of 5 recently added directors that I can then apply to various records doesn't sound very useful. I'd rather type and have it autocomplete.

I think you idea sounds great for optimizing for cases where users are linking to a smaller set of options (e.g. I added a new Status field to my table of tickets and I'm going through each record adding a status), but I don't think we can assume that that's going to be a majority of cases. It could also be an improvement that we could apply on top of the existing autocomplete idea in the other thread at a future date, they don't seem mutually exclusive.

[mathemancer]

I think I understand what you mean now, but I'm concerned that we'd be optimizing our entire UI for a use case where the user needs to work with recently-used foreign keys.

Point taken, but I don't think there's much optimization to be done (from a performance and UI standpoint) if they aren't doing something repetitive. I'm looking where there's light as they say.

Regardless, I think we'll eventually need both the ability to find records to link, and to cut out the repetition of linking them (especially since that look up is going to require a wait for any large-ish data) if we want a pleasant user experience. As you said, they're not mutually exclusive. I'm totally fine with waiting for the repetition removal. I would like to make sure the UI emphasizes linking the record, rather than pasting in a datum, but I do think the ideas in other parts of the discussion do that pretty well (especially with the multiple column concept; that makes it clear that you're hitting a whole row).

[kgodey]

@mathemancer Thanks! That all makes sense. For this iteration, I don't think we should implement the feature you're suggesting but I think it will be useful for the future.

[kgodey]

@ghislaineguerin I think everyone else has covered the points I wanted to make. I just want to reiterate that I don't think we can rely on a single column either for autocomplete or for displaying the value. I think we need to find a way to show multiple values at the very least (maybe the first 3 columns?) to help with the ambiguity.

I'm fine with whichever autocomplete option you'd like to go with, the main problem I think we need to solve is how to display the foreign key value without needing the user to configure something first.

[pavish]

@ghislaineguerin @seancolsen

Extending from "Option 2", which is @ghislaineguerin's choice and the "Modal selector" idea discussed here: #796 (reply in thread)

I think we can combine the "search and enforcing selection from the list of records only" idea with the "modal" idea, and include searching through all columns. Rough mockup:

1. User edits cell
2. We display a small table on the dropdown, which searches through all columns and displays results with the name of the column along with the value.
3. When user hovers a row, we show the values for all columns of that row, if that is possible to do. I can't think of a good layout for that yet
4. The user can open up a modal, by clicking on the 'All columns' button.
5. The functionality of the modal would be as @seancolsen explained in the above thread.
6. In addition to that, from within the modal, the user can select what columns to display on the dropdown by default. We can restrict upto 3 columns.

We can come up with better names for the terms used, this is just a rough mockup to get it out visually.

[mathemancer]

A compromise I just thought of: Look things up once in the back end, and cache the last, say, 5 or 10 foreign keys along with the foreign rows they represent in the front end so at least if they're doing something repetitive they don't have to wait for the look up in the DB every time.

[ghislaineguerin]

@pavish it's not clear to me what 'All Columns' is solving in this scenario. I really like the ability to retrieve the records based on matching values in any of the columns, it would make the search usable. I assume once a record is added, we'd have to show all the columns for that specific record, something like a card.

[ghislaineguerin]

@pavish how does this look like when the search box is empty? does it list all the records?

[pavish]

• it's not clear to me what 'All Columns' is solving in this scenario

  @ghislaineguerin From your other comment,

  Another idea is that we initially show the cards with the first 3 fields, and there's some sort of button to configure, and other fields can be selected from there

  This is what the 'All Columns' button was supposed to solve. The name on the table was just representational. The image above does not represent the whole idea. From my above points:

  The user can open up a modal, by clicking on the 'All columns' button.
  The functionality of the modal would be as @seancolsen explained in the above thread.

  It could also be a multi-select menu, in which the user can choose the columns they wish to search from.

• I assume once a record is added, we'd have to show all the columns for that specific record, something like a card.

  Yes, this is what I had in mind.

[pavish]

how does this look like when the search box is empty? does it list all the records?

My preference would be to list four or five records, just so that the user knows that they can search through records in this pane.

[ghislaineguerin]

Based on @pavish idea, do we want to have some sort of "card" that represents each record, and one search is performed, then the different columns are filtered showing only those where matches are produced?

Is this feasible from a technical standpoint, and does it offer good performance? I assume that the user could select which fields to include in these "lookup" cards at some point.

Another idea is that we initially show the cards with the first 3 fields, and there's some sort of button to configure, and other fields can be selected from there. We can explain that this will be saved when retrieving records from that particular table, similar to the "view" idea proposed by @seancolsen

[seancolsen]

As a heuristic for the default behavior, is it worth considering only searching in VARCHAR/CHAR columns? We could also limit the search to only columns that are indexed -- though I'm not sure what our strategy is for adding indexes, so that might be conversation for another time.

[kgodey]

@seancolsen We're not dealing with indexes in the alpha release, so I don't think we should search for only indexed columns. I like the idea of only searching VARCHAR/CHAR columns but I'm wary of potential use cases where those might not be useful (e.g. people might have unique or unique-ish columns for numbers dates, URLs, emails, etc.). I think we should add that optimization in the future if it proves useful.

@ghislaineguerin I'm not sure what you mean by this:

Given that we have that UI to select columns, should we persist that for that table? As in, if the same table is referenced elsewhere, do we show those selected columns as well?

Where else would tables be referenced?

[ghislaineguerin]

@kgodey If I add another FK column that points to 'artist', will the record dropdown maintain the columns that I have chosen or is it particular to the table from which the table is being referenced.

[mathemancer]

We're not dealing with indexes in the alpha release, so I don't think we should search for only indexed columns.

This is correct. However, we could "cheat" a bit: Column sets defining UNIQUE constraints in PostgreSQL are automatically indexed with b-tree indices. Further, the b-tree index does assist with prefix matching for text (or char or varchar) fields. Thus, for any individual columns which are marked "unique", or the first column of any set of columns marked "unique", we will have index assistance for these look ups (assuming prefix search; full-text would not be helped by b-tree indices).

Also, the columns which are going to be most useful for finding desired rows are often going to be ones that have unique values (not in all cases, but it's certainly an advantage). Thus, we could gently guide the users to mark columns they want to use for these look ups as "unique" whenever possible, and then use those columns for searching. (Again, assuming prefix search; @ghislaineguerin is that what you were thinking?) Whenever this unique constraint is possible, this would give us extra speed and performance (from the user perspective) without actually needing to implement anything extra under the hood, since we're already letting them work with UNIQUE constraints.

Edit: We may need to check the COLLATE settings to make the prefix search work without setting operator classes.
Edit2: Apparently you get prefix searching on b-tree indexed textual columns if the DB is using the C locale (which is the default). Do we have a reason to change that? See the docs: https://www.postgresql.org/docs/13/indexes-opclass.html
Edit3: Collation support docs: https://www.postgresql.org/docs/13/collation.html

[silentninja]

Just like @seancolsen mentioned, I am not able to find much difference between options 1 and 2. From what I understood, the first option allows you to enter an invalid value in the input table, which would anyway throw up an error, other than that I am not able to come up with differences, I think I am missing some key details between those options.

I came across some code examples with similar functionality that could be helpful to play around with

As for the search, functionality is concerned I would prefer if we keep it to basic search. When a foreign key constraint is added, we could ask for the column(can be extended to a limited set of columns) to be used as a lookup field, with the first column(or the column with the most repeated value) selected as default.

For anything more than a basic search, I think we should go with views as it provides the flexibility the user would need, as they would need multi-column, related column, full-text fuzzy search, ranking and if we have it by default, it would end up abstracting a lot of underlying details from the user.

We could use trigrams or full-text search without index and have the user create an index if they find the query to be slow(or we could monitor and let them know), this should help them make a decision between necessity vs complexity.

[kgodey]

I think the idea of creating an index if the user finds the query too slow is a great idea for the future (post-alpha release), it will also help introduce users to indexes. Otherwise, I think your ideas are pretty similar to the solution we've been coalescing around in the other thread.

[ghislaineguerin]

Thanks, @silentninja. Although both examples look very similar, there is a big difference between typing directly into the cell and retrieving records only from the selector. This difference is significant because, as described above, the accepted value is an ID number. Typing anything other than that would be invalid. If we allow users to type in, we'd have to change or replace any text that doesn't match the referenced table's primary key.

So for option 1, we assume that users will type in the exact ID or click to reveal the various records. We'd have to point them to another input field to search through records, select one, and fill the cell. We'd have two input fields, increased error opportunities, and potentially modifying a user-entered value.

For option 2, we assume that the user would not provide an ID number and would search for records instead. Entering values is restricted only to those in the list of matches, so users would not be allowed to type in data that we've already retrieved. They would select the record from the list every time or copy and paste the ID value.

[silentninja]

@ghislaineguerin Got it, Option 2 is much better then

[silentninja]

@kgodey Yep, haven't added much just summarised important points based on the above discussion