Database Design - Referential Integrity

[Joy]

Hi all -

I am modifying a program for the firefighters school.

In the original program, the home fire department was typed in by the
secretary.
This led to the usual different spellings for the same fire dept. and also
was time consuming. It is easier and more accurate to use a dropdown box.

The fire department is used in 3 different ways:
1. There is a home fire department for each student. It is found in the
STUDENT table.

2. Each student may be assigned to more than one fire dept. Let's say his
home department is Fire Dept #4 but he also fights for Depts #5, 6, 7
We need to know this, so if we print the members of Dept. 7 we know which
courses have been taken by that department.
These departments are found in the STUDENT/DEPARTMENT table.

3. There is a fire department which pays for his course - we need to show
this department on his graduation certificate. For example, let's say Fire
Dept. #5 paid for members of Fire Dept # 4 to take the course. When the
student registers for a course, he gives the name of the department that is
paying for the course.
The department certificate name is found in the STUDENT/COURSE table.

It is a good idea to create one DEPARTMENT table, then wherever it is used,
it is used as a drop down box.

Problem is student/course table is connected to student table is connected
to student/department table.
What I am thinking, is to have duplicate department tables, so they can be
connected to all the other tables, and ensure referential integrity.

Is this a good way to go? The department table doesn't change much, but if
you need to add a new dept. you have to add it to all 3 dept. tables, so
that the name will appear in every dropdown box. So this is an overhead,
and a source trouble, if someone forgets to do it.

Are there any other alternatives beside duplicating the department table
that would work? And what if I don't ensure referential integrity?

Thanks!

Joy

 

[John S. Ford, MD]

First, to deal with your overall datastructure, I'd break it down as
follows:

If I understand this correctly, your tblStudents needs to have one field for
the "Funding" Department ie. the fire department that funds the student's
course. In addition, you need to have a tblFireDepartments that list all
the departments.

Lastly, you will need a tblLinkStudentFireDepartment with two fields:
StudentIDNum and DepartmentIDNum. This linking table will facilitate the
one-to-many relationship between a student and all his or her associated
departments.

The trick is to realize that you are really dealing with two separate
fields: the funding department, and the associated departments (which I'd
expect would INCLUDE the funding department and which could be several).
You will probably want to establish referential integrity between these
three tables: tblStudents, tblFireDepartments and
tblLinkStudentFireDepartment.

As far as creating a form that allows your users to enter a student's fire
department as a dropdown list, I'd just create the entry field as a combobox
and use the Access combobox wizard to link it to the departments table.

Does this help?

John

 

[Larry Daugherty]

Hi Joy,

No, it's not a good idea to duplicate the Department table. It's against
the relational rule that says data will be stored exactly once in the
database. The reason for the rule is that when data is stored multiple
times it will eventually get out of synch - especially if it depends on
human behavior to keep it in synch.

Your idea of the Department being in a lookup table is excellent - stay with
that paradigm all the way through. Of course, if you were managing
department kinds of things you'd want to have a form or two based on
tblDepartment.

If I'm aligned with your thinking overall, there should be no fixed linkage
between tblStudentCourse and tblStudentDepartment. Whether tblCourse serves
as a lookup table or a main or a child table depends on your point of view
as you're managing enrollments. One of the fields in tblStudentCourse would
be PayingDepartment as each course might be paid by a different department.
One of the fields in tblStudentDepartment should be Boolean Yes/No that
answers the question: Home Department? Further, there must be some code
such that the bit can only be set from the keyboard, not cleared - assuming
there must be a Home department - and the only way to change the setting is
to choose another department as home.

HTH

 

[Joy]

Hi -

Thanks for your earlier help with the Firefighters School E-R diagram. I
would like to summarize what relationships I have already, then ask you for
your opinion.

Summary: There are students and fire departments. One student can belong
to > 1 fire department. So this is represented by 3 tables - student,
student/department and department. Each table uses a form to access the
table.

Also, the students enroll in courses. One student can take > 1 course. So
this adds on 2 more tables, student/course and course details. (Actually,
for course, there is a course name table and course details table. One
course can be offered many times). But for now, the student is connected to
the course details by the student/course table. I have forms for te course
details, student/course and student tables.

