Normalization of DB Tables

MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 01:04
Joined
May 21, 2018
Messages
9,493
I am posting this for the OP as a fresh thread and continuation of thread

Help with subform datasheet view looks good(the way I designed); but the form view in my main form looks like a datasheet.

I'm designing an employee database. I'm fairly new to Access; even though I created a very good database approximately 20 yrs ago. I've found working within a database is not the same as designing one. Now that my old one is no longer needed due to new job; I have created another one. I have 8...
www.access-programmers.co.uk
That thread was extremely long and did not even get to addressing the normalization issue. I am posting here so those interested in helping do not have to wade through multiple posts. Most of the tables need to be fixed so I welcome others to help. I have attached the OPs original db, the start of a clean db, and some data in an Excel sheet. This is an employee training database which IMO can be very complex to get the relations correct. In my proposed design there is even a many to many linked to another many to many. So not trivial.
In the cleanTraining DB I will enter tables once they are cleaned up and then establish relationships. Here are the table details so far.

Code: 
T_Employees
   Employee_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong  Indexed
   First_Name   dbText  Required
   Last_Name   dbText  Required
   Home_Phone   dbText
   Cell_Number   dbText
   Driver_License_Number   dbText
   License_Expires   dbDate
   Driver_License   dbText
   Photo   dbLongBinary
   Resigned_ or_Termed   dbText
   Staff_Info_Comments   dbMemo

T_PIR_Info
   Employee_Number   dbLong
   Last_Name   dbText
   First_Name   dbText
   Past_HS_ or_ EHS_ parent   dbBoolean
   Hire_Date   dbDate
   Terminated   dbDate
   Program   dbText
   Bilingual   dbBoolean
   Second_Language   dbText
   Volunteer_Only   dbBoolean
   Comments   dbMemo

T_TrainingCourses
   TrainingCourseID   dbLong  PrimaryKey  Indexed
   Training_Title   dbText
   Sponsoring_Organization   dbText
   Type_Training   dbText

T_Credits_OCFS
   CreditID   dbLong  PrimaryKey  Indexed
   CreditName   dbText

T_TrainingCourses_Credits
   Training_Credit_ID   dbLong  PrimaryKey  Indexed
   TrainingID_FK   dbLong  Indexed
   CreditID_FK   dbLong  Indexed
   Credit_Hours   dbDouble

T_Employees_TrainingCourses
   ETCC_ID   dbLong  PrimaryKey  Indexed
   EmployeeID_FK   dbLong
   TrainingCourseID_FK   dbLong
   CourseDate   dbDate

T_Lookups
   LookupID   dbLong  PrimaryKey  Indexed
   LookupValue   dbText
   LookupCategory   dbText
   LookupSortOrder   dbLong

T_Programs
   EE_Program_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong
   Employee_ID_FK   dbLong  ForiegnKey  Indexed
   Hire_Date   dbDate
   Job_Status   dbText
   Job_Title   dbText
   Supervisor   dbText
   12_or_10_mos   dbText
   Site_Detail   dbText
   Last_Day_Worked   dbDate
   Resigned_ or_Termed   dbText
   Staff_Info_Comments   dbMemo

T_Sites
   SiteID   dbText  PrimaryKey  Indexed  Required
   SiteLongName   dbText
For the tables I understand I created these relations.


In addition an Excel training log was provided. This shows training course and for each course the employee gets credits in 9 areas for a set amount of time.
Credits.jpg


This leads to the relationship
Training.jpg
 

Attachments

Part two

So Employees taking many courses. So there is a many to many in T_Employees_TrainingCourses. Each course can have credits in many credit areas leading to a second Many to Many. These relations are hard to visualize until you put it together with subforms and queries.

For @sribblett
1. I am assuming that these courses are given many times, but that the sponsor, type, of training and credit hours remain the same. I did notice that sometimes this was not correct, but those look like errors to me. Here is an example showing the same course with different credit hours and sponsor.
2019 Summer Institute - Oh, The Things You Will KnowCOIClassroom7.27.199.505.750.752.250.75
2019 Summer Institute - Oh, The Things You Will KnowQualityStarsNYClassroom7.27.199.506.500.7500.5000.500.500.750

