Solved Best ERD Design Approach

[Sarah.M]

Hi, Plz note, My VBA is blocked by IT for security reason (Cybersecurity).
I can use only Expression builder, Macros, Queries, Tables, Forms and Reports only.
------------------------------------------------
I have tblOrganization, tblManagement and tblDepartment
for each of them has Positions for example Organization Manager, Management Manager and Department Manager

1. I work as secretary I write letters, send emails, phones, organize appointments and meetings. The most important thing is to prepare reports. This database will help me to generate
-position book for Organization, management, and department,
-Phone and e-mail book. (Sample Attached PDF)
-Assigned book for users start date and end date (each user has 1 Main Job, but sometimes has more that one job as Assignment Job Temporarily with Start and End Date, (tblPosition is going to be Main job, tblAssignment for temporarily) if it is wrong theory plz correct me.

2. Also it is going to help me to make cascading combobox filter each others

But I'm not sure how good my design is , please help me with the suggestion to make it good design

Sample attached

 

[theDBguy]

Sample attached

I don't see any attachment...

 

[Sarah.M]

I don't see any attachment...

Hi, Ohhh, I am sorry
Refresh the page
Plz find the attached file

 

[Sarah.M]

Any suggestions plz

 

[mike60smart]

I usually think along the lines of:-

A Person belongs to a Department and within the Department they have a specific Role.

Each Person can of course change Departments and change Roles.

 

[plog]

Without knowing anything about your organization or data I see 2 big things:

1. Spiderweb of relationships. There should only be 1 way to trace a path between 2 tables. I'm pretty sure I can trace at least 6 between tblManagement and tblDepartment. That's wrong. I don't know what the correct relationship is, but you've got to many of them. These goes for other tables as well but some of those get cleaned up with #2 below.

2. Duplicate tables/storing data in table names. tblManagPosition, tblDepPosition, tblOrgPosition have virtually the same structure. These 3 tables should have their data in one table, That might mean adding a field or two to differentiate them and store data only some of the records need, but you should not have 3 similiarly structured tables.

Again, that's without knowing anything about your organization nor the purpose of this database. If you can make the changes I suggest above, post back an updated database with some sample data and include 1 paragraph about what your organization does and 1 paragraph about what this database will help you accomplish. I can make further suggestions and ask more intelligent questions to make sure you are headed in the right direction.

 

[Sarah.M]

Again, that's without knowing anything about your organization nor the purpose of this database. If you can make the changes I suggest above, post back an updated database with some sample data and include 1 paragraph about what your organization does and 1 paragraph about what this database will help you accomplish. I can make further suggestions and ask more intelligent questions to make sure you are headed in the right direction.

Hi @plog , thanks for replying, I make new ERD Design based on your feedback, plz check if it is good

1. I work as secretary I write letters, send emails, phones, organize appointments and meetings. The most important thing is to prepare reports. This database will help me to generate
-position book for Organization, management, and department,
-Phone and e-mail book. (Sample Attached PDF)
-Assigned book for users start date and end date (each user has 1 Main Job, but sometimes has more that one job as Assignment Job Temporarily with Start and End Date, (tblPostion is going to be Main job, tblAssginedment for temporarily) if it is wrong theory plz correct me.

2. Also it is going to help me to make cascading combobox filter each others

If you have more questions, suggestions plz let me know

Sample attached

 

[plog]

Again, spiderweb of relationships. There should only be one way to travel from one table to another, everything is intermingled. You have way to many relationships.

Let's take 1 example I can get my head around. You have sections (tblSection), Departments (tblDepartment) and Positions (tblPosition).. All 3 are directly related to another, that can't be right. My guess is tblPosition should not be directly related to tblDepartment. If you want to find out what department a position belongs to you get their indirectly by going through tblSection.

You've done this alot. I don't think posiitions should be directly related to managers--instead starting at positions you go through sections to departments and then to the managers table. That's how you relationships are to work.

Some quetions:

1. Why aren't departments directly linked to organizations?

2. Isn't management a type of position?

3. What's the difference between an assignment and a position?

 

[Sarah.M]

Let's take 1 example I can get my head around. You have sections (tblSection), Departments (tblDepartment) and Positions (tblPosition).. All 3 are directly related to another, that can't be right. My guess is tblPosition should not be directly related to tblDepartment. If you want to find out what department a position belongs to you get their indirectly by going through tblSection.

I made some changes based in your feedback, plz look at DB 59

You've done this alot. I don't think positions should be directly related to managers--instead starting at positions you go through sections to departments and then to the managers table. That's how you relationships are to work.

Some questions:

1. Why aren't departments directly linked to organizations?

Because Dep is part of Org

2. Isn't management a type of position?

No

3. What's the difference between an assignment and a position?

Assignment: Assigning the employee side tasks on behalf of someone else, for example, when an employee goes on vacation, another employee will temporarily replace him
Position: It is the real position that he is
I use 1-M for Cascading Combo Boxes look at this Form [frmCascadingComboBoxes] look at 59 DB in the attachment

Please help

 

[mike60smart]

So the Organisation that you work for as a Secretary (Position).

Starting from the Top you have an Organisation which is made up of a Number of Sections.

Each Section is made up of a Number of Departments.

Is the above correct ?

Or is it The Organisation is made up of a Number of Departments.

Each Department is made up of a number of Sections?

 

[jdraw]

You might consider the following as a mock-up/guide:

We are the ABC Organization
ABC Org has a CEO
We are structured into Departments and Sections
each Department has 1 to 3 Sections
each Department has a VP/Manager
VP/Managers report to the CEO
Each Section has a Director/Manager
Each Director reports to the VP of the corresponding Department
Each Section has 4 to 8 Positions
Each Position has a Job Title
Employees occupy Positions
VP/Directors and CEO are Management positions

Also, who is the User in your 59.accdb??

 

[plog]

First, forms have absolutely nothing to do with what we are working on. The are at best 2 steps down the road. You build a solid foundation (tables), then you make sure you can get the data out like you want (reports/queries) then you build a way to edit/add data in it (forms).

The latest structure you posted is looking really good. Does it work though? You know your data better than us, does it look like it represents the data in your organization accurately?

With that said, 2 issues still:

1. The relationship among assignments, positions and users is still too much. You are giving out assignments at 2 different levels--to people and to positions. Why not make it just positions? If someone goes on vacation, etc. then you simply update tblAssignment--you add an AssignEndDate to the current assignment holder and add a new record for the position who gets it while they are out. That way you can delete the direct connection between tblUser and tblAssignment.

2. tblDepKind has a 'Section' kind. That seems extremely odd given you have a whole table for sections. Under what circumstances could something be a section in the department table but not be in the section table? Confusing.

 

[jdraw]

I agree with plog. My post #11 was intended to help you define tables and relationships.
From my experience, many Personnel systems accommodate "temp positions or acting positions". That is, employees "officially" occupying positionX, and yet "acting as occupying positionY" for some period of time.

I'm not suggesting that in your case, just a comment re positions and employees.
Management/reporting relationships are often implemented as self joins.

I don't think management is a separate table. I'm not sure who the User is in your setup

 

[Sarah.M]

Thanks all for your feedback
I will fixed it and post it

 

[Sarah.M]

1. The relationship among assignments, positions and users is still too much. You are giving out assignments at 2 different levels--to people and to positions. Why not make it just positions? If someone goes on vacation, etc. then you simply update tblAssignment--you add an AssignEndDate to the current assignment holder and add a new record for the position who gets it while they are out. That way you can delete the direct connection between tblUser and tblAssignment.
I update the design plz find attached DB 60
2. tblDepKind has a 'Section' kind. That seems extremely odd given you have a whole table for sections. Under what circumstances could something be a section in the department table but not be in the section table? Confusing. I delete tblDepKind

How will I classify the General Manager she is not part of section?

 

[plog]

How many general managers do you have? What level does she belong at? Department? Organization?

I also see that you have 'Manager' listed in that table. That means you probably don't need a seperate Manager table.

 

[Sarah.M]

How many general managers do you have? What level does she belong at? Department? Organization?

I also see that you have 'Manager' listed in that table. That means you probably don't need a seperate Manager table.

1 GM
-Division Head
--Manager
---Secretary
----Clerk

 

[Sarah.M]

Any suggestions

 

[SHANEMAC51]

1 GM
-Division Head
--Manager
---Secretary
----Clerk

based on your task -i would make 2 main tables
1-tree = organization+ department+ department+ sector+ employees
2- events linked to any node of the tree

 

[Sarah.M]

based on your task -i would make 2 main tables
1-tree = organization+ department+ department+ sector+ employees
2- events linked to any node of the tree

Thanks for replying
Can you please upload your DB so that I can understand your explanation?