Like I say in the question this is my first go at database design and normalization I want to achieve 3NF. The general context is a basic banking system, that allows for loans and multiple transactions, each customer needs email, and phone details also must have an opening balance when setting up an account (over £50).

I've done a lot of research and think it's in 3NF, but need some professionals who know what they are doing to tell me where I have done things wrong perhaps and any tips to scale this design further.

enter image description here


I see several issues:

  • transactions.running_balance is not an attribute of the transaction, it's a denormalized attribute, because you should be able to SUM() the credits and debits and get the running balance that way. If you store the aggregate value as well, there's a chance it could disagree with the actual running balance. I mean, what if you were to check that stored running balance and then do the SUM() of all prior transactions, and it doesn't add up to the right number? How can you know which one is correct?

  • transactions has both a DATE and a DATETIME column. Are both of these supposed to be the date of the transaction? Why two columns, not just one DATETIME column? What would it mean if they have different dates, that is, the DATE has '2021-11-06' and the DATETIME has '2021-11-16 00:00:00'?

  • I can't tell what address.customer_id is for. Maybe it satisfies 3NF, but I can't tell because I don't see the purpose of it from this diagram. Maybe it's a leftover from when you were thinking each address is specific to one customer?

  • You probably would need another table for loan payments, because there are likely to be multiple payments, each with a different amount and date. Then maybe a table for late fees too.

  • Thanks for the tips, so get rid of running_balance would you say, also for transactions date issue, thanks for telling me, think I must have accidentally added a second date column in. Also for address.customer_id ye must have left it over. Could you expand on what you mean by adding another table for loans? thanks
    – Mj _
    Nov 16 '21 at 16:29
  • 2
    I mean a table for loan payments. Like you need to record each time a customer pays part of their loan back. Nov 16 '21 at 16:52

Many things look "right". Here are some nit-picks.

  • Banking has strict rules on rounding. FLOAT is the wrong datatype to use for money or even monthly_rate. See DECIMAL.

  • You may want to keep Loans pristine. That is, find some other place to put the next month's due date.

  • Transactions could use the sign of the amount to determine 'credit' vs 'debit'.

  • You have an extra customer_id.

  • Many-to-many between Customer and Address may be overkill.

  • How would you represent a husband and wife with a join account?

  • Are the tables set up to handle an auto-payment from a checking account toward paying off a loan?

  • 1. Thanks I've change then to decimal(10,2) 2. For loans do you suggest moving anything to do with months, into a new column called payment_plan? 3. What do you mean by your 3rd bullet point? 4. Thanks for spotting that. 5. I thought i would expand customer and address further for scalability 5. Interesting will think about this. 6. Also can you explain last bullet point? thank you.
    – Mj _
    Nov 17 '21 at 7:39
  • @Mj_ - Transactions has two amount columns; isn't one of them always 0? Is a "joint" account represented as one "customer" (the couple, with a single address), or is it two "customers" with different names and (potentially) different addresses? Will auto-pay could be handled as 2 "transactions"?
    – Rick James
    Nov 17 '21 at 17:23
  • yeh i made a credit and debit, credit for outgoing money, debit for incoming. Does this not seem good, what do you suggest I could change it too? thank you.
    – Mj _
    Nov 17 '21 at 17:36
  • @Mj_ Since you are happy with your two-column approach, keep it that way.
    – Rick James
    Nov 18 '21 at 3:00

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