5

I'm creating a database for a 'bank' and was just unsure as to whether my method of attaching accounts to customers was the most efficient. Here are a few of the enterprise rules:

-there are 2 types of account (current and savings)

-each account has a field for: balance, date of last access

-current accounts have overdraft

-savings accounts have interest_rate

-a customer can have as many accounts as they want

-an account can be 'subscribed' to unlimited people

I'm just not sure if the best method is just to have interest rate and overdraft in the account table to be set to null if it is the wrong account type.

Sorry if this question isn't put very well (I'm breaking my stackoverflow virginity here). Also, here is a link to the ER diagram have created so far: https://drive.google.com/file/d/0BwKmjTsIbwP0SE9Xbk1ycnkyV0E/edit?usp=sharing

Sorry that I couldn't post the image directly on here, it's just because I've got no rep...

Thanks in advance!

user3665815
  • 65
  • 1
  • 1
  • 4

1 Answers1

16

You take the information that you're given, and you normalize the data into relations. That's why you use a relational database.

So let's start with customers. We'll create a Customer table. Generally, table names are singular.

Customer
--------
Customer ID
Customer name
Customer address
...

As you can see, all of the data in the Customer table is about the customer.

Next, we'll create an Account table.

Account
-------
Account ID
Account Type ID
Account balance
Account interest rate
Account overdraft
Account last access time stamp

All the data in the Account table came from your requirements statement. The interest rate is set to zero for accounts with no interest. The overdraft is set to zero for accounts with no overdraft.

Next, we'll create an Account Type table.

Account Type
------------
Account Type ID
Account Type

The data would be (0, Checking) and (1, Savings). With an Account Type table, you can easily add new account types, like (2, Certificate of Deposit).

Next, even though you don't mention it, we'll create a Transaction table. You'll need this to be able to print monthly statements.

Transaction
-----------
Transaction ID
Transaction time stamp
Transaction amount
Account ID

Ok, we've set up all of the entity type tables for banking. Now, let's set up the relationship tables.

Customer and Account have a many to many relationship. A customer can have many accounts, and an account can have many customers.

So, let's create a Customer Account table.

Customer Account
----------------
Customer ID
Account ID
Customer Account creation time stamp

The primary (clustering) key is (Customer ID, Account ID). You'll also need a unique index on (Account ID, Customer ID)

I believe we're done creating tables.

  • An account has an account type.

  • A customer can have many accounts.

  • An account can have many customers.

  • An account can have many transactions.

  • A transaction is posted to one account.

Gilbert Le Blanc
  • 50,182
  • 6
  • 67
  • 111
  • 2
    In the transaction table, there needs to be reference of both the parties involved in the transaction. You have only one. – HIRA THAKUR Feb 19 '18 at 16:00
  • 1
    How can this be scaled if a new account type needs some special fields which are exclusive to this new account type? Is it not better to have different tables for different account types? or use a joined table with a parent table containing common fields. I am struggling to design such a solution and thinking of possible solutions that would scale well. Your solution is already a good solution. – bluelurker Jun 28 '21 at 09:43