If in fact the course is given at another time and the data does change then that would be considered a separate record.

2. What is PIR info and what is the relation to an employee? It looks like those fields are unique to an employee and should go into the employee table.
3. Why is there a program field in the PIR table?
4. What is the relation (in plain language not db language) between an employee an a program. To me. it looks like an employee works for the company but has many jobs during that time. So a program is the job that an employee is doing. So an employee can have many programs, but other employees cannot relate to the same program. A 1 to many from employee to programs. If that is the case then a program would have a foreign key to an employee. However there are hired and last dates. Are these different than what goes in the employee table? Does the employee have a hired date to the company and a separate hired date for a programs?
5. You have a table Required training and 1stAid Mat. Can these be combined? Could this be one table like
T_Required_Certifications
CourseID_FK ' Do these courses exist in the T_TrainingCourses or are these not counted in that training. At first glance they look like they are.
'CourseName if these do not exist in the T_TrainingCourses
DateTaken
DateExpires
CertificationRecieved.

That is enough to get started. For others wanting to help. Here is the original DB table structure. As you can see there is a lot of complex normalization needed.
Code: 
T 1st Aid-CPR & MAT
   Employee_1st Aid_CPR   dbLong  PrimaryKey  Indexed
   Employee_ID_FK   dbLong  ForiegnKey  Indexed
   Employee_Number   dbLong
   First_Name   dbText
   Last_Name   dbText
   1stAid_CPR_AED_Complete   dbDate
   1stAid_CPR_AED_Class_Type   dbText
   Never_Taken   dbBoolean
   1stAid_CPR_AED_Expires   dbDate
   1stAid_CPR_AED_Card   dbBoolean
   MAT_Complete   dbDate
   MAT_Expires   dbDate
   MAT_Certificate   dbBoolean
   Comments   dbMemo

T Course of Study
   Employee_Case_Study_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong
   Employee_ID_FK   dbLong  ForiegnKey  Indexed
   First_Name   dbText
   Last_Name   dbText
   Course_of_Study   dbText
   College_or_University   dbText
   Major   dbText
   Anticipated_Graduation_Date   dbDate
   Degree_to_be_Earned   dbText
   Teaching_Certificate_to_be_Obtained   dbText
   List_of_Classes_1   dbText
   List_of_Classes_2   dbText
   List_of_Classes_3   dbText
   List_of_Classes_4   dbText
   List_of_Classes_5   dbText
   List_of_Classes_6   dbText
   Additional_Class_Must_Complete_to_Earn_Degree_1   dbText
   Additional_Class_Must_Complete_to_Earn_Degree_2   dbText
   Additional_Class_Must_Complete_to_Earn_Degree_3   dbText
   Additional_Class_Must_Complete_to_Earn_Degree_4   dbText
   Additional_Class_Must_Complete_to_Earn_Degree_5   dbText
   Additional_Class_Must_Complete_to_Earn_Degree_6   dbText
   Additional_Class_Must_Complete_to_Earn_Degree_7   dbText
   Additional_Class_Must_Complete_to_Earn_Degree_8   dbText
   Course_of_Study_Comments   dbMemo
   Completed_Course_with_Degree   dbDate

T Education Credentials
   Employee_Ed_Credentials_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong
   Employee_ID_FK   dbLong  ForiegnKey  Indexed
   Highest_Academic_Degree_on_File   dbText
   CDA   dbText
   CDA_Expiration_Date   dbDate
   CDA_Language   dbText
   NYS_Teaching_Certificate   dbText
   NYS_Teaching_Certificate_Type   dbText
   NYS_Teaching_Expiration_Date   dbDate
   Additonal_NYS_Teaching_Certificate   dbText
   Additional_NYS_Teacher_Certificate_Type   dbText
   Additional_NYS_Teaching_Expiration_Date   dbDate
   Addition Education_1_Certificate   dbText
   Expiration_Date_for_Added_Certificate_1   dbDate
   Addition Education_2_Certificate   dbText
   Expiration_Date_for_Added_Certificate_2   dbDate
   Addition Education_3_Certificate   dbText
   Expiration_Date_for_Added_Certificate_3   dbDate
   Education_Comments   dbMemo
   Enrolled_in_CDA_Class   dbDate
   Completed_CDA_Preparation_Class   dbBoolean
   CDA_Completion_Deadline   dbDate
   CDA_Comments   dbMemo

