Table Relationship Question

[MikeC]

The below tables have been added to an MSDE 2000 database. Two of the
tables, tblClassSchedule and tblRegistration, are children of tblEmployee.
tblRegistration also has a second relationship where it is a child of
tblClassSchedule.

I am having difficulty defining tblRegistration's *second* relationship in
MSDE 2000 because tblClassSchedule and tblRegistration are already related
to the same parent table. If I were building a Jet database, I would solve
the problem by adding another independent instance of tblEmployee to the
table relationships. I am unable to find a similar option in MSDE. I'm
assuming MSDE provides an alternative method to define the table's second
relationship. Can anyone tell me how to define tblRegistration's
relationship with tblClassSchedule in MSDE 2000?

1) tblEmployee

Fields:

EmployeeID (PK)
LastName
FirstName
etc.

2) tblClassSchedule

Fields:

SchedID (PK)
EmployeeID (FK) <-Here, employee is the instructor.
Description
etc.

3) tblRegistration

Fields:

RegID (PK)
SchedID (FK)
EmployeeID (FK) <-Here, employee is the student.
RegDate
etc.

 

[Sylvain Lafontaine]

First, having a relationship defined is not mandatory for using a join
beetween two tables.

Second, I'm not sure with what tool or dialog window or view or DDL
statement that you are trying to add a second relationship to your table.
By itself, this shouldn't make any difference when adding a relationship to
a table is there is already an associated relationship with another table.

S. L.

 

[MikeC]

There are a couple places in my original post where I said "MSDE" instead of
"ADP". This mistake will confuse anyone who reads the post.

In short, I want my ADP/MSDE application to enforce referential integrity.
Relationships need to be somehow defined before this can happen. The
built-in "Create Database Diagram" facility provides options for enforcing
referential integrity, so that's what I'm trying to do.

So far, ADP will not let me define tblRegistration's relationship with
tblClassSchedule because tblClassSchedule and tblRegistration are already
related to the same parent table. However, the relationships (described
below) are perfectly legitimate. How do I enforce referential integrity for
these relationships?

 

[Sylvain Lafontaine]

I doesn't see any particular problem adding many relationships to a table
using the Diagram facility of ADP. I have just made a test by adding a few
relationships from different tables to a single table and everything looks
to work perfectly fine.

Could you describe exactly the steps that you are doing or any error message
that you might see?

S. L.

 

[MikeC]

Did you try this kind of relationship? To reproduce the error, you might
need to add the relationships in a-b-c order. I get the error when I try to
add relationship c.

Parent
a/ \b
Child1--c--Child2

I'm leaving town shortly and will return on Friday evening. I'll post more
info when I return.