I am searching for some guidance regarding how to setup a database I'm creating. I believe the easiest method to request now you ask , to explain the particular scenario:
I've got a database which will hold details about student-employees in a school.
I've one table known as "Semesters" that consists of details about a particular semester (e.g. "Fall/Spring/Summer time", year, etc.).
I've another table known as "Employees" that consists of details about a particular student-worker (e.g. title, jobTitle, startDate, endDate). I'd also enjoy having a mention of "Semesters" table showing which semester(s) the worker was hired. So, for instance, one column within the "Employees" table would let me know this specific worker was working throughout the "Fall2010, Spring2011, and Fall2011" semesters.
However , I'm not sure how you can reference several records in a single area.
I am certain that this can be a not unheard of scenario, I am simply not sure how to pull off it. Solutions that reference MS Access or SqlServer could be excellent.
This seems like a many to a lot of relationship. In this situation you'd likely use a junction table.
Employees (empID, title)
1 bob 2 moe 3 george
Semesters (semID, title)
1 fall 2010 2 spring 2010
EmploymentHistory (empID &lifier semID)
1 1 (bob was employed fall 2010) 1 2 (bob was employed spring 2010) 2 2 (moe was employed fall 2010)
You are able to make this happen with several foreign secrets towards the Semester table (not Semesters) in the Worker table (not Employees).
If you wish to keep an eye on the semesters that the Worker has labored, I'd normalize that right into a WorkHistory table that's one-to-many with Worker and several-to-many with Semester.