1

I'm learning databases and I'm designing an ERD based on simple banking processes. I'm not sure if my design of the entity relationship diagram is correct.

enter image description here

Thought Process

A customer can open 1 : N accounts, and n accounts can be opened by a single customer N : 1. A bank can offer a customer n loans, and a loan can be offered and associated with a single customer who has a bank account.

N loans can have many loan payments N: M. N accounts can be associated with N : M loans, and n loans can be associated with only N accounts. A single account can have many transactions 1 : M, and many transactions originate (are made by) a single account M : 1.

Summary

I'm not sure if my thought process is entirely correct. I'm still skeptical about loans being an associative entity, and if loan payments is entirely necessary. I believe that it solves the many-to-many relationship between customer, and accounts table.

The only reason why I created loan payments table is to normalize the table to 2NF. Would that be a good approach in terms of entity and relationship design...?

Questions

  1. Are there any design flaws that you can think?
  2. Is there anything I could improve in my design?
  3. Could I add something else to improve it?

1 Answer 1

1
  1. Relation between Customer and Loan is redundant, because it is already defined via relation Customer -> Account -> Loan.

  2. There is contradiction: Relation Customer -> Loan shows that Customer can have 0 Loans. But on the other hand Customer has at least one Account and each Account has at least one Loan. Thus Customer has at Least one Loan.

  3. In your model Customer has at least one Account. It is impossible to say if this is correct or not without knowing requirements in your particular case. But in the real world there can be customers without accounts.

  4. In your model each Account has at least one Loan. Again, if this is your requirement, it is fine. But in the real world you don't have to have a loan to have an account.

  5. In your model each Loan has at least one Payment. In the real world this is not true. First a Loan entity is created. Then it can take months or a year till the first Payment is done.

  6. In your model each Account has at least one Transaction. In the real world this is not true. Account can exist also without any transactions.

  7. In your model a Loan can be related to multiple Accounts. In the real world a Loan usually is related to a single Account.

  8. Wording on your diagram is misleading. "Customer opens an Account" implies that this relation has importance at particular moment of time only. But normally such relation exists over relatively long time. Better would be word "has", "owns" or similar.

  9. "Customer is offered a Loan" - the word "offered" is also misleading. It implies some event in the past. Where as usually such relation exists over relatively long time. Furthermore, "offered" does not tell anything if customer has accepted the offer or not. Again, this is misleading. Better would be "has".

  10. The word "Make" in the relation Account -> Transaction is misleading. Do you mean "An Account makes a Transaction"? This would be incorrect. Better would be words like "changed by".

  11. Word "Has" in the Relation "Loan -> Loan Payment" is not bad, but it implies something static. But in reality there are more and more payments over the time. Better would be word like "Paid by", "Repaid by", "Compensated by" or similar.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.