Best way to structure employees and tasks

[Burner]

Hi,

Sorry in advance for the long post, but I think it may be important for context.

I’m creating a base for HR and figuring out the best way to structure the base. Here’s what I’m trying to accomplish and my dilemma.

I have two entities (still figuring out if these should be separate tables or within the same table, which is why I’m referring to them as entities). (1) Employees and (2) Onboarding tasks.

Employees will have their own properties so fields like name, position, date of joining, and a ton of other fields. Onboarding tasks also have a bunch of properties such as task description, applicable to (FTE/PTE), responsible for task (HR or Employee since HR has to perform some of the tasks and there are other tasks that the employee must perform) and a bunch of other onboarding task fields. Also, there are about 60 onboarding tasks, so that number is a factor in my design approach.

My approaches are:

Approach 1
Employees in one table. Onboarding tasks in another table (listing out the 60 onboarding tasks). Then in the Employee table, I create a linked reference field to link all the onboarding tasks completed for an employee. This approach separates the onboarding tasks to it’s own table where I can add more onboarding tasks as they come about as well as makes the base relational, so that each onboarding task is related to each employee record. So I guess that’s nice.
The downside of this approach is that the Employee table will have a column that has around 60 linked records for each employee record (as onboarding tasks are completed). This is not very useful – the linking seems like linking for the sake of it, but not very useful in everyday life. Also, this assumes that each onboarding task is a binary (done or not done) for it to be linked to an employee record. So if “Create official email” is completed, that onboarding task is linked to the employee ‘John Smith’ implying that this task has been done. Not that I have an issue with it, but this seems somewhat limited.

Approach 2
I have each of the 60 onboarding tasks as checkbox fields within the employee table itself. I can create a series of views separating these onboarding tasks from the other employee personnel information and further breakup the onboarding tasks into sub-views indicating phases. So I’d have a view called “Employee onboarding stage 1” and only show the columns for onboarding tasks 1-10, then another view called “Employee onboarding stage 2” and only show the columns for onboarding tasks 11-20, etc.
With this approach I lose all of the metadata associated with each onboarding task such as who is responsible for the task, what is the SLA for the task, which type of employee contract this task is applicable for, and so on. I could put all of this metadata in each fields column description, but something about that just doesn’t feel right.

I’m just trying to get some advice on how to think about the tradeoff and which approach would be better even if there’s no perfect solution. Or maybe there is a much better way to do this and is neither of the two approaches listed above.

Any help will be much appreciated.

Thank you in advance.

[WilliamPorter · Accepted solution]

I did not quite think through the entire project myself, but I can offer a tentative answer your basic question. I am 83% confident that you’ll want to put employees and onboarding tasks into separate tables. That “83%” is a rough guess. Let’s say 83% ± 3. :slightly_smiling_face:

Avoid super-wide tables!

I can tell you with a higher degree of confidence that, in the data-modeling game, if you’re starting to think of creating a table that’s go 60 different columns/fields that are all basically connected items, you’re almost certainly doing it wrong. When the number of related columns is small — say, Phone1, Phone2, Phone3 — the case can be argued either way. But for one thing, think of the UI issues that having 60 columns each representing a different task represents!

Multiselect? Maybe, but probably not

I suspect that a single column of type Multi-Select might make more sense, if you were determined to keep things in a single table. I haven’t tried this. I’m not sure that it’s POSSIBLE to create a Multi-Select field with 60 options. But just thinking about it, I can see a problem with this approach: Those selection descriptions are going to have to be pretty darned succinct.

Many-to-many!

When I’ve done things like this in the past, I’ve generally used a many-to-many design: PERSONS on the left (so to speak), FIXEDOPTIONS over on the right, and in the middle, the join table “PERSON-OPTIONS”.

The advantage of the many-to-many approach is that it gives you a lot of flexibility. For example, for each task linked to a single employee, you can have a field marked “Progress” or “DateDue” or whatever. Say you put in a date due field. You can then look at a calendar in Airtable and see what specific tasks are due today, tomorrow etc. and for what new employees.

The problem with many-to-many relationships in Airtable is that they raise awkward UI issues. Not impossible, just awkward, because if you stay in Airtable (don’t use a front-end builder, don’t write scripts to help you here) a lot of the linking will have to be done manually.

What it looks like in real life

But actually in this scenario this might not be too hard. Sounds like you know that every new employee needs to have these 60 things done. So you can, for each new employee, do the following (by hand):

