Foreign Keys and Table Occurrence Structures

[geraldh]

I am trying to build a database to track my college students' enrolments, evaluations, attendance, etc.

I hope I will clear in what I am trying to ask:

I have the following tables (there are others, but these will suffice for my question).

Sessions

Course Offerings (with foreign key field id_Sessions)

Sections ( with foreign key field id_Offerings)

Enrolments ( with foreign key field id_Sections)

I started building the structure as such:

Session ==> Offerings ==> Sections ==> Enrolments. 

Please see the attached PDF.

But I was wondering if I should have a foreign key in the Enrolments table for each of the parent tables?

In other words:

Enrolments (with foreign keys id_Session, id_Offerings,  id_Sections, )

And then have Enrolments connect directly to a table occurrence of Sessions, Offerings, Sections, rather than cascading through the four tables?

I know there is some redundancy here, but I found that this makes reporting on Enrolments in certain situations either necessary or easier.

Is this good (common?) practice? Is it good practice to have the "lowest" rung have the foreign keys of all it parent (and grandparent) id keys , and then connect directly with just one table occurrence away, rather than trying to access info through multiple table occurrences? 

I do hope I am somewhat clear in my question. Thank you.

Foreign Keys.pdf

Edited April 17, 2015 by geraldh

[Mark Scott]

Is this good (common?) practice? Is it good practice to have the "lowest" rung have the foreign keys of all it parent (and grandparent) id keys , and then connect directly with just one table occurrence away, rather than trying to access info through multiple table occurrences? 

​Hi Gerald,

Your gut feeling that that might not be a good idea is correct. The redundancy you noted makes it hard to maintain referential integrity if anything changes in the intermediate tables.  Thus each child table should have a foreign key only for its direct parent (or parents if it's a join table), as shown in your relationship graph.  (There are probably exceptions, in order to support certain UI approaches perhaps, but those would be rare and would require diligence to keeping everything in sync.)

hth,

Mark

[geraldh]

Hello Mark,

Thank you for your response.

Since my posting, I gave this more thought, and if I thought about the parent tables as value lists/conditional value lists (much like Category, SubCategory etc), this approach could be following proven structural strategies.

Besides the increase in the number of records in the tables Session, Offerings, and Sections, their data should remain stable, and there are few fields in this table--therefore integrity would not be at great risk.

One of the reasons I wanted to try this approach was to take full advantage of the GetSummary function and be able to report on Enrolment data when summarized by either Session, Offerings, and Section. Unless I am mistaken, the breakField must reside in the same table as the summaryField, and if the breakField (foreign key) is more than 1 table occurrences away, it is impossible to do this? Thus the reason to have each of the foreign keys reside in this Enrolment table, and set up the TO to be only 1 table occurrence away from the Enrolment TO.

I've attached an updated table occurrence graph PDF to show the basic setup. Haven't tested yet, but I imagine I can now take advantage of the GetSummary function and have more breakField options to get summaries according to different "values" (i.e. the breakField, i.e. foreign keys for Session, Offerings, Sections).

Any feedback on potential dangers would be greatly appreciated.

Thank you.

Foreign Keys and Structure.pdf

[comment]

One of the reasons I wanted to try this approach was to take full advantage of the GetSummary function and be able to report on Enrolment data when summarized by either Session, Offerings, and Section. Unless I am mistaken, the breakField must reside in the same table as the summaryField, and if the breakField (foreign key) is more than 1 table occurrences away, it is impossible to do this? Thus the reason to have each of the foreign keys reside in this Enrolment table, and set up the TO to be only 1 table occurrence away from the Enrolment TO.

​The solution to this problem is to have the GrandParentID and AncestorID as calculation fields in the Child table. They do not need to serve as foreign keys, and you do not need to add any TOs of the ancestor tables to the graph for this purpose.

[geraldh]

Thank you comment,

I see the merit of this. I thought of this, but wanted to avoid adding calculation fields. But I think it is better having calculation fields instead of complicating the structure of the database.

I shall go ahead and try this.