Database Table Referencing Several Records

I'm looking for some guidance as to how to set up a database I am creating. I think the best way to ask the question is to describe the specific scenario:

I have a database that will hold information about student-employees at a school.

I have one table called "Semesters" that 开发者_如何学JAVAcontains information about a specific semester (e.g. "Fall/Spring/Summer", year, etc.).

I have another table called "Employees" that contains information about a specific student-employee (e.g. name, jobTitle, startDate, endDate). I would also like to have a reference to the "Semesters" table indicating which semester(s) the employee was hired. So, for example, one column in the "Employees" table would tell me that this specific employee was working during the "Fall2010, Spring2011, and Fall2011" semesters.

The problem is that I don't know how to reference several records in one field.

I am sure that this is a fairly common scenario, I'm just not sure how to go about it. Answers that reference MS Access or SqlServer would be excellent.

Thanks.

This sounds like a many to many relationship. In such a case you would likely utilize a junction table.

Employees (empID, name)

1 bob
2 moe
3 george

Semesters (semID, name)

1 fall 2010
2 spring 2010

EmploymentHistory (empID & semID)

1 1 (bob was employed fall 2010)
1 2 (bob was employed spring 2010)
2 2 (moe was employed fall 2010)

You can accomplish this with several foreign keys to the Semester table (not Semesters) from the Employee table (not Employees).

If you want to keep track of the semesters that an Employee has worked, I'd normalize that into a WorkHistory table that's one-to-many with Employee and many-to-many with Semester.