UNIQUE constraints on many-to-many tables
May 15, 2024 18:07:36 GMT -8
Post by Uncle Buddy on May 15, 2024 18:07:36 GMT -8
While rewriting the notes dialog, I found that it was possible to add the same pair of foreign keys to the notes_links table more than once, such as the same note_id and event_id pair. This has caused me to think about adding UNIQUE constraints to all my junction tables.
Question 1: Is it really a many-to-many relationship? Yes. Each note can be linked to many events, and each event can be linked to many notes. It's just that each element can only be linked to the same note once.
So this is OK because that's what many-many tables are for:
But this is undesirable since it serves no purpose and might create bugs:
Question 2: Will a UNIQUE constraint in the table schema solve the problem? Such as `UNIQUE (note_id, event_id)`.
I don't think it will. If the user tries to link a note to an element that the note is already linked to, a SQLite error will occur due to the UNIQUE constraint and the user has no use for that.
Adding UNIQUE constraints to all the possible pairings of note_id with other element IDs in the notes_links table would double the size of the table schema. This is not a problem if it's necessary but I don't see the advantage. A similar problem exists in media_links and roles_links.
Instead of complicating the UNIGEDS schema and then having to complicate the Treebard code to handle SQLite errors, I think the right approach is to add Python code to make it impossible for the user to insert the same link to the table twice. Also, my philosophy on messages, notifications and R-U-Sures is to keep things simple silently when possible. So if the user tries to link a note to an event when that note and event are already linked, I would just return on the function and not do anything, and not annoy the user with a message about his mistake.
It boils down to this: we are not here to fiddle with software. We're trying to do some genealogy here.
Question 1: Is it really a many-to-many relationship? Yes. Each note can be linked to many events, and each event can be linked to many notes. It's just that each element can only be linked to the same note once.
So this is OK because that's what many-many tables are for:
note_id | event_id |
12 | 98 |
127 | 42 |
But this is undesirable since it serves no purpose and might create bugs:
note_id | event_id |
12 | 98 |
12 | 98 |
Question 2: Will a UNIQUE constraint in the table schema solve the problem? Such as `UNIQUE (note_id, event_id)`.
I don't think it will. If the user tries to link a note to an element that the note is already linked to, a SQLite error will occur due to the UNIQUE constraint and the user has no use for that.
Adding UNIQUE constraints to all the possible pairings of note_id with other element IDs in the notes_links table would double the size of the table schema. This is not a problem if it's necessary but I don't see the advantage. A similar problem exists in media_links and roles_links.
Instead of complicating the UNIGEDS schema and then having to complicate the Treebard code to handle SQLite errors, I think the right approach is to add Python code to make it impossible for the user to insert the same link to the table twice. Also, my philosophy on messages, notifications and R-U-Sures is to keep things simple silently when possible. So if the user tries to link a note to an event when that note and event are already linked, I would just return on the function and not do anything, and not annoy the user with a message about his mistake.
It boils down to this: we are not here to fiddle with software. We're trying to do some genealogy here.