T Employee Main
   Employee_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong  Indexed
   First_Name   dbText
   Last_Name   dbText
   Home_Phone   dbText
   Cell_Number   dbText
   Driver_License_Number   dbText
   License_Expires   dbDate
   Photo   dbLongBinary
   Driver_License   dbText
   Program_Information_PK   dbLong
   Resigned_ or_Termed   dbText
   Staff_Info_Comments   dbMemo

T Employee Trainings
   Trainings_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong
   First_Name   dbText
   Last_Name   dbText
   Name_of_Training   dbText
   Sponsoring_Organization   dbText
   Type_of_Training   dbText
   Training_Date   dbDate
   Total_Training_Hours   dbSingle
   1_Principals_of_Early_Childhood_Development   dbSingle
   2_Nutition_and_Health_Needs_of_Infants_and_Children   dbSingle
   3_Child_Day_Care_Development   dbSingle
   4_Safety_and_Security   dbSingle
   5_Business_Record_Maintenance_and_Management   dbSingle
   6_Child_Abuse_and_Maltreatment   dbSingle
   7_Statutes_and_Regulations_Pertaining_to_Child_Day_Care   dbSingle
   8_Statutes_and_Regulations_Pertaining_to_Child_Abuse   dbSingle
   9_Shaken_Baby_Syndrome   dbSingle
   Hours_Not_Counted_Toward_OCFS   dbSingle
   Certificate   dbBoolean

T JunctionTable
   Employee_Training_ID   dbLong  PrimaryKey  Indexed
   Employee_ID_PK   dbLong
   Employee_Number_PK   dbLong
   Training_Course_ID_PK   dbLong  ForiegnKey  Indexed
   Date_Taken   dbDate
   Employee_1st_Aid_CPR_PK   dbLong
   Employee_Required_Training_ID_PK   dbLong
   Employee_Case_Study_ID_PK   dbLong
   Employee_Waivered_ID_PK   dbLong
   Employee_Ed_Credentials_ID_PK   dbLong

T Program Information
   EE_Program_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong
   Employee_ID_FK   dbLong  ForiegnKey  Indexed
   Hire_Date   dbDate
   Job_Status   dbText
   Job_Title   dbText
   Supervisor   dbText
   12_or_10_mos   dbText
   Site_Detail   dbText
   Last_Day_Worked   dbDate
   Resigned_ or_Termed   dbText
   Staff_Info_Comments   dbMemo

T Required Trainings
   Employee_Required_Training_ID   dbLong  PrimaryKey  Indexed
   Employee_ID_FK   dbLong  ForiegnKey  Indexed
   Employee_Number   dbLong  Indexed
   Complete_Foundations_of_Health_and_Safety   dbDate
   Foundations_in_Health_and_Safety_Certificate   dbBoolean
   Complete_Mandated_Reporter   dbDate
   Mandate_Reporter_Certificate   dbBoolean
   Complete_Obesity_Prevention   dbDate
   Obesity_Prevention_Certificate   dbBoolean
   Complete_SIDS   dbDate
   SIDS_Certificate   dbBoolean
   Complete_AHT   dbDate
   AHT_Certificate   dbBoolean
   Required_Trainings_Comments   dbMemo

T Staff Waivered Sites
   Employee_Waivered_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong
   Employee_ID_FK   dbLong  ForiegnKey  Indexed
   Home_Site   dbText
   Brocton   dbBoolean
   Bush   dbBoolean
   Connections_North   dbBoolean
   Early_Care_Community_Center   dbBoolean
   GOW   dbBoolean
   Holy_Family   dbBoolean
   Laughlin_Community_Action_Center   dbBoolean
   Gowanda_Board_Approved_Staff   dbDate
 
MajP said:
I am posting this for the OP as a fresh thread and continuation of thread

Help with subform datasheet view looks good(the way I designed); but the form view in my main form looks like a datasheet.

