Author Cassandra Harley
I am working on the Foreign Keys in my metadata.

I am under the impression that it is a better practice to have an 'ON
DELETE' AND 'ON UPDATE' on the foreign key to maintain referential integrity
(either that, or add TRIGGERs, but I haven't gotten to them...yet).

For most of my database this is simple.
ie if the Employer is deleted, all his employees etc etc can be deleted (ON
DELETE CASCADE works well here), and if the state of NSW (in my STATE table)
merges with VIC (the premiers played a game of football...double or
nothing!) then the Foreign Keys that reference NSW can all become NULL (ON
However I have an area in which I am not sure what would be the best
(I have pasted a trimmed version of the tables below, ie leaving out
anything that is not relevant).

I have following scenario:
If a tuple in the superfund is referenced by a 'supersummary', then that
superfund tuple cannot be deleted.

If a tuple in the superfund is referenced by a 'employee_super_Details' or
'employer_super_details'. Then the delete must cascade (assuming however
that no super_summary exists that references that superfund)

I am not convinced that I have handled the design of these tables (below)

When created a 'supersummary' will be referenced by payslips, each being
from the same employee.

ie. An employee gets 4 payslips this month.
At the end of the month, the payslips are tallied and the superannuation
a 'supersummary' is created to represent the monies owed to the superfund.
The 4 payslips used to calculate the super, will then reference this

The application will work out the periods, calculate the super, and then
create the foreign key references in the payslips, and ensure all the
payslips belong to the same employee.

So due to this rule, the super_summary can be thought of as belonging to one
Note: I have some trouble with this, because it would be possible to create
a super_summary that was referenced by different employees(by accessing the
database directly), although the database would be correct, the program
would no longer work correctly.

If the supersummary belongs to one employee and also one superfund, then
when wanting to report to superfund X about employee B, the program will
then access the 'employee_super_details' table looking for the PK (superfund
X, employee B).

Is this okay?

I think this part of the database is a little different, and hence I am
hesitant about the correctness of it.

I understand that you guys are really not about teaching a girl the basics
of databases. I can assure you I have studied the subject, however it is not
an easy subject to fully grasp until actually getting in the water and
trying some stuff. Which is where I am.

Thanks again,

//Part of my database is below

(Superfund_ID D_PKID,
PRIMARY KEY (Superfund_ID);

(SuperSummary_ID D_PKID,
SuperFund_ID D_ID,
PRIMARY KEY (SuperSummary_ID),
FOREIGN KEY (Superfund_ID) REFERENCES T_Superfund (Superfund_ID));

(Employee_ID D_PKID,
Superfund_ID D_PKID,
PRIMARY KEY (Employee_ID, Superfund_ID));

(Employer_ID D_PKID,
Superfund_ID D_PKID,
PRIMARY KEY (Employer_ID, Superfund_ID));