A. Preliminary
您的方法
首先,我必须赞扬你的态度.很少有人不仅从坚实的基础上思考和工作,而且希望理解和实施复式记账系统,而不是:
避免所有这些,并寻求标准方法,受到高度赞扬.
此外,您希望在关系数据模型的形式下,您不会被日期、Darwen、Fagin等规定了基于Record ID
的记录归档系统的视图所奴役,因为这些视图削弱了建模练习和由此产生的"数据库".如今,一些人痴迷于原始的RFs,并压制E F Codd博士的Relational Model.
1.寻找答案的方法
如果你不介意的话,我会按照逻辑顺序从头开始解释,这样我就可以避免重复,而不仅仅是回答你的特定要求.如果你对以上任何一点都了如指掌,我深表歉意.
障碍物
理想情况下,我想看看这double entry rows个在数据库中是什么样子
这是建模或定义任何事物所需的正确方法的障碍.
- 与在每个文件上加盖
ID
字段并使其成为"键"一样,这会削弱建模练习,因为这会阻止数据分析(数据表示的实际上是什么),在开始时期望贷方/借方对有两行将削弱对事物是什么的理解;会计操作是什么;这些操作有什么影响;以及最重要的是,数据将如何建模.尤其是当一个人在学习的时候.
Aristotle告诉我们:
the least initial deviation from the truth is multiplied later a thousandfold ...
a principle is great, rather in power, than in extent; hence that which was small [mistake] at the start turns out a giant [mistake] at the end.
释义为,开头的一个小错误(例如.原则;定义)最后证明是一个大错误.
因此,对智力的要求,第一件事,是在建模练习结束时,明确你的 idea .当然,在学习会计术语时,这也是必须的.
2.答案的范围
Assume there is a bank, a large shop, etc, that wants the accounting to be done correctly, for both internal accounts, and keeping track of customer accounts.
Let's just call it System
instead of Bank
, Bank
may be too complex to model ...
Customers perform a set of operations with system (deposits, withdrawals, fee for latter, batch fees), and with each other (transfer).
为了明确起见,我已确定范围如下.如果不是,请纠正我:
- 不是只有总账、没有客户账户的小企业
- 而是一家小型社区银行,没有分行(总行是the家分行)
- 您需要这internal个帐户,其中包括:
- 一辆简单的将军Ledger,
- 还有external个账户,每个客户一个
- 我脑海中最好的概念是一家小型社区银行,或者像这样运营的企业.一个农业合作社,每个农民都有Account美元作为购买依据,按月计费和支付,合作社的运作就像一家小银行,有完整的Ledger总额度,并提供一些简单的银行便利.
- 单个赌场(而不是连锁赌场)也有同样的要求.
- 不是一家拥有多家分行的大银行;各种金融产品等.
- 我将称之为
House
,而不是System
或Bank
.这一点的相关性将在稍后明确.
任何寻求just、Ledger、without(外部客户Account)的复式输入法的人,都可以很容易地从这个答案中找到答案.
同样,这里给出的数据模型也很容易扩展,Ledger
可以比给出的简单数据模型大.
B. Solution
1.复式记账法
1.1. 概念
知道它的名字是什么;它有巨大的价值;它比一个自己滚动的系统更好,这是一回事,知道它是什么,足以实现它,是另一回事.
首先,一个人需要对总分类账和总会计原则有一个良好的理解.
第二,理解金钱代表价值的概念.价值无法创建或销毁,只能移动.From账户中的一个存储桶to另一个存储桶,也称为借方(从账户)和贷方(到账户).
虽然确实有the SUM( all Credits ) = SUM( all Debits )份,而且可以从DEA系统获得这样的报告,但这不是实施所需的理解,这只是一个最终结果.还有更多的事情要做.
虽然every transaction consists of a pair: one Credit and one Debit for the same amount是真的,但还有更多的东西.
贷方和借方不在同一账户或分类账中,它们在不同的账户或分类账中,或在不同的账户和分类账中.
SUM( all Credits )并不简单,因为它们在不同的地方(套装).它们不在同一个表的两行中(稍后可能会有更多).同样,the SUM( all Debits ).
因此,两个sum()中的每一个覆盖完全不同的集合(关系集),并且必须首先获得,然后才能比较这两个sum().
1.2.对复式记账会计的理解
在try DEA实现之前,我们需要正确地理解我们正在实现的东西.我建议如下:
- 你说得对,第一个原则是持有perspective of the Credit/Debit Pair美元,在处理账簿上的任何东西时,比如总账、客户账户、银行账户等等.
- 复式记账系统的目标是:
消除(而不仅仅是减少)所谓的:
Full Audit functionality
It is not good enough to keep good Accounts, it is imperative for a business that accounts for other people's money, to be readily audit-able. That is, any accountant or auditor must be able to examine the books without let or hindrance.
- 这就是为什么一个局外人,例如一个审计员,首先想知道的是does the SUM( all Credits ) = SUM( all Debits ).这也解释了为什么DEA概念高于公司可能保留的任何账户或会计系统.
最大的好处,虽然是第三级,是每天或月末的任务,如试算表或 checkout ,可以很容易和快速地结清.所有报告、报表、assets资源 负债表等都可以简单地获得(如果数据库是关系数据库,则只需SELECT
).
- Then准备好100的维基百科词条.
互联网上有大量误导性的信息,而维基百科尤其可怕,它永远在改变(真相不会改变,谎言会随着天气而改变),但抱歉,这就是我们所拥有的一切.仅用于获取概述,它没有 struct 或逻辑描述,尽管它很长.点击链接获取更多信息.
我不完全同意Wikipedia article中的术语.然而,为了避免可避免的念力,我将使用这些术语.
网上有一些教程,有些比另一些更好.建议任何实施适当会计系统的人使用或不使用DEA.这需要时间,这与这样的答案无关,这就是我将Wikipedia article联系在一起的原因.
2.业务往来
理想情况下,我希望查看那些复式条目行在数据库术语中是什么样子,SQL中的整个过程是什么样子,以及在每种情况下受影响的实体是什么,等等.
好啊让我们先讨论事务,然后逐步了解支持它们的数据模型,然后判断示例行.任何其他命令都会适得其反,并导致不必要的来回.
你的号码.一般情况下,绿色是House
,蓝色是外部客户Account
,黑色是中性.
这是第一个Treatment的增量,在不同的场景中如何对待一件事情(您关心的问题,以及您对具体示例的要求,都是完全正确的).
Credit/Debit Pairs
This is the first principle of DEA, understand the pair, as the pair, and nothing but the pair.
不用担心General Ledger
或Account
是如何设置的,或者数据模型是什么样子.从会计(账簿上必须做的事情)的Angular 来考虑,而不是从开发人员(系统中必须做的事情)的Angular 来考虑.
请注意,该对的每条腿都在一组(Ledger
)中,或者在两组中(一条腿在Ledger
中,另一条腿在Account
中).在Account
中没有两条腿都在的对.
- 因为实现了DEA,所以每个Business Transaction(与数据库事务不同)由两个操作组成,每个贷方/借方分支一个.这两个动作是纸质账簿中的两个分录.
- 客户将现金存入他的账户.
- 在日终程序中,除其他任务外,所有现金都要记账和判断.这一天结束了.银行认为日常现金交易所需的现金以外的所有
HouseCash
元现金都被转移到HouseReserve
元.
- 银行每月向所有客户账户收取一次费用(样本批量作业(job))
- 这将以
Fee
的价格向每Account
人收费
Fee
取决于AccountType_Ext
- 这就是一个简单的例子.如果
Fee
依赖于其他因素,例如Account
中的交易数量;或者CurrentBalance
低于或高于某个限制;等等,则不会显示.我相信你能弄清楚这一点.
- 客户在柜台做一些操作,银行收取手续费(取现+提款手续费),
- 简单的交易不会产生费用,而且已经提供了存取款.让我们来看看一笔实际收取费用的商业交易.
- 玛丽寄了500美元给她的儿子弗雷德,他正在海外旅行寻找鲸鱼来拯救,但钱已经用完了.银行向海外银行转账收取30美元的手续费.弗雷德可以在任何合作银行分行收取资金(相当于500美元的当地货币).
- 要真正把钱转到 foreign 银行,
House
必须与当地一家提供国际结算和货币兑换服务的大银行互动.这与我们无关,也没有显示出来.在任何情况下,所有这些类型的Interbank
笔交易都是每天批处理一次,而不是每AccountTransaction
笔处理一次.
- 在这个简单的DEA系统中,
House
在Ledger
中没有货币账户.这很容易实现.
- 玛丽从她的账户上汇了一些钱到约翰的账户,约翰在同一家银行
- 这笔钱目前在玛丽的账户中(在今天的前一天存入),这就是为什么它是
HouseReserve
,而不是HouseCash
- 因为约翰今天可能会到银行取钱,所以钱从
HouseReserve
转到了HouseCash
.
- 如上例[1.3]所述,在DayEnd过程中,所有
Accounts
中HouseCash
的钱都将移动到HouseReserve
.未显示.
3.关系数据模型·初始
现在让我们看看数据建模师做了什么来支持会计的需求,即业务事务.
当然,这是第二个增量Treatment,建模者已经理解了真实世界中的业务事务,用关系术语表示(FOPC;RM;逻辑;规范化)
这不是满足重申范围所需的最简单的数据模型.
有一些更简单的模型(稍后会有更多),但它们有本模型没有的问题,这些问题是需要避免的,如果不是必须避免的话.
图像太大,无法在线查看.在新选项卡中打开图像,以欣赏完整的图像.
3.1. 符号
3.2.内容
The main difference between a genuine Relational data model produced by someone else, and mine, is:
a business Transaction (always two actions; two legs, one per Credit/Debit) is affected by a single row with two sides, one per Credit/Debit,
in AccountTransaction
or LedgerTransaction
.
大多数建模者会为贷记/借记对建模两行,每条腿或每侧各一行(hey, one leg is a Credit, and the other leg is a Debit, if I Normalise that, I get two rows).
错误的如果我告诉你弗雷德是萨利的父亲,你知道,从这一事实来看,萨利是弗雷德的女儿.
FOREIGN KEY
只需要声明一次,而不是每一方都声明一次.
Likewise, the Credit/Debit pair is a single Business Transaction,
a single Atomic article, that can be perceived from either Side, like two sides of one coin. Modelled as such.
防止了各种可预防的缺陷,消除了对"缺失"腿的搜索.
即使对于那些使用不标准OLTP代码的人来说(这会导致相当可预防的并发问题),如果实现了这种方法,这篇文章也不会出现这些问题.
此外,%Transaction
个表中的行数减少了一半.
I have arranged the articles such that the
External Account
Internal Ledger
and LedgerTransaction
Internal-External AccountTransaction
are clear.
同时还有Wikipedia entry米的高清晰度.
熟悉了DEA贷方/借方对之后,现在研究一下这对中的Treatment对.请注意,处理方式是不同的,它基于一系列标准(三种帐户类型;六种Ledger
类型;等等),而这些标准又基于总帐的复杂性.
这Ledger
个账户很简单,只有Asset/Liability
个账户.当然,你可以自由扩展.
眼光敏锐的人会注意到,AccountStatement.ClosingBalance
和LedgerStatement.ClosingBalance
实际上是可以推导出来的,因此(从表面上看)不应该存储.然而,这些都是公布的数字,例如.每个账户的月度银行对帐单,因此受Audit的限制,因此必须存储.
全面处理该问题,包括考虑因素;释义治疗,请参阅本问答;A:
3.3. 总结
在结束本节时,我们本应达成以下理解:
4.关系数据模型·完整
这又是一个完整的样本数据集.
Re Primary Keys:
请注意,LedgerNo
和AccountNo
不是代词,它们对组织、Ledger
的排序和 struct 等都有意义.它们是稳定的数字,而不是AUTOINCREMENT
或IDENTITY
或任何类似的数字.
LedgerTransaction
和AccountTransaction
的主键是纯复合关系键.
它不是纸质会计钟爱的某种交易号.
它也不是一个残废的Record ID
.
这Alternate Keys个对人类更有意义,因此我在示例中使用了它们(上面[2]和下面[5]的业务事务).这个答案已经是层次分明的,试图将成百上千的1's, 2's
和3’s
联系起来将是一场噩梦.
如果我们想理解事物的含义,我们需要抓住事物中存在的意义,而不是通过给它一个数字来消除意义.
在示例数据中,主键为粗体.
5.与世界其他地区的业务往来
理想情况下,我希望查看那些复式条目行在数据库术语中是什么样子,SQL中的整个过程是什么样子,以及在每种情况下受影响的实体是什么,等等.
现在我们了解了业务事务和服务于需求的数据模型,我们可以判断业务事务along with个受影响的行.
Each Business Transaction, in DEA terms, has two legs, two entries in the paper-based account books, for each of the Credit/Debit pair,
is yet a single Business Transaction, and now:
it is affected by a single row with two sides, for each of the Credit/Debit pair.
这是理解Treatment的第三步:业务事务;实现它们的数据模型;现在是受影响的行
The example database rows are prefixed with the table name in short form.
Plus means INSERT
Minus means DELETE
Equal means UPDATE
.
- 客户将现金存入他的账户.
- 银行每月向所有客户账户收取一次费用(样本批量作业(job))
- 这也是一个批处理作业(job),只是MonthEnd过程中的一个任务.
- 请注意,日期是该月的第一天.
- 客户在柜台做一些操作,银行收取手续费(取现+提款手续费),
- 需要明确的是,这是三个业务事务;每个事务两个分录,贷方/借方对的每一方各一个分录;受每个数据库行的影响.
- 玛丽从她的账户上汇了一些钱到约翰的账户,约翰在同一家银行
6.SQL代码
通常有几种方法来剥cat 的皮(代码),但如果cat 是活的(用于高并发系统的代码),则方法很少.
因此,在可能用于SQL代码请求的几种方法中,我给出了最直接和最符合逻辑的方法.
代码示例适用于SO,您必须捕获错误并从错误中恢复;不要try 任何会失败的操作(在使用谓词之前判断操作的有效性),并遵循ACID事务的OLTP标准等.此处给出的示例代码仅是相关的代码片段.
6.1. SQL视图•账户当前余额
因为这个代码段在很多地方都会用到,所以让我们做正确的事情并创建一个视图.
CREATE VIEW Account_Current_V
AS
SELECT AccountNo,
Date = DATEADD( DD, -1, GETDATE() ), -- show /as of/ previous day
ASS.ClosingBalance, -- 1st of this month
TotalCredit = (
SELECT SUM( Amount )
FROM AccountTransaction ATT
WHERE ATT.AccountNo = ASS.AccountNo
AND XactTypeCode_Ext IN ( "AC", "Dp" )
-- >= 1st day of this month yy.mm.01 /AND <= current date/
AND DateTime >= CONVERT( CHAR(6), GETDATE(), 2 ) + "01"
),
TotalDebit = (
SELECT SUM( Amount )
FROM AccountTransaction ATT
WHERE ATT.AccountNo = ASS.AccountNo
AND XactTypeCode_Ext NOT IN ( "AC", "Dp" )
AND DateTime >= CONVERT( CHAR(6), GETDATE(), 2 ) + "01"
),
CurrentBalance = ClosingBalance +
<TotalCredit> - -- subquery above
<TotalDebit> -- subquery above
FROM AccountStatement ASS
-- 1st day of this month
WHERE ASS.Date = CONVERT( CHAR(6), GETDATE(), 2 ) + "01"
6.2.SQL事务·[1.2]退出[外部]账户
另一个DEA业务交易的过程.
CREATE PROC Account_Withdraw_tr (
@AccountNo,
@Amount
) AS
IF EXISTS ( SELECT 1 -- validate before verb
FROM AccountCurrent_V
WHERE AccountNo = @AccountNo
AND CurrentBalance >= @Amount -- withdrawal is possible
)
BEGIN
SELECT @LedgerNo = LedgerNo
FROM Ledger
WHERE Name = "HouseCash"
BEGIN TRAN
INSERT AccountTransaction
VALUES ( @LedgerNo, GETDATE(), "Cr", "Wd", @AccountNo, @Amount )
COMMIT TRAN
END
6.3.SQL事务·[1.1]存入[外部]帐户
设置为SQL事务的过程,用于执行DEA业务事务.
CREATE PROC Account_Deposit_tr (
@AccountNo,
@Amount
) AS
... IF EXISTS, etc ... -- validate before verb
BEGIN
SELECT @LedgerNo ...
BEGIN TRAN
INSERT AccountTransaction
VALUES ( @LedgerNo, GETDATE(), "Dr", "Dp", @AccountNo, @Amount )
COMMIT TRAN
END
6.4. SQL交易•[内部]分类科目转账
将任何业务交易添加到LedgerAccount
的过程.它总是:
- 一个
LedgerTransaction.LedgerNo
,这是Credit
条腿
- 一个
LedgerTransaction.LedgerNo_Dr
,这是Debit
条腿.
- 由呼叫者提供.
CREATE PROC Ledger_Xact_tr (
@LedgerNo, -- Credit Ledger Account
@LedgerNo_Dr, -- Debit Ledger Account
@Amount
) AS
... IF EXISTS, etc ...
BEGIN
SELECT @LedgerNo ...
BEGIN TRAN
INSERT LedgerTransaction
VALUES ( @LedgerNo, GETDATE(), @LedgerNo_Dr, @Amount )
COMMIT TRAN
END
6.5.SQL批处理任务·账户月末
对于任何月份,这都使用与[6.1 Account Current Balance]类似的视图(视图是通用的),并将值限制为该月.呼叫者 Select 上个月.
在存储的过程中只有一个任务来处理AccountStatement
的月末,该任务作为批处理作业(job)执行.同样,只需添加基本代码和基础设施即可.
CREATE PROC Account_MonthEnd_btr ( ... )
AS
... begin loop
... batch transaction control (eg. 500 rows per xact), etc ...
INSERT AccountStatement
SELECT ACT.AccountNo,
CONVERT( CHAR(6), GETDATE(), 2 ) + "01", -- 1st day THIS month
AMV.ClosingBalance, -- for PREVIOUS month
AMV.TotalCredit,
AMV.TotalDebit
FROM Account ACT
JOIN Account_Month_V AMV -- follow link for code
ON ACT.AccountNo = AMV.AccountNo
-- 1st day PREVIOUS month
WHERE AMV.OpeningDate = DATEADD( MM, -1, ACT.Date )
... end loop
... batch transaction control, etc ...
6.6. SQL报表•金额(贷方)与金额(借方)
虽然the SUM( all Credits ) = SUM( all Debits )是真的,人们可以从DEA系统获得这样的报告,但这不是understanding.还有more个.
希望我已经给出了方法和细节,并涵盖了understanding和more,这样您现在就可以轻松地编写所需的SELECT
来生成所需的报告.
或者可能是External Accounts
的月结单,有一个连续的合计AccountBalance
列.想想:一份银行对账单.
- 真正的关系型数据库的众多巨大效率之一是,任何报告都可以通过single 100 command提供服务.
一份PDF文档
最后但并非最不重要的一点是,需要有数据模型;交易示例;代码片段全部以A3格式组织在一个100中(我的美国朋友使用11x17).学习和注释时,请使用A2(17x22)打印.