Solved

data modeling a double entry accounting system

Posted on 2009-05-14
10
3,197 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Use a numeric that's big enough for the largest value you expect to need. 15 digits seems pretty exceptional for most applications.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:robertkc
Comment Utility
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
Comment Utility
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
Comment Utility
<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
Comment Utility
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
Comment Utility
@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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

771 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

10 Experts available now in Live!

Get 1:1 Help Now