database 银行账户数据库

Database of bank accounts

提问by user3665815

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.

我只是不确定最好的方法是否只是将帐户表中的利率和透支设置为 null 如果它是错误的帐户类型。

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

对不起，如果这个问题没有很好地提出（我在这里打破了我的 stackoverflow 童贞）。此外，这里是迄今为止创建的 ER 图的链接：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!

提前致谢！

回答by Gilbert Le Blanc

You take the information that you're given, and you normalizethe 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
--------
Customer ID
Customer name
Customer address
...

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

如您所见，Customer 表中的所有数据都是关于客户的。

Next, we'll create an Account table.

接下来，我们将创建一个 Account 表。

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.

Account 表中的所有数据都来自您的需求声明。对于没有利息的账户，利率设置为零。对于没有透支的账户，透支设置为零。

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).

数据将是 (0, Checking) 和 (1, Savings)。使用帐户类型表，您可以轻松添加新的帐户类型，例如（2，存款证明）。

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.

Customer 和 Account 是多对多的关系。一个客户可以有多个账户，一个账户可以有多个客户。

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)

主（集群）键是（客户 ID、帐户 ID）。您还需要一个唯一索引（帐户 ID、客户 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.

• 帐户具有帐户类型。

• 一个客户可以拥有多个帐户。

• 一个帐户可以有多个客户。

• 一个账户可以有很多交易。

• 交易过帐到一个帐户。