Help users link tables via question-based forms

[ghislaineguerin]

Problem definition

We need to implement a design solution to help users link tables based on foreign key relationships. Although an obscure construct for non-DB users, the foreign key can be a potent tool in relational databases. It allows associating single records from a table to any other table by limiting the values of a column to those that match the linked record primary key only.

There are some challenging aspects around this problem, with a high risk of creating friction for users as most data management apps create relationships in the background or provide abstractions to help create relationships. Also, some users might confuse foreign-key relationships with joins or think about outcomes produced by a join, such as having a table where multiple fields from the linked tables are present. The problem with this is that foreign keys are just constraints to enforce referential integrity so, the user will still need to use joins to achieve their desired outcome.

Suggested Design Solution

NOTE: This is a draft of the solution and does not represent the final design

Using question-based forms to help users to define, verify and assign foreign key relationships

Question-based flows can help users understand the necessary steps to achieving their goals. By capturing intentions with question-based flows, we can directly involve the user in the steps, connecting the processes and guiding them where to go next. These flows work best when there is some ambiguity on what is going to happen next. For example, a user will link tables but doesn't know the system will add a constraint.

Example of linking tables via question-based form

Assumptions

• The user has already created the tables they wish to link
• No foreign-key constraints exist for those tables

Starting point on the 'many' side of the relationship

A user has a table called 'release' containing records representing a single release, each with a unique Id number. The user wishes to create a table that will list all the releases by name and the artist(s) associated with each release.

From the table toolbar, the user will click on the 'Link Table' option. After selecting 'Link Table,' a dialog box will open with the following content:

From here, the user can select a table, prompting a couple of questions to define the relationship. In this example, the user links the 'release' table to the 'artist' table. For this first example, we'll assume that 'release' can only be linked to a single record from 'artist.'

The user answers yes or no to the questions based on their desired outcome.

A foreign-key constraint is added to the 'release' table based on the user's selected relationships. A new column named 'artistId' has also been created.

The user can now link to records from the 'artist' table.

Starting point on the 'one' side of the relationship

A user has a table called 'artist' that contains records representing a single artist. The user wishes to create a table that will list all the artists and their associated releases from a 'release' table.

From the table toolbar, the user will click on the 'Link Table' option. Like the previous example, the user will select the table they wish to link to and answer the questions.

This time, the system added a constraint to the linked table instead of where the user initiated the process—achieving the same outcome in both cases.

Starting point on any of the sides of a 'many-to-many' relationship

A user has a pair of tables 'release,' and 'artist' that they wish to link so that 'artist' is linked to multiple records from 'release' and 'release' is linked to multiple records from 'artist.'

From any of the tables on this set, the user clicks on the 'Link Table' option located in the table toolbar. However, this time, they answer 'yes' to both questions.

This time, a new table is created called 'release_artist' or 'artist_release' depending on which table the user started the process from.

The resulting table has two columns referencing the primary keys of the linked tables.

Suggested Next Steps

• Define UI component and interactions for questions
• Improve the content of the 'Table Contraints' form so that it includes information that can help users understand what was set
• Define the behavior for cases where a foreign key is already set with the same options
• Define the flow from set foreign key constraint to a view where the joins between tables are defined
• Define how multi-column foreign keys would be presented

VIDEO: https://www.loom.com/share/e1d7d376c61145229d9c32533bff750f

[kgodey]

@ghislaineguerin the overall flow looks good.

Some thoughts:

I think before we get to the foreign key modal, we need to show a confirmation message that says something like
"We will now create a column in artist" / "We will now create a new table to map artist to release" and ask for user confirmation. Then we can take them to the constraint screen.

[seancolsen]

We need a place to show a message that explains the actions we've taken...

I'd like to use the toast system (to be created) to handle messages like that. As such, I don't think we need to allocate space for that message within the mockup.

[seancolsen]

Overall

• I really like the direction this is heading!
• I appreciate the specificity and narrow focus of this discussion.

Questions

• Do we want to allow the user to create an FK that targets a column which is not a primary key? Perhaps if they want to do that they need to open the "Table Constraints" modal and manually add the constraint? Or perhaps we're not supporting that use-case yet? Either approach is fine with me. Just curious.

• How will the user create an FK from release.artistId to artist.id if the column release.artistId already exists? Will we allow the user to select that column from with within the "Link to Table" form? Or will they need to manually create the constraint from within the "Table Constraints modal? Either approach is fine with me. Just curious.

Suggestions

