Database design and normalization of tables

saagar

New member
Local time
Today, 16:25
Joined
Dec 18, 2015
Messages
4
Hi.
Although I am not new to Access, I feel I need help in resolving the following issue.

I have created a contacts database. Following tables are in a one-to-many relationship in the following hierarchy (primary key from each table with its counterpart foreign key in the linked table):

1. TblOrganizationType (Eg., Education; Health; Government etc.)
2. TblOrganizations (Eg., Xyz University; Ministry of Health etc.)
3. TblInstitutions (Eg., College of Medicine; College of Science, Research Council etc.)
4. TblDepartments (Eg., Department of Psychology; Department of Research etc.)
5. TblSections (Eg., Medication education Section; Communication Section; Housing Section; Transport Section etc.)
6. TblEmployeeContactInfo (Eg., Title, FirstName, MiddleName, FamilyName, Position, Tel. Ext., Mobile1, Mobile2, Email1, Email2)

A. Although TblSections is linked with TblDepartments, all Departments may not have any Sections at all, and therefore, the Tbl5Sections may not have data in it; however, the Tbl5Sections is linked with Tbl6EmployeeContactInfo. Here is the main issue: when I run a query based on the above tables, the departments that have no sections under them, are not being retrieved.

B. Just a suggestion: Please advise if a JunctionTable (Many-to-Many relation table) will help as a link table between: TblDepartments and TblSections, so that my query output or report output can show all related data of an employee from a Department having a Section or no Section under it.
C. Will appreciate any other suggestions useful in improving the whole design and structure of the database.

I hope I have presented the scenario clearly.

Thank you in anticipation.
 
A. Although TblSections is linked with TblDepartments, all Departments may not have any Sections at all, and therefore, the Tbl5Sections may not have data in it; however, the Tbl5Sections is linked with Tbl6EmployeeContactInfo. Here is the main issue: when I run a query based on the above tables, the departments that have no sections under them, are not being retrieved.
you have a "normal" 1-to-many join. The department can be linked to multiple sections, but there may not be any sections at all.

A better solution than the one you are using is to join the departments/sections with a different join, that selects all departments irrespective of whether they have a section or not. (I forget the technical name - inner join?)

if you right-click the join link, you will see options like "select only where both match", and "select all from departments etc". The second one is the one you want.

This is far preferable to having to consider alternative links for certain departments.

----
I think the employee contact info being linked to sections is a bit of a red-herring.

However, it does imply that if an employee is assigned to a section, and a department has no sections, then the department has no employees?

If this is not the case, then your model is at fault, and you need to reconsider the data analysis, I think.
 
Thanks for the quick response. I think I will try this option and report the outcome soon.
Regards.
Sagar
 
The other method to consider is that for any case where a department has no sections, create an automatic "section-of-the-whole" record that would be deleted if you suddenly found that the department had sections. However, another approach that aligns more with Dave's solution is that you can build a junction table in parallel with the employee to department table to show that the employee is in a given section, and then when you build the employee list, include the junction table and section table with appropriate NZ functions to smooth out the cases where sections don't apply. I THINK that is slightly different than what Dave suggested, but the model is convoluted enough that I can't be sure.
 
A better solution than the one you are using is to join the departments/sections with a different join, that selects all departments irrespective of whether they have a section or not. (I forget the technical name - inner join?)

Outer Join

Full name would be Left Outer Join or Right Outer Join, depending on which end shows all records.

Inner Join is the 1:1 one.
 
Thank you for your valuable input. I will use this option; I think this should work. :cool:
 

Users who are viewing this thread

Back
Top Bottom