Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

data modeling a double entry accounting system

Posted on 2009-05-14
10
Medium Priority
?
3,236 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 1600 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 400 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

604 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