Solved

data modeling a double entry accounting system

Posted on 2009-05-14
10
3,217 Views
Last Modified: 2012-06-27
This is a question addressed at data modeling a double entry accounting system.

The diagram below shows an example of a data model that is used when a user withdraws funds from an internal system account to either a bank account or a credit card.

>>>The question is,
1 - is there anything that can be done to improve the structure or errors you may find?
2 - to support multi-currency would it be better to have multiple journal/posting tables for each currency? so that each journal/posting only consist of 1 type of currency.
(the current design supports multi-currency as it is).

Note1:
The Batch table is used for entry of data at batch level (it contains both debits & credits in a single transaction ie single line/row)
The Journal table is used to group the batch entries
The Posting table is where the actual double entry accounting occurs (each transaction from the journal is split into either credit or debit transaction) i.e we do this http://www.bestbusinessinfo.com/1_3/1_3graphics/11303_20.gif

dea-withdraw.JPG
0
Comment
Question by:robertkc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 22

Expert Comment

by:dportas
ID: 24388309
I always recommend to avoid the MONEY type. It has too many problems with rounding and conversion. NUMERIC is a ISO Standard SQL type and is a much better choice for monetary amounts.


0
 

Author Comment

by:robertkc
ID: 24401555
hi dportas

what "exactly" is wrong with it (money data type), do you have sources in regards to reproducing the issues found.

0
 
LVL 22

Accepted Solution

by:
dportas earned 400 total points
ID: 24401673
Rounding issues due to 4 decimal limitation (repro at the bottom of this comment).

Many T-SQL calculations will involve numeric types anyway, which means you have the potential overhead of doing repeated conversions from the MONEY type.

Unlike NUMERIC, MONEY is not standard SQL and is not supported by other DBMSs or by many languages and tools. Again, there may be some cost to convert.

Limited to a fixed precision of either 2 or 4 decimals.

The potential benefit of using MONEY is that you'll save up to one byte for some values. You'll never save more than one byte per row and on average you'll generally save a lot less than that. The space saved is probably negligible in most cases but if you think you'll benefit from it then this is a reason in favour of using MONEY.

DECLARE
 @mon1 MONEY,
 @mon2 MONEY,
 @mon3 MONEY,
 @mon4 MONEY,
 @num1 NUMERIC(19,4),
 @num2 NUMERIC(19,4),
 @num3 NUMERIC(19,4),
 @num4 NUMERIC(19,4);

SELECT
 @mon1 = 100, @mon2 = 339, @mon3 = 10000,
 @num1 = 100, @num2 = 339, @num3 = 10000;

SET @mon4 = @mon1/@mon2*@mon3;
SET @num4 = @num1/@num2*@num3;

SELECT @mon4 AS money_result,
 @num4 AS numeric_result;


money_result          numeric_result
--------------------- ---------------------------------------
2949.00               2949.8525

(1 row(s) affected)
0
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

Author Comment

by:robertkc
ID: 24402492
I can see the problem, now.

So what do i store money and small money as
both as numeric(19,4)?

0
 
LVL 22

Expert Comment

by:dportas
ID: 24402521
Use a numeric that's big enough for the largest value you expect to need. 15 digits seems pretty exceptional for most applications.
0
 

Author Comment

by:robertkc
ID: 24403126
hi dportas

just wanted to ask how come you choose numeric datatype, why not decimal?

both will work, so why have two types?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 24403295
From BOL:
<quote>
The SQL-92 synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal.
</quote>

In other words, in T-SQL there is no difference between decimal and numeric.
0
 

Author Comment

by:robertkc
ID: 24403543
<quote>

This is what then SQL2003 standard (§6.1 Data Types) says about the two:

 <exact numeric type> ::=
    NUMERIC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | DECIMAL [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
  | SMALLINT
  | INTEGER
  | INT
  | BIGINT

 ...

21) NUMERIC specifies the data type
    exact numeric, with the decimal
    precision and scale specified by the
    <precision> and <scale>.

22) DECIMAL specifies the data type
    exact numeric, with the decimal scale
    specified by the <scale> and the
    implementation-defined decimal
    precision equal to or greater than the
    value of the specified <precision>.

</quote>
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24403565
I am not exacly sure what you are trying to state.  But let me repeat what I stated previously:
In MS SQL Server there is no differences between numeric and decimal. Period.
0
 

Author Comment

by:robertkc
ID: 24405197
@dportas
Here is a solution to your example!

DECLARE
@mon1 MONEY,
@mon2 MONEY,
@mon3 MONEY,
@mon4 MONEY,
@num1 NUMERIC(19,4),
@num2 NUMERIC(19,4),
@num3 NUMERIC(19,4),
@num4 NUMERIC(19,4);

SELECT
@mon1 = 100, @mon2 = 339, @mon3 = 10000,
@num1 = 100, @num2 = 339, @num3 = 10000;

set @mon4 = @mon1*@mon3/@mon2
SET @num4 = @num1/@num2*@num3;


SELECT @mon4 AS money_result,
@num4 AS numeric_result;

money_result          numeric_result
--------------------- ---------------------------------------
2949.8525               2949.8525


Rule: Always perform the order of operations as follows addition, subtraction, multiplication and division.
(As you can see division is always last!) :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question