I have a database with the following tables to store data:
tblBudgetCodes, tblemployees, tblJobTitles, tblPrograms, tblSchedules, tblSchools
What I need to do now is create a junction table (and possibly another data table) to bring all the data together and this is where I need a bit of guidance. I will explain how the data goes together and I will attach my current tables here for review as well.
I have 600+ Employees and 94 Schools. Each school has it's own schedule and it's own programs (with their own sponsors) and a different number of each job title for those programs. I have an employee named "vacant" so that I can use "vacant" as a placeholder for vacant contract jobs at the schools. Employees who are contracted at one school for normal daily work can also work programs at other schools for after school programs. for example:
Jane Doe may be assigned to work as a Manager for Montgomery Elementary (contract program) but then work as Lead Cook for Howell Middle School for the ASM program and work Concessions over the weekend for football games and then work as assistant cook for Franklin High School during the Summer program (which has a different pay rate since it's not during the regular school year)
The programs are ASCP Snack, ASM, Concessions, Contract, SubManager, Summer and Tutoring. The budget codes, schools feed into (relationships) tblPrograms and then that in turn feeds into tblSchedules.
My question is twofold. Do I need to make another data table to link the schools with all of the various job titles they can have or can I just do that in the bigger junction assignment table? And then what is the best way to structure the Assignment table so that down the line when I want to retreive the data I can see which employee is assigned to what program and which school for which job title, pay rate and budget code. But I also need to know their contract school assignment no matter what other school program they may be working as well.
tblBudgetCodes, tblemployees, tblJobTitles, tblPrograms, tblSchedules, tblSchools
What I need to do now is create a junction table (and possibly another data table) to bring all the data together and this is where I need a bit of guidance. I will explain how the data goes together and I will attach my current tables here for review as well.
I have 600+ Employees and 94 Schools. Each school has it's own schedule and it's own programs (with their own sponsors) and a different number of each job title for those programs. I have an employee named "vacant" so that I can use "vacant" as a placeholder for vacant contract jobs at the schools. Employees who are contracted at one school for normal daily work can also work programs at other schools for after school programs. for example:
Jane Doe may be assigned to work as a Manager for Montgomery Elementary (contract program) but then work as Lead Cook for Howell Middle School for the ASM program and work Concessions over the weekend for football games and then work as assistant cook for Franklin High School during the Summer program (which has a different pay rate since it's not during the regular school year)
The programs are ASCP Snack, ASM, Concessions, Contract, SubManager, Summer and Tutoring. The budget codes, schools feed into (relationships) tblPrograms and then that in turn feeds into tblSchedules.
My question is twofold. Do I need to make another data table to link the schools with all of the various job titles they can have or can I just do that in the bigger junction assignment table? And then what is the best way to structure the Assignment table so that down the line when I want to retreive the data I can see which employee is assigned to what program and which school for which job title, pay rate and budget code. But I also need to know their contract school assignment no matter what other school program they may be working as well.
Last edited:
