Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    8

    Help with DB design: Staff, Departments tables

    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!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •