referential integrity

[CJ_London]

There is no doubt that enforcing referential integrity (a child record cannot exist without a parent record) is a good thing to do, But the blanket statement that a design is not good because you cannot enforce RI is not always true.

You can have a child record providing the parentFK is null. It is only when you assign a value to the parentFK, that value must exist as the primary key in the parent table and referential integrity can be enforced.

This feature can have benefits. For example in a cashbook type accounting system you have income/expenses and bank transactions. The expense occurs first and the bank transaction later, but the transaction is the parent since one transaction (a payment) might cover many expenses. So you leave the expense parentFK as null and when the payment occurs in the bank statement assign the transactionPK to the parentFK(s).

Once you have done this assignment, referential integrity kicks in and you cannot delete the bank transaction - but you can still edit the expense parentFK or even delete the record however you can use code and/or conditional formatting to prevent this happening

You could say don't enter the expense until the bank transaction occurs but a) this is not always convenient and b) by summing the income/expenses with a null parentFK you have a rudimentary view of income and expense accruals - accruals are not normally part of a cashbook system.

If you truly want to fully enforce referential integrity so no child record can exist even with a null parentFK then you also need to set the parentFK required field to yes.

 

[CJ_London]

Don't confuse optional relationships with required relationships. BOTH support RI.

I wasn't

 

[CJ_London]

No, just pointing out that many newbies think that RI means a record cannot exist in a child table without a parent.

when in fact a record can legitimately exist if the fk is null. And if the developer does not want that to be a possibility then they need to set the fk field to required

you used the word ‘optional’, I didn’t. Otherwise you are just restating my comments

 

[CJ_London]

Thanks for providing additional clarification for others

 

[CJ_London]

Are you recommending to NOT use RI when the relationship is optional?

This the only question I can see , and I answered no

 

[Galaxiom]

I would have expected transaction and expense to be a many to many. An expense can be paid over multiple transactions. A transaction can pay multiple expenses,

 

[GPGeorge]

I would have expected transaction and expense to be a many to many. An expense can be paid over multiple transactions. A transaction can pay multiple expenses,

That seems to me to be the basis of a Business Rule. Business Rules tend to be specific to businesses that invent their own rules.

I suspect you are right for the majority of mature businesses, though.

 

[Galaxiom]

Sometimes I argue with clients about business rules that are bizarre but, in the end, if it is not illegal, immoral, or fattening, I warn them and just do it.

I have had cases where I could see the reality would be that the program settings would need change in the future, despite users assuring me that it wouldn't. So built in what made sense and simply hid the extra functionality from the front end.

Sure enough, before long, the request to change the configuration was requested and I was able to make the changes in record time.

 

[CJ_London]

An expense can be paid over multiple transactions. A transaction can pay multiple expenses,

don't disagree for a double entry accounting system, but the example I used was for a cashbook system.