database 复式记账系统的数据库模式设计？

Database schema design for a double entry accounting system?

提问by soden

Does anybody know or have any links to websites describing details of how to design a database schema for a double entry accounting system ??.

有没有人知道或有任何网站链接，这些网站描述了如何为复式记账系统设计数据库架构的详细信息？？。

I did find a bunch of articles but non were very explanatory enough. Would appreciate it if someone could help me on this.

我确实找到了一堆文章，但没有足够的解释。如果有人可以帮助我，我将不胜感激。

采纳答案by Leslie

Here's one link that you may find helpful:

这是您可能会觉得有用的一个链接：

http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html

http://homepages.tcp.co.uk/~m-wigley/gc_wp_ded.html

Updated:Alternative links (as original one seems to be broken):

更新：替代链接（因为原始链接似乎已损坏）：

• https://medium.com/@RobertKhou/double-entry-accounting-in-a-relational-database-2b7838a5d7f8(Complete article with images)
• https://vikrampareek.wordpress.com/2012/09/19/185/
• http://acc4arab.com/acc/showthread.php?t=4227

• https://medium.com/@RobertKhou/double-entry-accounting-in-a-relational-database-2b7838a5d7f8（带图片的完整文章）
• https://vikrampareek.wordpress.com/2012/09/19/185/
• http://acc4arab.com/acc/showthread.php?t=4227

回答by bkire

Create the following tables

创建以下表

• account
• transaction
• line_item
• contact (can be a customer a supplier, or an employee).

• 帐户
• 交易
• line_item
• 联系人（可以是客户、供应商或员工）。

To keep things simple, we will leave out the account_type table, contact_type table, etc.

为了简单起见，我们将省略 account_type 表、contact_type 表等。

Identify the relationships between the tables and set them up

确定表之间的关系并进行设置

• a contact can have many transactions, but each transaction can only have one contact (one-to-many relationship)
• an account can have many transactions, and one transaction can affect many accounts; line_item is the join table between transaction table and account table (a many-to-many relationship)
• a transaction can have many line items, but each line item must relate to one transaction.

• 一个联系人可以有多个事务，但每个事务只能有一个联系人（一对多关系）
• 一个账户可以有多个交易，一个交易可以影响多个账户；line_item 是交易表和账户表的连接表（多对多关系）
• 一笔交易可以有多个行项目，但每个行项目必须与一个交易相关。

We have the following schema (a one-to-many relationship):

我们有以下模式（一对多关系）：

CONTACT ———< TRANSACTION ———< LINE_ITEM >——— ACCOUNT

Add appropriate fields to each table

为每个表添加适当的字段

• Contact
  • contactID
  • name
  • addr1
  • addr2
  • city
  • state
  • zip
  • phone
  • fax
  • email
• Transaction
  • transactionID
  • date
  • memo1
  • contactID
  • ref
• Line_item
  • line_itemID
  • transactionID
  • accountID
  • amount
  • memo2
• Account
  • accountID
  • account_name
  • account_type

• 接触
  • 联系人ID
  • 姓名
  • 地址1
  • 地址2
  • 城市
  • 状态
  • 压缩
  • 电话
  • 传真
  • 电子邮件
• 交易
  • 交易ID
  • 日期
  • 备忘录1
  • 联系人ID
  • 参考
• Line_item
  • line_itemID
  • 交易ID
  • 帐户ID
  • 数量
  • 备忘录2
• 帐户
  • 帐户ID
  • 用户名
  • 帐户类型

Create as many new transactions as needed

根据需要创建尽可能多的新交易

For example to add a new transaction in the database, add a new record in the transaction table and fill in the fields, select a contact name, enter a date, etc. Then add new child records to the parent transaction record for each account affected. Each transaction record must have at least two child records (in a double-entry bookkeeping system). If I purchased some cheese for $20 cash, add a child record to the transaction record in the child record, select the Cash account and record ?20.00 (negative) in the amount field. Add a new child record, select the Groceries account and record 20.00 (positive) in the amount field. The sum of the child records should be zero (i.e., 20.00 ? 20.00 = 0.00).

