New Educational Database - Structure & Normalization

[alpinegroove]

I am trying to redesign an Access database of instructor and course information.

Currently, the database I inherited has only one table with about one hundred fields, roughly in the following categories:

Course title, course number, type, unit number
Instructor contact and biographical information
Teaching Assistant contact and biographical information (more than one per course)
Reader Assistant contact and biographical information (more than one per course)
Course schedule, day/time/
Course location, room
Discussion section schedule, day/time
Discussion section location
Final exam day/time
Final exam location
Instructor compensation
TA compensation (more than one TA per course)
Reader compensation (more than one Reader per course)
Total compensation
Course cap
A list of fields with document names that indicate when a form was sent and received.

My feeling is that all of this information should not be in one table, but I am not sure why. Should it be divided into different tables? Why?

If so, what tables should I create? The obvious ones seem to be:
Course Information
Instructor Information
TA Information
Reader Information
Compensation - should this be separate from Instructor/TA/Reader tables?

How do I deal with the list of documents and procedures that contain sent and received dates?

Any help would be appreciated!
Thanks.

[TheShabz]

You are correct that all this data should not be in one table.

You're on the right track in terms of normalizing the database. I'm not sure how the document section would be laid out as we would need more info on what they are and how they relate to the rest of the entities, but here's what I would do for the rest.

tables:
tblCourse
tblEmployee
tblEmployeeType

tblEmployee, and i used employee as a generic term so call them whatever you will, will contain information on Instructors, TAs, AND Readers. This is where compensation would go. It will also contain a field called EmployeeTypeID, which will act as a foreign key to tblEmployeeType. This table contains 2 fields. One as an ID field, and the other containing the name (instructor, Reader, TA).

The course table is a bit tricky as you might need more tables depending on what the sessions are and the room systems are. If there is more info on each, then you would need more tables and join them.

Overall, I'd recommend reading up on "database normalization" and "relational database management system". There is more information for free on the internet than you'll have time to read. you cant really go wrong. Read first, work later.

[alpinegroove]

Thank you. Can you please explain why it is necessary to have multiple tables? That is my sense too. I am just not able to explain why.

As for the documents, it is a way for us to keep track of documents sent to and received from instructors. For example when a contract/syllabus/reimbursement form was mailed and when it was returned.

As for the multiple tables, how should they be linked?

Thanks again.

[TheShabz]

Can you please explain why it is necessary to have multiple tables? That is my sense too. I am just not able to explain why.

"Database Normalization"

As for the multiple tables, how should they be linked?

"Relational Database Management System"

As for the documents, it is a way for us to keep track of documents sent to and received from instructors. For example when a contract/syllabus/reimbursement form was mailed and when it was returned.

This will require 2 more tables. 1 table for document type and another to act as a junction table to support a many to many relationship between course and document type.

Reading up on the two terms I mentioned will answer all of your questions. They are both explained far better than I can even begin to try.

[alpinegroove]

Thank you.