Enterprise Resource Planning (ERP)

An accounting system is as good as the underlying data model. A consistent data organisation simplifies not only the data entry, but enhances the flexibility and significance of the reports as well. It is surprising, how little data modelling has been recognized as a possibility for innovations in the field of general ledger accounting. 

The organisation of data according to mathematical criteria is a prerequisite for the efficient use of databases and is know under the term of “normalisation”. The basic concepts have been introduced by Edgar F. Codd (E.F.Codd, „A Relational Model of Data for Large Shared Data Banks“, Comm.ACM 13 (6), June 1970, pp. 377-387). Working with large databases Codd developed his rules of normalisation that are widely accepted up to this day.

Codd, E.F. (1970). "A Relational Model of Data for Large Shared Data Banks". Communications of the ACM 13 (6): 377–387

Codd discovered soon that through normalisation of data a universal data language could be developed. With this approach the application was able to reach a maximum of independency from the data. Furthermore, to a large extent the redundancy could be reduced and multiple entries requiring complicated administration tools again avoided.

In practice the maximum elimination of redundancy is not consequently realised however. An important reason is the time aspect. In a billing process for example, it is necessary to register the client address each time a document is sent. Considering data consistency it would not be desirable that a change of address would apply to all open or even posted billings.

Another limiting factor is the complexity of the application. Consequently normalised data leads to many very small tables that are reintegrated for the application into so called “views”. The usage of views requires a technical basis for this in the used database, an element rather complicated to realise.
 
As a third element should be considered that Codd’s reflections treat mainly the structuring of master data, basically composed of an index and descriptive elements (e.g. address number and address data). The key question for double sided accounting regarding multiple indices in the same transaction table remains here not answered.

Following the arguments of Codd, the further normalisation of double sided into single sided bookings seems an evident conclusion. At the first normalisation level already Codd required that the repetition of homogenous data is to be avoided:

Instead of a list with structure A:

- batch, account1, account2, amount

It is better to establish a list with structure B:

- batch, account, amount

For the accounting this is especially evident, since the bookings are attributed to a batch. Following the list with structure A there are immediately problems arising for the reporting programs. With such a data structure, how could a database selection call for a balance or an income statement ever work efficiently? For every query line the program would have to check if account1 or account2 would correspond to the current reporting account, and for account1 the positive amount and for account2 the negative amount would have to be considered for the calculation of the report total.

In a more general view, this design issue is well known among programmers as the so called “bill of materials” problem. Records of a bill of materials include two product numbers, one for the master product and one for the detail product. Two product numbers are necessary, since each product can be relied in multiple ways to any other product (compare to this subject: Carl August Zehnder, Informationssysteme und Datenbanken, B.G.Teubner, Stuttgart 1989, mainly pages 60 f).

For the full normalisation of this „many to many“ relation the relationship as a container must be registered in the database as well. Based on this entry, single sided relations to the products can be entered. In practice this rather complicated solution is often avoided, since it is not efficient for applications with drag and drop functionality, where for example a master product is simply dragged over a detail product. The application can be simplified recording the master product as product1, the detail product as product2. With this approach the reports however get more complicated. These were probably the considerations that got Pacioli and others to use the double sided bookings.

On the other side, with today’s technical possibilities, there is little interest to omit the “batch” in the accounting data model. On the contrary, for the revision and the comprehension of the booking process the possibility to attribute bookings to a batch is most valuable. This corresponds well to the usual presentation on paper. Thereby it is less important if the bookings are single or double sided. It is only necessary to assure that the sum of all bookings in the batch result in a total of zero. For double sided bookings this will always be the case, for single sided bookings it will be necessary to check for the zero total before the batch can be posted.

It is also interesting to reflect the account assignment under the aspect of normalisation. As a matter of fact, almost all charts of accounts include several dimensions summarized to an overloaded register difficult to handle. A chart of accounts often includes elements to structure the internal organisation, the business field and the contract partner. This makes the general ledger too complex and requires considerable efforts to separate the chart of accounts into these underlying dimensions in the reports.

The complexity of the chart of accounts is already apparent in the proceedings of Pacioli. The initial concept of an account was to represent a client reporting (that is the contractual relation to the principal or the believer). But soon enough virtual principals and believers appear, like merchandise purchase and stock, and later general expenditures as well as profits and losses make the contractual aspect of the account completely disappear.

These arguments show that it is much more efficient to use a consistent data model for the general ledger accounting as well. This way, the chart of accounts can be kept slim and oriented for the financial reporting. The details for the contract partners, business fields and organisational units are then available from supplementary data structures at the user’s demand.