Correct Relationship for self-referenced tables?


W

WDSnews

In my Relationships, I have instances where, in one case, tables such as the
People table need to reference itself, or in another case, a related table
needs to reference two different records in the same table. For example,
should I reference a single instance of the People table in the
Relationships Manager, or should I add a second instance, People_1, and
reference it too? I've been using the latter without any noticable
problems, but I'd like to hear your opinion. The same situation exists when
building queries.

i.e. In the People table, children need to reference their biological
mother in the same table. Should I let People.Mother point to People_1.ID,
or back to the same table at People.ID?

i.e. In the Teacher/Student relationship table there are fields called
Teacher.ID and Student.ID that both refer to the People table. Should both
foreign keys point to a single People table or should one of them reference
People_1.ID?

thanks for your opinion about the Relationships Manager and about query
relationships.
 
Ad

Advertisements

A

Arvin Meyer [MVP]

WDSnews said:
In my Relationships, I have instances where, in one case, tables such as
the People table need to reference itself, or in another case, a related
table needs to reference two different records in the same table. For
example, should I reference a single instance of the People table in the
Relationships Manager, or should I add a second instance, People_1, and
reference it too? I've been using the latter without any noticable
problems, but I'd like to hear your opinion. The same situation exists
when building queries.

i.e. In the People table, children need to reference their biological
mother in the same table. Should I let People.Mother point to
People_1.ID, or back to the same table at People.ID?

People_1 is the same rable, just an alias for it. That relationship should
be fine.
i.e. In the Teacher/Student relationship table there are fields called
Teacher.ID and Student.ID that both refer to the People table. Should
both foreign keys point to a single People table or should one of them
reference People_1.ID?

I'd point them to the People table, but do it, using a separate alias. The
only reason is that it's easier to see and understand the relationship.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top