• For the two questions, I'd prefer if we use a "Yes"/"No" radio group instead of a checkbox. One reason is that it steers the user to towards making a deliberate choice instead of sticking with the defaults. Indeed, if both boxes are unchecked by default we have a one-to-one relationship, which I'd argue are rare in normalized schemata. Further, on a grammatical note, I think a "Yes"/"No" answer also fits better with the wording of the field label being a question, whereas I'm usually inclined to pair checkboxes with simpler true/false phrases.

• Or, here's an alternate interface that could replace the two questions with one question.

  

  For a11y, we'd use some alt text like "Each artists record can have multiple releases records. Each releases record has no more than one artists record", and we'd also set that to display as a hover tool-tip on each option. We'd use SVG to create the diagrams so that we could put the table names in there dynamically.

• After the user selects a table and answers the questions, I'd like to allow them to customize the names of all new tables/columns we create. We can still pre-fill those fields with names we generate, but I'd like to show text input fields for those values so the user can change them before pressing "Apply". The fields would only appear once the previous questions have been answered. This UI will also give the user a clearer expectation of what will happen after they click "Apply".

• Improve the buttons on the bottom of the modal (these changes may very well be implied, given the rough state of the mockup):

  • Add a "Cancel" button on the left.
  • Move "Apply" to the right side.
  • Make the "Apply" button disabled until the user can submit the form.

• I don't think we need to open the "Table Constraints" modal after the user adds a new FK. Closing the "Link to Table" modal and displaying a detailed success toast message is sufficient I think.

[ghislaineguerin]

Do we want to allow the user to create an FK that targets a column which is not a primary key? Perhaps if they want to do that they need to open the "Table Constraints" modal and manually add the constraint?

I thought the same, however, I'm not sure how we'd limit the options if, at the same time, we'd need to:

• Display foreign keys from existing databases (created outside of mathesar)
• Display multi-column (composite) foreign keys

We could, maybe, hide the columns and display some sort of toggle for advanced users, as well as a warning for the user when changing the FK column. A likely scenario since they might think that's how they can display other fields instead of Id.

How will the user create an FK from release.artistId to artist.id if the column release.artistId already exists? Will we allow the user to select that column from with within the "Link to Table" form? Or will they need to manually create the constraint from within the "Table Constraints modal?

We have no way of knowing a column exists or making decisions based on the column name, so my guess is that we'd duplicate the column if the user had created and then started the link table process.
I think for the sake of simplicity and prioritization we'd need the user to add it manually, as in, create a foreign key constraint from the menu. This, of course, might be out of reach for a non-advanced user, but we'll have the opportunity to introduce the same concept again at the view level when a user tries to add a column from another table, so we might just wait and optimize then.

For the two questions, I'd prefer if we use a "Yes"/"No" radio group instead of a checkbox.

This makes sense, I had the checkboxes initially and was thinking of a yes/no toggle, but I feel we shouldn't assume default 'no' values and have the user explicitly decide. I agree with your suggestion.

Or, here's an alternate interface that could replace the two questions with one question.

I like that your option introduces the DB cardinality concepts to the users. I did like that the question-based one used natural language to convey the idea. There's a lot of support around the idea of using natural language to help non-technical users create queries, so in a way, I feel the wording of the questions might help.

After the user selects a table and answers the questions, I'd like to allow them to customize the names of all new tables/columns we create. We can still pre-fill those fields with names we generate, but I'd like to show text input fields for those values so the user can change them before pressing "Apply".

I agree they should be able to edit, my concern is that at this point they might have a low understanding of why we created those in the first place, so naming might be low priority to them. We could improve that of course.

Improve the buttons on the bottom of the modal (these changes may very well be implied, given the rough state of the mockup)

This was just a rough prototype issue, thanks for the suggestions :)

I don't think we need to open the "Table Constraints" modal after the user adds a new FK. Closing the "Link to Table" modal and displaying a detailed success toast message is sufficient I think.

We might take the user to another table or a new table after this step. We could use some sort of summary if we are not going to display this. I'll think about it.

[pavish]

@ghislaineguerin The flow looks good to me. I have a small thought that might make this more accessible for both non-technical and technical users.

Non-technical user

For of a new user who has very little idea on databases, the above design and the 'Link to table' modal would be ideal.

User who understands db terminologies

Someone who understands database terminologies/cares about what happens on the db would be confused by the word 'Link' and by the questions, and they would not know what changes happen on the db.

They might probably be more comfortable doing things manually from the constraints modal.

The problem is we don't want to mislead them into thinking that we don't allow manual configurations when they click 'Link Table' button.

Concern:

While trying to make the UX easier for users new to databases, we do not want to make it harder for users who understand db terminologies.