I'm designing an employee database. I'm fairly new to Access; even though I created a very good database approximately 20 yrs ago. I've found working within a database is not the same as designing one. Now that my old one is no longer needed due to new job; I have created another one. I have 8...
www.access-programmers.co.uk
That thread was extremely long and did not even get to addressing the normalization issue. I am posting here so those interested in helping do not have to wade through multiple posts. Most of the tables need to be fixed so I welcome others to help. I have attached the OPs original db, the start of a clean db, and some data in an Excel sheet. This is an employee training database which IMO can be very complex to get the relations correct. In my proposed design there is even a many to many linked to another many to many. So not trivial.
In the cleanTraining DB I will enter tables once they are cleaned up and then establish relationships. Here are the table details so far.

Code: 
T_Employees
   Employee_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong  Indexed
   First_Name   dbText  Required
   Last_Name   dbText  Required
   Home_Phone   dbText
   Cell_Number   dbText
   Driver_License_Number   dbText
   License_Expires   dbDate
   Driver_License   dbText
   Photo   dbLongBinary
   Resigned_ or_Termed   dbText
   Staff_Info_Comments   dbMemo

T_PIR_Info
   Employee_Number   dbLong
   Last_Name   dbText
   First_Name   dbText
   Past_HS_ or_ EHS_ parent   dbBoolean
   Hire_Date   dbDate
   Terminated   dbDate
   Program   dbText
   Bilingual   dbBoolean
   Second_Language   dbText
   Volunteer_Only   dbBoolean
   Comments   dbMemo

T_TrainingCourses
   TrainingCourseID   dbLong  PrimaryKey  Indexed
   Training_Title   dbText
   Sponsoring_Organization   dbText
   Type_Training   dbText

T_Credits_OCFS
   CreditID   dbLong  PrimaryKey  Indexed
   CreditName   dbText

T_TrainingCourses_Credits
   Training_Credit_ID   dbLong  PrimaryKey  Indexed
   TrainingID_FK   dbLong  Indexed
   CreditID_FK   dbLong  Indexed
   Credit_Hours   dbDouble

T_Employees_TrainingCourses
   ETCC_ID   dbLong  PrimaryKey  Indexed
   EmployeeID_FK   dbLong
   TrainingCourseID_FK   dbLong
   CourseDate   dbDate

T_Lookups
   LookupID   dbLong  PrimaryKey  Indexed
   LookupValue   dbText
   LookupCategory   dbText
   LookupSortOrder   dbLong

T_Programs
   EE_Program_ID   dbLong  PrimaryKey  Indexed
   Employee_Number   dbLong
   Employee_ID_FK   dbLong  ForiegnKey  Indexed
   Hire_Date   dbDate
   Job_Status   dbText
   Job_Title   dbText
   Supervisor   dbText
   12_or_10_mos   dbText
   Site_Detail   dbText
   Last_Day_Worked   dbDate
   Resigned_ or_Termed   dbText
   Staff_Info_Comments   dbMemo

T_Sites
   SiteID   dbText  PrimaryKey  Indexed  Required
   SiteLongName   dbText
For the tables I understand I created these relations.


In addition an Excel training log was provided. This shows training course and for each course the employee gets credits in 9 areas for a set amount of time.
View attachment 84659

This leads to the relationship
View attachment 84658
Click to expand...
Please look below what I've done so far in my DB. Hope all is good so far. Please let me know. Once I get all table set up in good working order. I will add all ,y employee information. to start creating forms and queries
 

Attachments

Can you repost the DB. I get an error downloading this and an "unrecognized DB error".
 
Sorry I am still having issues opening this. It might be something on my end. If you download it, can you open it?
 
I was thinking I could add the information of the waivered table to the Program Table. Waivered Table isn't that big and I can get the information I need in a query.
 
Here is my solution. As I said you tables have more issues then simply relationships of keys. I have added tables and deleted tables. I have turned fields into tables. If you have any questions on this design I can help, I cannot help with the design you posted. There are too many issues to fix it.
TrainingDB.jpg
 

Attachments

