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:
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.
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.