1. Copy the value in the primary field for all 60 records in the OnboardingTasks table
2. Copy the value of the primary field for the new employee (probably employee’s name)
3. Go into the join table (EmployeeOnboarding?) and go to a view that isn’t filtered so you can create new records by pasting.
4. Paste the sixty values from the OnboardingTasks table to create 60 new join records
5. Select the EmployeeName (or whatever the link column is called) of all 60 records and paste the employee name.

(The steps above assume that you’re using Windows Clipboard history or some similar utility. Otherwise just add a step in there: copy, paste, copy something else, go back and paste that.)

You’ve now created your sixty onboarding task records for new employee Elmer Fudd. Once this is done, you

• go to the join table, filter for Elmer, and see exactly how things are proceeding with his onboarding
• make notes when appropriate about each onboarding task that are specific to Elmer
• mark the date each task is concluded
• use date due fields to plan upcoming onboarding efforts
• assign tasks to different people

and so on. And over in Employees, you can look at Elmer Fudd’s record and have a rollup fields that shows completed tasks, tasks due, etc.

Rollups vs Views

Of course rollup fields are not very nice to look at and it is not easy in Airtable to jump from Elmer’s record in Employees over to a view in EmployeeOnboard (the join table) to view just his 60 tasks. This is where front-end builders like Stacker or Softr can help. Maybe can be done with JavaScript but you have to be careful not to run afoul of the problem that in Airtable record sets are global. So (depending on how many new employees the company is onboarding in an average week or month) you might just keep half a dozen or so views over in EmployeeOnboarding that are filtered and named for specific employees. When Elmer’s onboarding is completed, you can delete or repurpose the view for him and assign it to Daisy Duck or some other newer employee.

[Burner]

Response to WP_Rucksack

Thank you so much for that super-detailed response. That was really helpful. I apologize for not responding sooner. Turns out I had my email notifications off for the Community forum, so I’ve now fixed that.

I agree with everything you said. I knew intuitively that keeping Employees and Onboarding tasks in separate tables was the way to go and you’ve just confirmed that and given me a clear picture of how to set up the junction table.

So I now have tables “Employees”, “Onboarding tasks”, and “Employee Onboarding” – the third one being the junction table.

Also, although obvious, I didn’t realize until your response (so thank you once again), that the junction table would also allow me to add all the meta info like due date, completion date, last follow up, or whatever to the junction table ‘Employee Onboarding’ for each record (d’uh).

You’re right in that adding records to the junction table makes it awkward and I like your approach of copying the primary key from employees to the junction table and then copying the 60 onboarding tasks against each of that employee’s rows. I’m thinking of taking this one step further and just setting up an automation for this. So as soon as a new employee is added to the Employee table, (1) Make a linked reference entry in the junction table, and then (2) against that newly created linked reference in the junction table, enter the 60 onboarding tasks, one for each of those new employee linked reference rows. This may be tricky to do using a single Airtable automation and may not even be possible given that it involves looping and iterating – there’s no way I’m creating 60 native Airtable automations – I don’t even think that’s possible. Alternatively, I could set up an loop/iterator using Integromat to generate new employee-onboarding tasks entries for each new employee added.

With Respect to Rollups/Views thank you for the great insight. Very fair point and now I’m giving some serious thought to slapping on a front-end like Stacker/Softr (I’ll have to learn these first), but seems like it eases out the awkward UI scenarios and can also circumvent certain Airtable limitations.

Thanks a bunch for the deep insights. Really appreciated it.

[WilliamPorter]

Glad you found it helpful.

Many-to-many relationships are often presented as something advanced and scary. Not really. The classic example for many-to-many is the school enrollment app. You start with Students and Teachers (in two different tables). Then you have a join/junction table typically called Enrollments.

The thing to keep in mind is that any data that pertains, say, to a student regardless of whether that student is enrolled in any classes at all would be stored in fields in the Students table. On the other hand, each record in the Enrollments table represents one student taking a class from one teacher.

So far, it’s actually pretty straightforward — and this might be the situation with your use case.

Of course in real life, the school enrollment example often is NOT this simple. Typically the database will need to have tables for several other fields:

• Classes: This would solve the problem not uncommon in universities where 1 student might be enrolled in two quite different classes from the same teacher; it also allows for Prof Jones and Prof Smith both to be teaching classes of French 1101.
• Grades
• Buildings and Rooms

And so on. So you end up having many-to-many-to-many-etc relationships. Lots of fun!