Ms Access Relationship (1 Viewer)

eugenelim0410

New member
Local time
Today, 11:16
Joined
Aug 13, 2018
Messages
7
I have built several tables for my database which includes the employee, client, engagement, team and task table.

I found that my tables contain a many to many relationship so I created a table to link the both tables.

Please advise if the relationship have any problem.

1 client will have many engagements.
1 engagement will have 1 team each time.
1 team consist of many employees.
1 employee can be allocate into different teams.

Please check the attachment for the relationship.
 

Attachments

  • relationship.PNG
    relationship.PNG
    25.8 KB · Views: 93

Minty

AWF VIP
Local time
Today, 18:16
Joined
Jul 26, 2013
Messages
10,355
You should remove the employeeID from the Team table, as that is handled by the Junction table.
I'm unsure of why the Task Table is linked to the Engagement table ?
Should the task not be allocated to a team and then employees for the task follow that team?

Generally you shouldn't have circular relationships.
 

eugenelim0410

New member
Local time
Today, 11:16
Joined
Aug 13, 2018
Messages
7
I have remove the employeeID from team table.

For the task part, what I want is after I allocate employee into a team and then assigned tasks for the team member. So should I linked the task table with team-employee table? Please advice.

Thank you so much!
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:16
Joined
Sep 21, 2011
Messages
14,046
Speaking from experience, I'd also alter any 'ID' fields to a better more meaningful name, as you have done with the other tables..
If you combine tables in a query with same named fields, Access asks you which is which.?
 

plog

Banishment Pending
Local time
Today, 13:16
Joined
May 11, 2011
Messages
11,611
2 big issues, then some nits to picks:

1. Ciruclar path in your relationships. There should only be one way to travel between tables in your relationships, you have a circuit which is incorrect. You can go clockwise or counter clockwise to get from Team to Task Allocation, that's wrong, one of those relationships need to go.

2. Storing redundant data. [Client Name] should not be in Engagement. You've already got it in Client and its linked via [Client ID] so its connected, no need to store in multiple places.

Only use alphanumeric characters in names. That means no spaces, no slashes, no dashes, etc. It just makes coding and querying easier down the line. For example, that means [Team - Employee] should probably become [TeamEmployee] and [Employee ID] should become [EmployeeID].

What's the attachment? Generally you store files in a directory and put the path to them in the database. So, Photo7.jpg gets saved on the network and "P:\\DatabaseImages\Photo7.jpg" goes into a text field in the database.

Ungenericize field names. Not only would I prefix all those ID fields with what ID they are (EmployeeID, TaskAllocationID), but all the other field names that are not unique across the database (ClientCity, ClientSTate, EmployeeCity, EmployeeState).
 

jdraw

Super Moderator
Staff member
Local time
Today, 14:16
Joined
Jan 23, 2006
Messages
15,364
In addition to the advice given so far, I'd like to see a 4-5 line description of the "business process(es)" in plain, simple English. Then, based on your description, a list of business rules that you have started.
1 client will have many engagements.
1 engagement will have 1 team each time.
1 team consist of many employees.
1 employee can be allocate into different teams
.

Please tell us a little about "engagement".
Can an Employee be on multiple teams at the same time?
It sees you are interested in which team member is associated with an engagement.

Perhaps you could describe a "day in the life of the business" to put the pieces into context.
 

eugenelim0410

New member
Local time
Today, 11:16
Joined
Aug 13, 2018
Messages
7
The system that I wish to create is a staff allocation. For every client, a team of employee will sent out for providing service which is call engagement. An employee will not be assigned into different team under same time.
 

Mark_

Longboard on the internet
Local time
Today, 11:16
Joined
Sep 12, 2017
Messages
2,111
If a given employee cannot be on more than one team at a time, then you will want to put two dates into your "TeamEmployee" table; DtJoined, DtDeparted. These will hold when an employee is assigned to and leaves a team. These may or may not be the same for all team members, but if you prepare for them to be different NOW, you won't be working out how to handle when one member is hospitalized and needs to be replaced.

DO NOT put a flag into the Employee table to check if they are "Available". Instead do a query to return any records that have a Join date without a Depart date. This will give you the most up to date result.
 

Users who are viewing this thread

Top Bottom