3

Imagine the situation. There are three tables,

Student table[PK Student ID] 
Enrollment Table [PK EnrollID, FK Student ID] 

Fees Paid [PK Fee ID, FK EnrollID, FK StudentID]



Edit: Fee_Paid Table references the PK of both ENROLL TABLE and STUDENT Table.    
ENROLL Table References the PK of STUDENT Table.

Given this situation which I have, I was informed that student ID should not be a FK in the Fees table and it causes "Integrity Issues".

While It can be done that way (linking fees paid to student ID and not needing student id in Fees_Paid Table).

Are there any negative ramifications to be using the foreign keys the way that I am cause I honestly seen none. The integrity rule I'm aware of is not letting students be deleted when their ID exist in Fees table but any further clarification will be appreciated.

  • 1
    Please make it clear what the foreign keys are: what list of columns in what table references what list of columns in what table, for each foreign key. – philipxy Dec 25 '15 at 4:11
  • @philipxy Hi, I have edited my question but again FEE TABLE references PK in STUDENT and ENROLL table as FKS. and ENROLL TABLE only references the PK of STUDENT TABLE as a FK – Niana Dec 25 '15 at 13:23
2

Your tables mean something like

Student /* [StudentID] is a student AND ... */
Enrollment /* enrollment [EnrollID] is of student [StudentID] AND ... */
Payment /* payment [PaymentID] is enrollment [EnrollID]
    AND enrollment [EnrollID] is of student [StudentID]
    AND ... */

You must decide exactly what these mean and what situations can arise. Together those determines the constraints (PKs, FKs etc). You must give the meanings to users.

In the case of payments, purchases, orders, prices etc finalized transactions are usually recorded with the data from that time, even if a customer ceases to be a customer or prices changed. Does this matter to you? Is there is a FK Payment (StudentID) to Enrollment (StudentID)? I gave Payment a meaning that would lead to that FK.

The integrity problem is that your design allows Enrollment and Payment to disagree about EnrollID and StudentID even when the DBMS is enforcing the constraints. That can never arise, and the database would describe an impossible situation, so it's an error, but the DBMS doesn't know that, so it can't reject it.

That problem is gone with FK Payment (EnrollID, StudentID) to Enrollment (EnrollID, StudentID). Then the DBMS catches those errors.

But then of you update one of those tables then you have to update the other. That is called an update anomaly. (This one doesn't lead to errors when you have the 2-column FK; it's just inconvenient.)

You can see that the table meanings are simpler when you just have

Payment /* payment [PaymentID] is enrollment [EnrollID] AND ... */

If you do want to involve the previous Payment meaning in a query then you use JOIN to get the table whose meaning is the AND of the simpler meanings.

PS You don't need to declare a FK Payment (StudentID) to Student (StudentID) because it will be enforced by the combination of FKs in Payment and Enrollment.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.