I wnat to thank you
MajP said:
Here is my solution. As I said you tables have more issues then simply relationships of keys. I have added tables and deleted tables. I have turned fields into tables. If you have any questions on this design I can help, I cannot help with the design you posted. There are too many issues to fix it.View attachment 84876
MajP said:
Here is my solution. As I said you tables have more issues then simply relationships of keys. I have added tables and deleted tables. I have turned fields into tables. If you have any questions on this design I can help, I cannot help with the design you posted. There are too many issues to fix it.View attachment 84876
Click to expand...
I want to thank yo so much for all your help!!! As I stated awhile back, I created a transportation DB which was a huge one also about 20 years ago. I had only 1 table and all information was in that 1 table. It was a great DB except different people told me it would have been better if I didn't put everything into one table. this is why I created the DB the way I did. It has been a nightmare ever since. So TY again. Did you link my training log to the data base, where I still add to my excel training log or do I add to the actual DB? Once I add my data I will be able to get the information that I need for each staff reagarding their trainings and training hrs.
Click to expand...
 
If this design is correct and makes sense for how you do business, it will not be an simple thing to make forms to do the data input. You have several many to many and several one to many. You will have lots of subforms. You will need a lot of pull down choices in combo boxes to make data entry easy. I will try to demo the training courses because that may be the most complicated.

I left in the first aid MAT table, but to me that could possibly just be another training course. Not sure if they require their own table. I also did away with required training because I thought that is just a training course with the.
 
MajP said:
If this design is correct and makes sense for how you do business, it will not be an simple thing to make forms to do the data input. You have several many to many and several one to many. You will have lots of subforms. You will need a lot of pull down choices in combo boxes to make data entry easy. I will try to demo the training courses because that may be the most complicated.

I left in the first aid MAT table, but to me that could possibly just be another training course. Not sure if they require their own table. I also did away with required training because I thought that is just a training course with the.
Click to expand...
Want I could do is add the required trainings to the 1st Aid & MAT Table. Or once the table is set up will I be able to run a query or report showing that all required trainings and 1st Aid/MAT trainings are complete. Where am I tracking the trainings from staff - still on the excel form like before - is it linked to the database or will I have to change the way I enter trainings. I was going to do a pivot table from excel and to add just total hours and total hours in each OCFS 9 topics. Add that pivot table to the database. I'm glad I didn't work on that much longer before getting with you and your help.
 
Let me demo the training form. If I am understanding things correctly it will be a much easier way to track training than Excel.
 
I'm all about easier and more proficient, TY!!!! Will I need to type the entire training log into Access or will I be able to cut and paste?
 
I need to type the entire training log into Access or will I be able to cut and paste?
Click to expand...
You would likely do neither. You would do an import from queries. If you look at T_Courses_Credits you can see I did a large import already.
 
I did an import from the Excel sheet. It I may not be 100% based on some issues in the data.

There is a form to enter/edit a course and assign it the related credits. That is done once per course.
Then on the main form you choose from the list of course to assign it to an employee on the date given. All the credit hours are related "automatically". You can see this on the second tab. This is read only. You never have to log credit hours. If a course is required you can track which employees have taken all the required courses. No seperate table is needed. IMO this is a correct design, but it is not trivial to make the user interface for this. The credit hour tab requires a crosstab query and defined columns.
 

Attachments

I'm in a pretty good place now with school startup and licencing for a couple sites are complete. Just waiting for confirmation that everything went well. so now I can concentrate on getting this database get up and going. I will need to update all trainings from my most up to date excel spread sheet. All out staff has tons of trainings in the last couple of weeks. It will now slow down so I can get all my data in the revised db once it's in good working order. Tell me what I need to do next and I will do whatever it takes.
 
You must log in or register to reply here.

Similar threads

M
Create Backend Tables Using DAO
Replies
16
Views
3,021
gemma-the-husky
gemma-the-husky
P
Print a list of used tables and populated fields
Replies
2
Views
1,712
Pierre Beauchamp
P
ajetrumpet
  • Locked
Get Table Information
Replies
0
Views
6,283
ajetrumpet
ajetrumpet
B
  • Locked
Code to upsize individual tables to SQL Server
Replies
0
Views
4,800
ByteMyzer
B
T
Create Table and AutoNumber
Replies
1
Views
1,407
TanisAgain
T

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom