Database Design

[Joy]

I have 3 tables that are related to each other, namely: student/ course,
student and student/department.

In the 3 tables I need to select the value for a department, using a drop
down box.
I also have a department table (deptid, deptname).

Is it okay to duplicate the department table so there will be a copy for
each child table that uses it and still maintain referential integrity? It
would create a bit of an overhead having 3 copies of the same table, in case
someone updated the table and forgot to duplicate it.

(If I am not clear, I can explain using examples.) What do you do in this
situation?

Thanks for your help!

Joy

 

[Guest]

Why would you want to duplicate the department table?

It reads like what you want is:

tblStudents
StudentID (PK, Autonumber)
Forename (Text)
Surname (Text)
DOB (Date/Time)
etc........

tblDepartments
DepartmentID (PK, Autonumber)
Department (Text)

tblCourses
CourseID (PK, Autonumber)
Course (Text)

Not knowing more about what you are wanting to achieve I can only guess that
Department is based on the Course level - i.e. History, Philosophy, etc.
appear as part of the Social Sciences department.

Therefore the Courses table changes to become:

tblCourses
CourseID (PK, Autonumber)
Course (Text)
DepartmentID (FK, to tblDepartments - DepartmentID)

The only other step to consider, based on what you have given, is if the
students can only study one course or if they can study multiple courses.

If they can study one course then the following would apply:

1) The Students table would have a CourseID foreign key added to it.

tblStudents
StudentID (PK, Autonumber)
Forename (Text)
Surname (Text)
DOB (Date/Time)
CourseID (FK, Number, to tblCourses - CourseID)
etc........

However, if one student can study multiple courses then this means that you
have a many-to-many relationship and need to simulate this entity with a new
'junction' table.

tblStudentsToCourse
StudentID (FK, Number, to tblStudents - StudentID)
CourseID (FK, Number, to tblCourses - CourseID)

both of these fields, while being foreign keys, should be selected as a
composite primary key (more than one field).

And there you have it. One department table - not three - and a normalised
structure.

You don't need to put the DepartmentID into the Students table as querying
across the relationships will bring the correct department in.