Now the tricky part. When the student enrolls in a course, a sponsoring
Fire Department pays for his course. It may not be his home fire
department, it may not be one of the fire departments he is associated with.
We need to show the Sponsoring Department on the certificate. So when a
student enrolls, I would like to use the Department table as a lookup table
for the SponsoringDepartment field in the student / course record. Using a
lookup table saves time, increases accuracy. It would just be a dropdown
combo box to select the value for the field.

With me, so far?

So is it possible to use the department table twice in the E-R diagram?
Once it connects with student to show affiliated departments. Once it is
used in the student/course table to show which department paid for the
course.

One thing I thought of was to duplicate the department table, and maintain
them separately with 2 different forms. Department table could have DeptID,
DeptName, DeptCity, DeptProvince.

SponsoringDepartment table could have SponDeptID and SponDeptName only. But
you would have to maintain 2 different tables.

Would that work? Is that good or bad database design?

I really want to get these tables straight, so I don't have to do this
again )))

Thanks for helping. If you need more info, please ask me.

Joy

 

[John Vinson]

So is it possible to use the department table twice in the E-R diagram?

Absolutely. If you add it a second time, Access will alias it by
adding a _1 to the name; but that's precisely what you need to do in
this case.

One thing I thought of was to duplicate the department table, and maintain
them separately with 2 different forms. Department table could have DeptID,
DeptName, DeptCity, DeptProvince.

SponsoringDepartment table could have SponDeptID and SponDeptName only. But
you would have to maintain 2 different tables.

Would that work? Is that good or bad database design?

It could be made to work, with difficulty, but it's BAD design since
you would be storing exactly the same information redundantly in two
tables. It's the same department, regardless of whether it's joined to
the SponDeptID field or to the DepartmentID field. Just use the one
table and join it to both fields - since you're joining to separate
tables you won't even need another instance of the table on the form;
just a line from the DepartmentID field to the Departments table, and
another line from SponDeptID to the same table.

John W. Vinson[MVP]

 

[Joy]

Thank you, John, for your advice. To go one step further, I would like your
opinion once again.

I will adopt the 'one department table' approach. I will place the
department table in the Relationships diagram twice. It will be connected
to the student/department table and the student/course table, as in my
previous post.

I would also like to connect it to the student table. The reason is, that
when the secretary enrolls the student, there is a field for home fire
department. Currently, she types it in by hand, but it would be so much
easier to have a drop down box, so the 'home fire department' field value
would be selected from the department table. (If it is not found on the
department table, there would be a form to add it.)

So I would really like to use the department table in 3 places...connect to
the student/ department, student/course and the student table.

Hmm...according to you, this would work. I just put the same department
table in the Relationships diagram 3 times altogether. And Access can
handle it okay, no sweat.

COOL! Just let me know if I am wrong, but I think this is the way to do it.

Thanks! (You taught me something!!)

Joy

 

[John Vinson]

Thank you, John, for your advice. To go one step further, I would like your
opinion once again.

I will adopt the 'one department table' approach. I will place the
department table in the Relationships diagram twice. It will be connected
to the student/department table and the student/course table, as in my
previous post.

I would also like to connect it to the student table. The reason is, that
when the secretary enrolls the student, there is a field for home fire
department. Currently, she types it in by hand, but it would be so much
easier to have a drop down box, so the 'home fire department' field value
would be selected from the department table. (If it is not found on the
department table, there would be a form to add it.)

So I would really like to use the department table in 3 places...connect to
the student/ department, student/course and the student table.

Hmm...according to you, this would work. I just put the same department
table in the Relationships diagram 3 times altogether. And Access can
handle it okay, no sweat.

That will work, and it's handy if that makes your diagram more
readable. However it is NOT necessary to add the table to the diagram
more than once; since you're joining to three different tables, you
can - if you wish - put the table into the diagram once and just drag
three lines to the three different tables. The relationships will be
created correctly in either case.

Do be sure to check the "Enforce Relational Integrity" checkbox in the
join properties - otherwise the diagram is just a pretty picture which
does nothing to the structure of your data! In this case I would
suggest that you do *not* check the Cascade Deletes or Cascade Updates
options; you don't want to delete a whole bunch of students just
because a fire department (say) gets consolidated into another one.


John W. Vinson[MVP]