I am building a project management app. I want to assign multiple users to a project. What does that structure need to be like on glide tables?
The two most common approaches are:
- use a separate join table with one row per project per user
- add a column in the Projects table to hold a joined list of UserIDs
Either approach is okay, the second uses less rows, if that is a concern.
1 Like
If number of rows is of no concern and assuming a user can work on many projects and a project can have multiple users working on it, I would use a join table for this many-to-many relation.
I have trouble with relations and database structure and table cleanliness is general. I’ll do anything to put chances on my side. Also application maintainability and scalability are top of mind to me. I think using a join table here would be simpler to understand, it would require less fiddling around with a comma-separated list in a joined list column, overall it would be cleaner.
1 Like
I want to let user from the front end of the app to select who he wants to add to the project and store it on table. What approach must I take?
How to populate it on project table from the helper table?
A really easy way to add users to a project is to use a choice component with multi-select. This will give you a comma separated list of users. Then use a split text column to convert it to an array. With this array you can create a relation from users to projects and from projects to users.
3 Likes
I think you have 2 approaches, and both are fine.
Approach A: 2 tables (Projects, Users)
Follow Jeff’s suggestion above. Use a choice component. Easy, straightforward both in the data editor and layout editor.
Approach B: 3 tables (Projects, Users, ProjectUser)
On the detail screen of a project, or on a new screen on which you’ve passed on the project ID, insert a collection of users (which you can also filter if needed). As a collection item action, create a “Add to project” action. Triggering this action will create a new row in the ProjectUser table and thereby write a user ID, project ID and any other information related to that new row you might need. Something like this:
Approach A is easier to set up and uses less rows. Approach B is easier to understand when it comes to many-to-many relations (to me) and more versatile in that the relation exists as a table in and of itself (approach B) and not just as a column (approach A).
Both approaches are fine. If you have no idea which approach is best for you, then the easiest is best, go for approach A (Jeff’s recommendation).
2 Likes