I’m wondering how to best setup some tables before getting into building out the rest of my doc. In our company we’ve got my department using Coda internally, for ourselves and no other teams right now. This means there are ~10 of us that will be users in Coda. We also often times need to include other teams in our projects but they won’t necessarily be using Coda or even getting a login due to us just needing the ability to track them on their involvement for our team but nothing past that. We hire contractors for a lot of these projects as well so again, they won’t need to be in Coda and will not receive logins but they will need to be tracked for us to aggregate a variety of data.
Additionally, we’ll have another table for projects where people can be assigned as the lead, support, and also contractors all as separate columns.
Our team (users) will almost always be the lead (with very few exceptions), but we will also often times be listed under support on the projects table. We also use Coda for other things, tasks, process automation, etc… so it’s important that we’re able to receive notifications.
But the catch for our team is that we can also be listed as Support on these projects with contractors and other employees that aren’t users in Coda.
Contractors can only be listed as contractors and other departments will only be listed as support.
The last piece of this is we’d like to be able to pull in some historical data whih would include some former employees which many were leads on these projects. So we can’t simply just make the “Leads” column a People/Users column as we’d have no way to reference the former employees.
Is the best way to approach this to have our department as it’s own table with each person as their own row? And a separate “people” table that would include contractors, former employees, and all other internal departments whom of which will not be coda users?
Thank you for any help!
Hey! I can’t say I understood your specific case. Usually with a client I would ask them a lot of questions to understand the whole picture. But here is some generic advice that worked for me in the past.
The rule of thumb is this: One concept in your system (one kind of thing) — one table.
Everyone in your system is a team member, just with different roles? Prefer a single table with a Role column then.
You have clear distinction between e.g. Users and Staff members? (e.g. a platform with Teachers and Class managers; those two groups have distinct sets of properties and are not interchangeable.) Separate tables then.
Exceptions:
- Different entities coming from different docs via cross-doc (e.g. the list of users from one doc and the list from employees from another). Then you build your system around the fact that these are two different entities.
- You’re implementing “sessions”, i.e. when each Coda user gets a row in a helper table where they can set up their individual filters. Then it’s better to make it a separate table, where the “unique key” would be a Coda user, and the employee record from the Employees database would be looked up. In this case better to keep Employees table separate (with employee properties such as name, address etc), and the inputs table (with controls tweaked in by that employee) separate.
Thanks, Paul!
I’ll note that depending on your role there can be a handful of varying “properties” or columns. Probably about 5-6 additional columns for a contractor than an internal employee.
and
One team will be actual users in Coda so we’d like to utilize the “People” column type but this team could potentially be listed in a column with other non-coda users so I don’t see a way to add in a person that’s not a user and a person that is a user into the same column while still being able to use the advantages of being a user (notifications).
It’s okay if for some rows some columns remain blank.
My usual approach would be a hunch: do these two categories of people are actually two different things within the system? E.g., I’m currently working on an online teaching platform, and Teachers, Students, and Employees are three separate tables. There’s nothing common in them beyond name and email; there are some relations that require these to be separate entities from database design point of view (e.g. a Student has an Employee who’s responsible for that Student’s classes scheduling).
Then, we have a doc that both Teachers and Employees use. In this doc we have a separate table (DB Doc users) where the first column is a People column (Coda user), and then a column Current teacher, which
- For teachers it resolves to their Teacher record
- For employees it resolves to a manually selected “which teacher I’m managing now”, so that the employee can see the doc like that teacher would
- Filters on all tables are set up to read from, basically,
Teacher = [DB Doc users].Filter([Coda user] = User()).[Current teacher].First()
TL;DR: you design your database from what makes the most sense for your data. Then you have a separate table that links a Coda user to an employee/user/contractor/whoever’s record.
Okay, I think I’m following!
If I’m able to bring all “people” to one table but still have a separate table underneath where we simply just reference all users in Coda. Could I still allow notifications to be sent to the actual users when they’re added to a column even if it’s not their actual user record but a link to it?
Also, am I able to filter what my options are in a look up based on the criteria found in the lookup table?
You are able to set up lookup filters on any criteria, so that’s okay. You can still paste anything into a cell though (including a record from another table — there’s no actual type safety in Coda).
Regarding notifications, not sure though. I think it only works if you assign a People object into a cell, but not through an association through any table. Interestingly, in almost a year of my Coda consulting practice I haven’t relied on those kinds of notifications a single time — it’s always been explicitly coded mechanisms, like someone presses the button and the notification gets sent to a Coda user (with Notify() action), and I look up that user from the database somewhere based on e.g. a lookup to the Employees table.
If anything, you can have an automation observing when the, e.g., Assignee (lookup to the Team members table) value changes for a row, then have a Coda user (People type) linked to that Team member, then your automation would just catch the row that triggered it and do something like
Notify(
[Step 1 Result].Assignee.[Coda user],
Concatenate("You've been assigned a task: ", [Step 1 Result].Task)
)
Actually, if you don’t need those individual filters etc, you can just have a People column on your Employees table. For some employees there will be a Coda account assigned, and for some it will be blank. No need to complicate with a separate table then.
Please mind that I don’t fully understand your situation. Usually I’m only able to suggest the best solution for the task when I know everything about a client’s operation. Please take this as a generic guidance, not a concrete plan of action, because you know your workflow but I don’t.
That’s great!
I think this might just work and be exactly the explanation I was looking for! I’m going to poke around with it a bit but I really appreciate your help, Paul!