2

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:

Diagram showing 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.

3
+50

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.

enter image description here

  • 1
    This seems like the most sensible approach, since the trigger will only move payments to the correct account, but all constraints are checked by the db - so if the trigger fails for any reason, the constraint will fail and one can never commit inconsistent data. – Falco Aug 10 '15 at 12:57

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.