Hello! I was looking for best practices for determining if a linked table should be editable one way or editable from both tables.
For context, I have 4 tables:
Users (i.e., Sam, John, Kristen),
Groups (i.e., Sales Team, Administrators),
Roles (Sale Rep, IT Manger), and
Doc Pages as a Table (Dashboard Page, CRM page).
The objective is to assign access (based on Group?, Role?, User?) to each row of the the Doc Page Table.
There are different ways to link these tables and whether to turn on the 2-way edits or not. I’m curious to see what is typically done. I feel like there is a fundamental hierarchy that I’m missing here.
Hi Theresa, not sure whether there are hard and fast rules around this.
Personally I typically do not use the two way editing when both tables have a number of columns, because you in any case need to go to the other table and maintain the rest of the columns.
But if the one table is simply a lookup column, then I will use the two way editing.
Thanks for the feedback Piet. I was struggling to establish criteria that would help correctly and consistently link tables because even editing in a one way direction the question becomes which table is it?
So, I’ve done some searching with database designing and application. I focused on the normalization requirement particularly the 1st normal form (1NF), which states that at every row and column intersection in the table there, exists a single value, and never a list of values. Since each of my tables have a many-to-many relationship, I was not meeting this requirement.
The solution I came across was to create a junction table (i.e. User-Group, Group-Roles) which is simply matching info from different tables into a single row (See below).
However, I had the hardest time understanding how the junction tables get populated if there were no direct relationship columns between the 4 tables. After some more digging, I found that this is where the UI/UX comes into play. Set up a page with controls (text box, drop down menus, etc.), some pre-populated, that a user will fill out and when submitted the rows are then created in those junction tables as well as the original tables. (I found an example below)
For me this set up/framework make sense because creating a relationship column in a given table will pull in all the info from the second table. I imagine such a setup makes the doc size large, filtering more complex, and slowing down performance.
