Link Employee Code to Comment.. and... Comment to other Comments

[LLD]

Hi,

I already have a table with basic details of my employees (Name, Employee Code (unique key), Date of Birth, Date when they joined us etc.).

In order to make diarised entries about them, I have set up another table called Comments (with its own unique key).

After saving a comment, I want to be able to - 

1) link that comment to one or more employees (this is applicable when the same comment applies to more than one person, e.g. when it involves a team)

2) link that comment to one more more related comments within the same table.

@Steve @Suvrutt_Gurjar pls could you suggest how I may do this?

[WillowMobileSys · Accepted solution]

There are a couple of ways.  I'll describe both

Your requested approach

In your Comments table, since it could be for a single Employee or for a Team, you'll want to have two columns - one for each.  This is so if you start from the Comment table you can easily specify which it is for.

In your Employee table, you will want to adjust the List of Comments to retrieve rows where Employee matches the Employee row OR Team matches the Team column of the Employee row - include this criteria OR([Employee] = [_THISROW].[Employee], [Team] = [_THISROW].[Team])

When you save a Comment, it will become attached to that Employee automatically based on whether the Employe or Team matches.

NOW, note some side effects.    With this model, should you provide the ability to delete an Employee or a Team, the Comments table will need to be managed separately - i.e. there will need to be a process the also deletes any related Employee/Team comments or otherwise they become orphaned rows.

Another Approach

SO, another approach is to have an Employee Comments table and a Team Comments table.  Next set each of these as a Child of their respective parent tables.  You can setup your Employee to see their Personal Comments and then their Team Comments as separate lists - which could be an advantage depending on your preference.  But the biggest plus is that when you delete an Employee, the related comments are also automatically deleted.  When you delete a Team, all of those related comments are deleted automatically.

I hope this helps.  Please ask if there are any questions.

[WillowMobileSys · Accepted solution]

There are a couple of ways.  I'll describe both

Your requested approach

In your Comments table, since it could be for a single Employee or for a Team, you'll want to have two columns - one for each.  This is so if you start from the Comment table you can easily specify which it is for.

In your Employee table, you will want to adjust the List of Comments to retrieve rows where Employee matches the Employee row OR Team matches the Team column of the Employee row - include this criteria OR([Employee] = [_THISROW].[Employee], [Team] = [_THISROW].[Team])

When you save a Comment, it will become attached to that Employee automatically based on whether the Employe or Team matches.

NOW, note some side effects.    With this model, should you provide the ability to delete an Employee or a Team, the Comments table will need to be managed separately - i.e. there will need to be a process the also deletes any related Employee/Team comments or otherwise they become orphaned rows.

Another Approach

SO, another approach is to have an Employee Comments table and a Team Comments table.  Next set each of these as a Child of their respective parent tables.  You can setup your Employee to see their Personal Comments and then their Team Comments as separate lists - which could be an advantage depending on your preference.  But the biggest plus is that when you delete an Employee, the related comments are also automatically deleted.  When you delete a Team, all of those related comments are deleted automatically.

I hope this helps.  Please ask if there are any questions.

[LLD · Accepted solution]

Hello! I am sorry about the late reply... thank you for the two detailed options, but I am afraid that both won't work, since the "teams" are dynamic, i.e. employee could be deployed to any "team", depending on production priorities for the day.

• Each employee has a unique Employee Code.
• Each comment has a unique ID.
• I need to associate (link) one or more employees to a comment, and,
• I need to associate (link) comments with each other. 

When I look at the related employee codes of a comment, I will know who all that comment relates to.

When I look at the related comment IDs of a comment, I will have a historical narrative

I have kinda fixed it for now.. not happy with my solution 😐