I am trying to put the following entity-model into practice:
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)
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
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
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:
When I change the account_type of a Payment_type, all payments of this type will then "belong" to the respective accounts.
If I delete an account I want to delete all payments in this account ( like delete cascade in a foreign key )
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.