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.
我确实找到了一些文章，但是没有足够的说明。如果有人能在这方面帮助我，将不胜感激。
3 个解决方案
#1
33
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://medium.com/@RobertKhou/double-entry-account -in- relationship -database-2b7838a5d7f8(完整文章与图片)
- https://vikrampareek.wordpress.com/2012/09/19/185/
- https://vikrampareek.wordpress.com/2012/09/19/185/
- http://acc4arab.com/acc/showthread.php?t=4227
- http://acc4arab.com/acc/showthread.php?t=4227
#2
27
-
create the following tables: account, transaction, lineitem, contact. a contact can be a customer, a supplier, or an employee. to keep things simple, we will leave out the account_type table, contact_type table, etc.
创建以下表:account, transaction, lineitem, contact。联系人可以是客户、供应商或员工。为了简单起见，我们将省略account_type表、contact_type表等。
-
identify the relationships between the tables and set them up.
确定表之间的关系并设置它们。
a) a contact can have many transactions, but each transaction can only have one contact (one-to-many relationship)
a)一个联系人可以有多个事务，但是每个事务只能有一个联系人(一对多关系)
b) an account can have many transactions, and one transaction can affect many accounts; lineitem is the join table between transaction table and account table (a many-to-many relationship)
b)一个账户可以有多个交易，一个交易可以影响多个账户;lineitem是事务表和account表之间的连接表(多对多关系)
c) a transaction can have many line items, but each line item must relate to one transaction. We have the following schema (a one-to-many relationship):
一个事务可以有许多行项，但是每个行项必须与一个事务相关。我们有以下模式(一对多关系):
CONTACT >——— TRANSACTION ———< LINE_ITEM >——— ACCOUNT
联系> - - -事务- - - < LINE_ITEM > - - - - - ACCOUNT
3 Add appropriate fields to each table. Contact (contactID, name, addr1, addr2, city, state, zip, phone, fax, email, etc.). Transaction (transactionID, date, memo1, contactID, ref, line_itemID. Line_item (line_itemID, transactionID, accountID, amount, memo2). Account (accountID, line_itemID, account_name, account_type).
向每个表添加适当的字段。联系(contactID, name, addr1, addr2, city, state, zip，电话，传真，电子邮件等)。事务(transactionID, date, memo1, contactID, ref, line_itemID)。Line_item (line_itemID、transactionID、accountID、金额、memo2)Account (accountID, line_itemID, account_name, account_type)。
4 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). Create as many new transactions as needed.
例如，要在数据库中添加一个新事务，在事务表中添加一个新记录，并填写字段，选择联系人名称，输入日期等。每个事务记录必须至少有两个子记录(在复式簿记系统中)。如果我购买了一些奶酪20美元现金,添加一个孩子记录交易记录,在孩子的记录,选择现金帐户和记录−20.00(负面)领域。添加一个新的子记录，在amount字段中选择杂货帐户和记录20.00(正)。子记录的和应该是零(即。,20.00−20.00 = 0.00)。根据需要创建尽可能多的新事务。
5 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表中的金额为正数，则显示金额，否则为null”。在信用计算字段中，输入公式“如果行项表中的金额为负数，则显示金额，否则为空”。
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.
基于这个相当简单的数据库设计，您可以不断地添加更多的字段、表和报表，以增加数据库的复杂性，以跟踪您或您的业务财务状况。
#3
8
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 Team的文章“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 ...]
(更多追随…)
#1
33
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://medium.com/@RobertKhou/double-entry-account -in- relationship -database-2b7838a5d7f8(完整文章与图片)
- https://vikrampareek.wordpress.com/2012/09/19/185/
- https://vikrampareek.wordpress.com/2012/09/19/185/
- http://acc4arab.com/acc/showthread.php?t=4227
- http://acc4arab.com/acc/showthread.php?t=4227
#2
27
-
create the following tables: account, transaction, lineitem, contact. a contact can be a customer, a supplier, or an employee. to keep things simple, we will leave out the account_type table, contact_type table, etc.
创建以下表:account, transaction, lineitem, contact。联系人可以是客户、供应商或员工。为了简单起见，我们将省略account_type表、contact_type表等。
-
identify the relationships between the tables and set them up.
确定表之间的关系并设置它们。
a) a contact can have many transactions, but each transaction can only have one contact (one-to-many relationship)
a)一个联系人可以有多个事务，但是每个事务只能有一个联系人(一对多关系)
b) an account can have many transactions, and one transaction can affect many accounts; lineitem is the join table between transaction table and account table (a many-to-many relationship)
b)一个账户可以有多个交易，一个交易可以影响多个账户;lineitem是事务表和account表之间的连接表(多对多关系)
c) a transaction can have many line items, but each line item must relate to one transaction. We have the following schema (a one-to-many relationship):
一个事务可以有许多行项，但是每个行项必须与一个事务相关。我们有以下模式(一对多关系):
CONTACT >——— TRANSACTION ———< LINE_ITEM >——— ACCOUNT
联系> - - -事务- - - < LINE_ITEM > - - - - - ACCOUNT
3 Add appropriate fields to each table. Contact (contactID, name, addr1, addr2, city, state, zip, phone, fax, email, etc.). Transaction (transactionID, date, memo1, contactID, ref, line_itemID. Line_item (line_itemID, transactionID, accountID, amount, memo2). Account (accountID, line_itemID, account_name, account_type).
向每个表添加适当的字段。联系(contactID, name, addr1, addr2, city, state, zip，电话，传真，电子邮件等)。事务(transactionID, date, memo1, contactID, ref, line_itemID)。Line_item (line_itemID、transactionID、accountID、金额、memo2)Account (accountID, line_itemID, account_name, account_type)。
4 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). Create as many new transactions as needed.
例如，要在数据库中添加一个新事务，在事务表中添加一个新记录，并填写字段，选择联系人名称，输入日期等。每个事务记录必须至少有两个子记录(在复式簿记系统中)。如果我购买了一些奶酪20美元现金,添加一个孩子记录交易记录,在孩子的记录,选择现金帐户和记录−20.00(负面)领域。添加一个新的子记录，在amount字段中选择杂货帐户和记录20.00(正)。子记录的和应该是零(即。,20.00−20.00 = 0.00)。根据需要创建尽可能多的新事务。
5 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表中的金额为正数，则显示金额，否则为null”。在信用计算字段中，输入公式“如果行项表中的金额为负数，则显示金额，否则为空”。
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.
基于这个相当简单的数据库设计，您可以不断地添加更多的字段、表和报表，以增加数据库的复杂性，以跟踪您或您的业务财务状况。
#3
8
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 Team的文章“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 ...]
(更多追随…)