Solved

data modeling a double entry accounting system

Posted on 2009-05-14
10
3,205 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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

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

Suggested Solutions

Title # Comments Views Activity
convert in derived column 7 30
Text file into sql server 5 28
Passing Parameter to Stored Procedure 4 24
MSSQL Import .SQL files - Increase Import Speed 9 42
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

803 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