Suggestion:

My suggestion would be to show additional text on the modal, providing more technical details for the configuration the user has chosen, and a link to where they can do it manually. Non-technical users can safely ignore this, while technical users would be more comfortable knowing this.

Examples:

If neither checkboxes are checked:

If both checkboxes are checked:

Doing the above also removes the necessity for additional confirmation.

[pavish]

We could also not redirect the user to the constraints modal if we show this information here. It would mostly be redundant, and they can click the constraints button to see it, if they wish to.

[ghislaineguerin]

@pavish @seancolsen @kgodey thanks for the comments. Some thoughts based on your comments:

• I like @seancolsen idea of naming each type of relationship, if we combine that with @pavish idea we could show the appropriate label in the summary "create a X type of relationship"
• @kgodey the subtitle you've proposed for this dialog seems to only imply one type of relationship. Could we use it instead to introduce the foreign key concept?

1. Click 'Link Table' from the 'artist' table
   

2. Select the 'release' table
   

3. Answering the questions
   A summary is displayed to inform users
   

4. Applying the change
   Release table is open and the fk column is created
   

5. Constraint is created
   A foreign key constraint was created
   

[seancolsen]

@ghislaineguerin I like all these improvements!

You've address all my suggestions except one -- I'd still prefer to show the name of new columns (and tables in the many-to-many case) within a text field that the user can edit. But I can let go of that. We could even add it later if we choose.

[ghislaineguerin]

@seancolsen thanks. This is the summary showing for many-to-many:

I didn't include the editing as to not increase the scope, but I will create a placeholder issue for it as I think it's worth considering.

[kgodey]

@ghislaineguerin I like this flow a lot.

I'm fine with the current subtitle, except that I would replace "using a foreign key" to "by setting up foreign key constraints"

I also think we should avoid using the words "relationship" and "link" interchangeably, so we should have the confirmation message say "Create a many-to-many link" and "Create a one-to-many link".

I also liked @pavish's suggestion of an "Under the hood" header because it explains what's going on. See suggestion:

I think the action button would be clearer if we changed it to "Create Link" instead of "Apply"

[pavish]

@ghislaineguerin The flow looks solid to me!

I think my major concern still needs to be handled: we don't want to mislead technical users into thinking that we don't allow manual configurations when they click 'Link Table' button

Currently when the user opens the 'Link Table' modal, unless the summary appears, they don't know if they can configure it manually.

I think we should always show 'If you prefer to configure this manually, go to constraints settings' at the bottom, regardless of the summary being shown or not.

[mathemancer]

A couple of comments:

• keep in mind what we can and can't infer from table and copy names w.r.t. other object type names. We can't easily infer "artist" as an entry in the "artists" table (at least not consistently with different pluralizations and more poorly-named tables).
• For me, @seancolsen's diagram idea would let me choose the appropriate setup immediately, and be much more secure that I've chosen correctly. The question-answer setup would take more thinking "is this correct?" and essentially trying to draw those diagrams in my head (maybe I'm just visual that way).

Overall, I really like the direction this is going, though.

[ghislaineguerin]

Also I assume you're still working on the "under the hood" message and the "Apply" button since it doesn't address my feedback from above.

@kgodey I'm still working on that, yes. Thanks for the feedback.

[seancolsen]

The diagram will definitely add more complexity to the implementation. If we're not relying on the diagram, we could add a separate ticket for it and address it in a later milestone. I'm fine either way.

[kgodey]

Okay, let's add a separate ticket for implementing it. @ghislaineguerin it's up to you whether you want to do the design for the diagram now or in a later milestone.

[pavish]

I like adding the diagram. I'm not worried too much on the complexity of the diagrams, since these could probably be exported from figma as 4 svg images, and we can serve them as static files. There can just be an img tag linking to the diagrams based on the chosen configuration. I wouldn't want to render the svg directly on the browser though, which will be complex and unnecessary for this particular usecase.

[mathemancer]

Regardless of when we end up adding the diagrams, I think the concept in @ghislaineguerin's demo there is really great, since it would help the user put the visual together with the binary logic.

[ghislaineguerin]

@kgodey @pavish @mathemancer @seancolsen
Thanks for your comments/suggestions. I have updated the prototype to allow the renaming of the new objects (tables or columns).

Unless there are any major concerns or overlooked requirements I will complete the spec for this design.

[kgodey]

I don't have any concerns. The new table that's being created will also have two columns for each table in the map (artist_id and track_id). We might want to ask the user to specify those names as well.

[mathemancer]

I'd just like to add that I'm really excited about that prototype; I think it's great!