Data Collections/Roles and Row Owners oh my

Hello fellow Gliders.

I took the big leap and invested in the Maker Plan. I am getting a little lost in the trenches right now with my data goals. I am a School Counselor working on a Graduation Tracking App for School Counselors to better communicate graduation progress and tracking with their students and parent/guardians. Can someone please please help me set up my collections and data the right way? Here is an infographic of how I want to set up who see’s what.

I am using Google Sheets for my primary collection. In summary, I need:

  • Myself (App admin) access to all user data
  • Counselors within a school (School team) access to view/edit/upload student data at their individual school.
  • Individual students - access to view/suggest updates to data (AKA the student Dashboard). (final data only edited by counselor (Credits earned, credits remaining etc.)
  • Individual parents - access to only their own students data. Read/view only/suggest updates to data (final data only edited by counselor)

I need someone to help me understand if I need all this info in one data sheet or can it be multiple and I set up relationships? What Roles, Reader View, Row Owners, Array Columns do I need to set up?

Thank you for any guidance!

I think of a structure like this.

1. Schools

School ID School Name Counselor ID Location
001 Central High 1234 Springfield
002 Westview Academy 5678 Rivertown

2. Users

User ID Name Role School ID Children ID
1234 John Doe Counselor 001
5678 Sarah Smith Counselor 001
1001 Alice Johnson Student 001
1002 Bob Brown Student 001
P001 Mary Johnson Parent 001 1001
P002 David Brown Parent 001 1002

Key Details:

  • User ID: Unique identifier for each user (Counselor, Parent, or Student).
  • Role: Role of the user (e.g., Counselor, Student, Parent).
  • School ID: For all users, links them to their school.
  • Children ID: For Parents, lists the Student ID(s) of their child/children (in cases of multiple children, this can be a comma-separated list of Student ID values).

3. Graduation Progress

This sheet tracks the academic progress for each student.

Student ID Semester Credits Earned Credits Remaining Credits Completed Suggested Updates Counselor Approval Notes
1001 Fall 2023 10 10 180 None Approved Doing well this semester
1002 Spring 2024 20 40 140 Needs more electives Pending Needs to meet with counselor

4. Roles & Permissions

This table defines the permissions for each role.

User ID Role View Data Edit Data Suggest Updates
1234 Admin Yes Yes Yes
5678 Counselor Yes Yes Yes
1001 Student Yes No Yes
P001 Parent Yes No Yes

You can do relation - lookup to retrieve this info back to the Users table, and use that to configure component visibility. It helps to have a centralized table where you can just change it here, and it changes for all relevant components. You can use boolean instead of Yes/No.


5. Updates

Tracks updates suggested by students or parents and the approval status by counselors.

Update ID User ID Update Description Approved By ID Status
001 1001 Request for Elective Credit 1234 Approved
002 P001 Suggest additional summer course 5678 Pending
2 Likes

@ThinhDinh -this is amazing feedback, thank you for your help and outlining the various data tables. You listed out the 5 areas (Schools, Users, Graduation Progress, Roles and Permissions, and Updates). Just wanted to get clarification that these 5 would be there own data sheets, and I can use relations to bring data together? And by doing so counselors can see sheets for the students within their school but not other schools (Based on the school ID). THANK YOU AGAIN!!!

1 Like

Yes, they are all correct assumptions.

You can use the School ID as the “Role” column in your setup, and then record the School ID in any other table, so you have a “security wall” for each school.

Alright I will give his a go. That is a good video - thank you for sharing. I happen to see that there were no tables with Row Owners assigned. Is that something I would not need assigned? Instead just User ID’s/school ID etc…?

Do you mean in my sample data?

I mean if you need higher level data security in your app, you should do this.

@ThinhDinh yes sorry that’s what I meant - in your sample data. Thanks you for the added security recommendation.

So are you cleared about what to do with row owners? The video I linked should help you with that, but please feel free to add any questions.

I have a vague understanding, I think I will figure it out as I work! But at least I have a direction. Thank you.

1 Like