例如在数据库中添加一个新的交易，在交易表中添加一条新记录并填写字段，选择联系人姓名，输入日期等。然后为每个受影响的帐户在父交易记录中添加新的子记录. 每个交易记录必须至少有两个子记录（在复式簿记系统中）。如果我用 20 美元现金购买了一些奶酪，在子记录中的交易记录中添加一个子记录，选择现金帐户并在金额字段中记录 ?20.00（负）。添加新的子记录，选择 Groceries 帐户并在金额字段中记录 20.00（正）。子记录的总和应为零（即，20.00 ? 20.00 = 0.00）。

Create reports in the database based on the data stored in the above tables

根据上表中存储的数据在数据库中创建报表

The query to give me all records in the database organized so that transaction line item child records are grouped by account, sorted by date then by transaction ID. Create a calculation field that gives the running total of the amount field in the transaction line_items records and any other calculation fields you find necessary. If you prefer to show amounts in debit/credit format, create two calculation fields in the database query have one field called debit, and another called credit. In the debit calculation field, enter the formula "if the amount in the amount field from the line_item table is positive, show the amount, otherwise null". In the credit calculation field, enter the formula "if the amount in the amount field from the line-Item table is negative, show the amount, otherwise null".

向我提供数据库中所有记录的查询，以便交易行项目子记录按帐户分组，按日期排序，然后按交易 ID 排序。创建一个计算字段，该字段提供交易 line_items 记录中金额字段的运行总计以及您认为必要的任何其他计算字段。如果您更喜欢以借方/贷方格式显示金额，请在数据库查询中创建两个计算字段，一个字段称为借方，另一个字段称为贷方。在借方计算字段中，输入公式“如果来自line_item表的金额字段中的金额为正数，则显示金额，否则为空”。在信用计算字段中，输入公式“如果行项目表中金额字段中的金额为负数，则显示金额，否则为空”。

Based on this rather simple database design, you can continuously add more fields, tables and reports to add more complexity to your database to track yours or your business finances.

基于这种相当简单的数据库设计，您可以不断添加更多字段、表和报告，以增加数据库的复杂性，以跟踪您或您的企业财务状况。

回答by Kenny Evitt

I figured I might as well take a stab at it. Comments are appreciated – I'll refine the design based on feedback from anyone. I'm going to use SQL Server (2005) T-SQL syntax for now, but if anyone is interested in other languages, let me know and I'll add additional examples.

我想我不妨试一试。感谢评论——我将根据任何人的反馈改进设计。我将使用SQL Server（2005）T-SQL语法现在，但如果有人有兴趣在其他语言，让我知道，我会添加更多的例子。

In a double-entry bookkeeping system, the basic elements are accounts and transactions. The basic 'theory' is the accounting equation: Equity = Assets - Liabilities.

在复式记账系统中，基本要素是账户和交易。基本的“理论”是会计等式：权益=资产-负债。

Combining the items in the accounting equation and two types of nominal accounts, Income and Expenses, the basic organization of the accounts is simply a forest of nested accounts, the root of the (minimum) five trees being one of: Assets, Liabilities, Equity, Income, and Expenses.

结合会计等式中的项目和两种名义账户，收入和费用，账户的基本组织只是一个嵌套账户的森林，（最小）五棵树的根是其中之一：资产、负债、权益、收入和费用。

[I'm researching good SQL designs for hierarchies generally ... I'll update this with specifics later.]

[我正在为层次结构研究良好的 SQL 设计......我稍后会更新这个细节。]

One interesting hierarchy design is documented in the SQL Team article More Trees & Hierarchies in SQL.

SQL 团队文章More Trees & Hierarchies in SQL 中记录了一种有趣的层次结构设计。

Every transaction consists of balanced debit and credit amounts. For every transaction, the total of the debit amounts and the total of the credit amounts must be exactly equal. Every debit and credit amount is tied to one account.

每笔交易都包含平衡的借方和贷方金额。对于每笔交易，借方金额的总和和贷方金额的总和必须完全相等。每个借记和贷记金额都与一个帐户相关联。

[More to follow ...]

[更多关注...]