Solved

data modeling a double entry accounting system

Posted on 2009-05-14
10
3,202 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
  • 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
 

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

929 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now