Solved Best ERD Design Approach (1 Viewer)

Sarah.M

Member
Local time
Today, 09:34
Joined
Oct 28, 2021
Messages
303
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 :cautious: , please help me with the suggestion to make it good design 🙏

Sample attached
 

Attachments

  • 51.accdb
    724 KB · Views: 132
Last edited:

mike60smart

Registered User.
Local time
Today, 07:34
Joined
Aug 6, 2017
Messages
1,237
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

Banishment Pending
Local time
Today, 01:34
Joined
May 11, 2011
Messages
11,023
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

Member
Local time
Today, 09:34
Joined
Oct 28, 2021
Messages
303
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
 

Attachments

  • 56.accdb
    836 KB · Views: 111
  • ReportPhoneBook.pdf
    93.6 KB · Views: 117

plog

Banishment Pending
Local time
Today, 01:34
Joined
May 11, 2011
Messages
11,023
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

Member
Local time
Today, 09:34
Joined
Oct 28, 2021
Messages
303
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 🙏
 

Attachments

  • 59.accdb
    2.7 MB · Views: 119

mike60smart

Registered User.
Local time
Today, 07:34
Joined
Aug 6, 2017
Messages
1,237
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

Super Moderator
Staff member
Local time
Today, 02:34
Joined
Jan 23, 2006
Messages
14,443
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

Banishment Pending
Local time
Today, 01:34
Joined
May 11, 2011
Messages
11,023
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

Super Moderator
Staff member
Local time
Today, 02:34
Joined
Jan 23, 2006
Messages
14,443
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

Member
Local time
Today, 09:34
Joined
Oct 28, 2021
Messages
303
Thanks all for your feedback
I will fixed it and post it
 

Sarah.M

Member
Local time
Today, 09:34
Joined
Oct 28, 2021
Messages
303
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?
1651775749828.png
 

Attachments

  • 60.accdb
    2.7 MB · Views: 120

plog

Banishment Pending
Local time
Today, 01:34
Joined
May 11, 2011
Messages
11,023
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

Member
Local time
Today, 09:34
Joined
Oct 28, 2021
Messages
303
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
 

SHANEMAC51

Active member
Local time
Today, 09:34
Joined
Jan 28, 2022
Messages
294
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
 

Attachments

  • Screenshot_3.png
    Screenshot_3.png
    15 KB · Views: 109
  • Screenshot_2.png
    Screenshot_2.png
    47.8 KB · Views: 109

Sarah.M

Member
Local time
Today, 09:34
Joined
Oct 28, 2021
Messages
303
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?
 

Users who are viewing this thread

Top Bottom