Hi all!

I am writing with what could be a very simple question. I just want feedback before I go ahead with it.

I joined this school about 3 weeks ago. I'm an English teacher, but I also help with the "IT" department here at the school. They're using MS Access, and it is my first time working with it. The database was mostly finished when I got here, and I've only done minor changes to it (rename tables/columns, create relationships).

The question I have is that they have two tables: Staff (with all the staff info), and Departments, with the department info.

The Departments table has the following columns:

ID
Department (name)
Department Head (this is linked to Staff table)
Staff in Department

The "Staff in Department" column is the problem. It is a multiple-value field, in which the Staff IDs are specified, separated by commas.

In the past, what I was taught was that you would create an additional table to create the relationship between Staff and Departments, and it would make it easy to query for using Join. On the other hand, I don't know if the current design is a valid design for MS Access (or any other DBMS). I've already made a similar change to the database (to relate Students to Courses, in which a similar design was in place) and I don't want to continue bugging the main IT guy unless I have a valid suggestion.

Anyway, what do you think? Am I on the right track? Or should I just work with what's there? Like I said, I've never worked with MS Access before this job, so maybe it's a valid implementation.

I hope that made sense :/
Thanks in advance!

If a staff member can only belong to one department at a time, then the department ID should be stored with the staff record. If a staff member can belong to multiple departments simultaneously, create a many-to-many table to link the two.