Foreign Key Constraint over two tables

Question

I am trying to put the following entity-model into practice:

1. We have a list of Payments, each Payment belongs to a certain store and is of a certain payment type (e.g. 10€ maintenance cost in Store1, 20€ rent in Store2)

2. A Payment Type is a categroy like (rent, maintenance, legal fees, licenses) and each category is assigned a certaing account_type (e.g. budget_legal, budget_monthly, budget_extraordinary ) where multiple Payment types can have the same account_type

3. We have accounts: An account belongs to a certain store for a certain account type. So Store1 can have account-types budget_legal, budget_monthly, while Store2 has account types budget_legal, budget_monthly and budget_extra

Relationship Model:

Image Created with http://draw.io/

Now I want a constraint that each payment must belong to an existing account. I want to guarantee the following:

1. When I change the account_type of a Payment_type, all payments of this type will then "belong" to the respective accounts.

2. If I delete an account I want to delete all payments in this account ( like delete cascade in a foreign key )

3. I can only insert/update Payments so that each payment refers to a valid store_id/account_type, which exists in the accounts table.

---

Possible solutions which I tried but don't really work:

I tried to add the account_type to payments, but then I have doubled the field - and when I change a Payment type I will have to change the referenced payments via trigger. - But in the best case I don't want triggers to enforce data integrity - and I don't want duplicate data storage.

I tried to create a foreign key constraint over multiple tables / a view which joins payment with payment_types. Unfortunately this seems to be impossible in Oracle DBMS

I tried to create a virtual column on Payments, which calculates the value from payment_types, but virtual columns can only reference columns in the same table.

Answer 1

Some notes first:

• Considering that Accounts has a unique constraint on (account_type, account_store), a Payment seem to be associated with only one Account. That suggests that the FK from Payments should reference Accounts (and not Stores). This change will also solve issue #2.

• Adding account_type to Payments (and modifying accordingly the foreign key to Payment_types) seems like the most sensible approach and it will solve issue #3.

• The two changes together will solve issue #1.

• Oracle does not have ON UPDATE CASCADE option, so this issue (updating the account_type of a Payment_type to update the related Payments) needs to be addressed by either an update trigger or by a stored procedure (that will also check that related Accounts also exist (or are added) for all Stores that have Payments of the (updated) account_type.

The suggested design, assuming you also have an Account_types table (I renamed a few columns but that is up to you and your preferences / conventions):

Account_types
---------------
account_type_id  PK


Payment_types
---------------
account_type_id  PK, FK -> Account_types
payment_type_id  PK


Stores
--------
store_id         PK


Accounts
---------------
account_type_id  PK, FK1 -> Account_types
account_id       PK
store_id             FK2 -> Stores


Payments
---------------
payment_id       PK
amount
account_type_id      FK1, FK2 
payment_type_id      FK1 -> Payment_types
account_id           FK2 -> Accounts

Note how both foreign keys from Payments use composite keys.