One-to-Many Table Relationship

[ghislaineguerin]

The design for issue #243 is ready for review. Please follow the Review Guidelines and make sure to review before the set deadline expires.

Document

https://wiki.mathesar.org/en/design/specs/views-one-to-many-relationships

Reviewers

@kgodey @pavish Your review is required.
Feedback from the community is also welcome. More context about Mathesar's design strategy can be found here.

Deadline

The deadline for submitting feedback is set 2 days from now, on Friday 26th.

[kgodey]

@ghislaineguerin This looks good. Some feedback:

(1) I think it would be good to display the first (non-primary key) column of the referenced table in the same column, similar to the dropdown for selecting a new record.

(2) There may be thousands or millions of records in the table that's being referenced. We need a way to link the correct record. We probably need some sort of search and autocomplete while linking records. Search should probably account for other fields in the table, not just the PK (e.g. if I want to link Aqua to Aquarium, I probably want to start typing Aq, not the ID of Aqua)

(3) I don't think we need the "From Referenced Tables" subheader in the table constraints section, all constraints are the same from the DB point of view. Can we find a different way to convey what you're trying to convey here? Maybe a tooltip?

(4) I think we might want different icons for primary keys and foreign keys to make the distinction clearer. Maybe some sort of "link" icon for FKs?

[ghislaineguerin]

@kgodey agreed, I wanted to keep the key concept present in the icon, but maybe we can do that without it, I'm concerned that the link icon will be too similar to URL data type. I will think about options.

[ghislaineguerin]

@kgodey

• Autocomplete has been added to the dropdown for adding records
• Updated icons have been added

[kgodey]

@ghislaineguerin The autocomplete looks good. The icon is hard to make out for me, I'm not sure what it represents.

[ghislaineguerin]

@kgodey I'm adding a note to review icons globally once the milestone design issues are ready.

[kgodey]

Sounds good, thanks.

[mathemancer]

A couple of things I noticed:

• this is actually describing a one-to-many relationship in general (one-to-one is a special case of that). one-to-many is the default foreign key relationship, and the most useful.
• The "orphan records" concept breaks the data consistency gained through use of foreign keys (in fact, this can't be easily implemented using foreign keys at the DB level; we'd have to get pretty hacky). What are we hoping to gain from this? Is it really necessary? I'd want to understand the use case more specifically if we are really sure that such a feature is needed:
  • In case of some "orphan records", am I correct that no new records would be added using that "orphan" value?
  • Would it be possible to update a record to use an "orphan" value?
  • When updating a record already containing an "orphan" value, should the new value for that cell be restricted by the foreign key linkage? What if that cell isn't modified in the update statement? Should the orphan value remain?

The usual options for what to do when a referenced value is deleted are:

• throw an error (warning) and refuse; this is the default for PostgreSQL.
• delete all rows that reference that value in the other table
• Set NULL wherever the deleted value was referenced.
• Set some default value wherever the deleted value was referenced (the default must be a valid id in the referenced table).

Relevant docs: https://www.postgresql.org/docs/13/ddl-constraints.html#DDL-CONSTRAINTS-FK

[mathemancer]

Ah, I was interpreting it as "allow us to keep the value in the referencing table if the value is removed from the referenced table"

More clarification would be great there.

[ghislaineguerin]

@mathemancer Yes, it's a one-to-many as I didn't have any good examples to demonstrate a 1 to 1. I agree that it's most useful.
As for orphan records, I did some research on data integrity and how it might be enforced, my concern was that since we don't have integrated views with the related tables, the user might forget or not know that a table record is referenced elsewhere and might accidentally create 'orphaned table records'.

This is an excerpt explaining how this feature is implemented in Microsoft Access:

The rules of integrity, by default, are designed to prevent the incidence of orphaned table records on the “many” side of relationships. As such, Microsoft Access prohibits the deletion of parent records or the modification of primary key values when associated child records exist. If referential integrity is not established, an indeterminate relationship exists between tables.

I'm open to ideas on how we can make that obvious to the users, as we won't have lookup fields in tables, only for views.

[mathemancer]

I think it would be much better to make the constraints involved in foreign key references obvious to the user, rather than allowing orphan records. Maybe a helper to make sure they chose an appropriate value? (drop-down combined with typing the first few characters?)

[kgodey]

Since the orphan records concept has been causing confusion to everyone on this thread and will also complicate implementation, I think we should remove the option and just use default foreign key behavior.

[kgodey]

Also, perhaps we should rename the spec "one-to-many" to avoid future confusion. I don't think we need to do anything specific for one-to-one at this point, it's just a case of one-to-many.

[pavish]

@ghislaineguerin @kgodey I have a few questions and thoughts:

In the menu

• We are linking tables release and artist through existing an existing column artist_id'. What happens when the values in the rows are not present on the referenced table artist?
  • This will throw an error from the db, how do we represent it on the frontend?
• I do not understand what 'Orphan records' mean. I assume it means we allow records to exist in release table when there is no linkable records on the referenced table artist, but the DB will not allow that.
• When clicking the option in 'Select table', it opens a new modal. It should be the same model. I assume you've meant it to be the same modal, but the prototype shows it as such. It is better to update the UX, since we have other contributors who may work on this and get confused.

The table

• I think it might be better to ask the user to define lookup fields, like airtable does, for searching records, instead of the first non-primary column. The first non-primary column may be a non-unique column, which would not be useful.
• We should not allow the user to type in values directly to a linked cell. Instead, it should act like a search box. The cell should only be updated when a valid value is clicked from the dropdown, and should clear if clicked elsewhere.
• While deleting a record from a referenced table (artist), we can show the tables that are linked, in the alert modal.

We also need to factor in how the UI will look when there is a one-many or many-one relationship involved. Since the menu options and modal is going to be similar for all three, it is better to start development work after we define the UX for all three. Or else, each design iteration will result in a complete rework on the frontend.

[ghislaineguerin]

@kgodey I've added the explanation to the modal where the table is selected, the modal you are showing here is where the lookup column is set on the actual table. Let me know if this is what you meant.

[kgodey]

@ghislaineguerin I think we need an explanation in the modal that I screenshotted as well, people aren't going to know what a lookup field is.

Also, the explanation that you set up doesn't actually explain what data lookup means.

[ghislaineguerin]

@kgodey got it, I will work on the message for both modals.

[ghislaineguerin]

@kgodey a new message describing lookups can be found in the most recent versions of the prototype. Is it ok to address this then?

[kgodey]

@ghislaineguerin Sure, please add a note to the spec where it should be reviewed so that I don't forget to review it.

[kgodey]

@mathemancer @pavish @ghislaineguerin FYI I renamed the spec in the wiki to be "one-to-many" and updated the link in the description.

[kgodey]

Based on the new action plan from the 2021-10-15 meeting, we're going to update the design requirements for issues in this milestone, including this one.