Multiple entries

If you are inserting a set of records in a table, it could have triggered an auto-insertion in another tables,
or few tables simultaneously. This explains the duplicating records captured in different tables.
The case is even more true to an accounting system, as a set of accounting tables is linked together via
interfacing with a number of third-party applications, thus giving an impression of a denormalised structure.

For intance, you may purchase a GL module from a vendor, but a Cash module from a different vendor, and
there is a common referencing field that connects these two modules in some way, in order to run your
accounting system as one integrated system. I came across an interesting book
(title : Accounting Database Design)from smashwords, and i think if you go thru the contents, it would clear some of your thoughts. It covers some accounting module design and query. The book is free for download.

> On Wednesday, September 12, 2007 9:37 AM vovan wrote:

> I'm starting an accounting project.
> I think I have to create a dataflow diagram first, then create the database
> structure.
> I'm the beginner in both areas - databases and accounting. But I leearned
> something already in the database area.
> Normal rules do not allow to store the same value in several places.
> For instance bank deposit for $1234.56 must be stored in one table in one
> field.
> I analyzed several existing accounting databases and realized that this is
> not the case for all of them. The value $1234.56 is stored in many places
> after I entered a single deposit via the program interface.
> I played with QuickBooks (I used the driver for QuickBooks database), with
> Microsoft Small Business Accounting (SQL Server database is open for the
> developer) and some other programs.
> Why all the databases I analyzed look like they are not normalized?
> Due to the huge number of database objects (Microsoft SBA for instace uses
> more than 500 tables) I wouldn't want to use existing database structures
> for my project.
> Can anybody explain the reason of using not normalyzed databases for
> accounting programs? And can somebody suggest the structure for normalized
> database for accounting? WEB site, book, somebody's product with a source
> code?
>
> Thank you
> vovan


>> On Wednesday, September 12, 2007 9:46 AM Dave Ballantyne wrote:

>> vovan wrote:
>>
>> It all boils down to needs and book keeping standards
>>
>> http://en.wikipedia.org/wiki/Double-entry_bookkeeping_system
>>
>> It sounds like you are attempting something a little ambitious if youre
>> a "beginner in both areas - databases and accounting".
>>
>> Good luck though


>>> On Wednesday, September 12, 2007 9:54 AM Jimb wrote:

>>> Since accounting has been around for a long time, there are certain basic
>>> book keeping principles that must be followed - how they are translated to
>>> computers varies wildly across different products.
>>>
>>> If you are designing a complete accounting system then it is a massive task
>>> - if you can, buy an existing system and implement it.
>>>
>>>
>>>
>>> "vovan" wrote:


>>>> On Wednesday, September 12, 2007 10:28 AM vovan wrote:

>>>> I already bought one from PCS Still and have the same problems there. I also
>>>> bought less expensive MS SBA and downloaded an SDK. Same stuff - the same
>>>> value is stored in 12-16 places. I wrote SP which can return the name of the
>>>> table along with the name of field where a particular value is stored. So,
>>>> I'm doing some entry via interface (for instance deposit check with the rare
>>>> value, as I said before for $1234.56 or create an invoice with unusual note,
>>>> like "TttHhhis is a test Innnvvvoice") then using that SP I get a list of
>>>> [TableName].[ColumnName] where those values are stored. And if I can accept
>>>> somehow that there is a real need to store $1234.56 in many places, but I
>>>> cannot understand at all why "TttHhhis is a test Innnvvvoice" is stored in
>>>> 16 places in different tables and different columns.
>>>>
>>>> Thank you
>>>>
>>>> "Jimbo" wrote in message
>>>>


>>>>> On Wednesday, September 12, 2007 10:30 AM vovan wrote:

>>>>> Maybe not really a beginner, but too far from to be an expert. 10 years
>>>>> experience, but with SQL Server I work not to often. Accounting is a new
>>>>> area for me.
>>>>> Thank you


>>>>>> On Wednesday, September 12, 2007 10:42 AM Jimb wrote:

>>>>>> Obviously the tables you describe are badly designed, but if the product
>>>>>> works reliably then who cares?
>>>>>>
>>>>>> But for you it is a nightmare to try to reverse engineeer a product from
>>>>>> such databases.
>>>>>>
>>>>>> The real question is - have you chosen the correct solution by following the
>>>>>> path you have chosen?
>>>>>>
>>>>>> Unless you are designing a very small part of an accounting system the
>>>>>> answer has to be No.
>>>>>>
>>>>>>
>>>>>>
>>>>>> "vovan" wrote:


>>>>>>> On Wednesday, September 12, 2007 11:38 AM Chri wrote:

>>>>>>> Hi,
>>>>>>> Try getting hold of Mr.Joe Celko's books. Has some great concepts, not
>>>>>>> exactly relating to Accounting but general.
>>>>>>>
>>>>>>> "vovan" wrote:


>>>>>>>> On Wednesday, September 12, 2007 12:01 PM Maurice Telkamp wrote:

>>>>>>>> Vovan,
>>>>>>>>
>>>>>>>> I've come across many bad database design in my life. But the information
>>>>>>>> you provide is too little to label this database as "bad".
>>>>>>>> Reasons why non-normalized databases exist are for example:
>>>>>>>>
>>>>>>>> history: I've seen database in SQL2005 that are (almost) identical to a
>>>>>>>> database x versions ago when they were implemented in dBase or Paradox.
>>>>>>>> Probably for commercial reasons or lazy developers, they never adapted to
>>>>>>>> new standards and/or capabilities. The same is true for developers who
>>>>>>>> incist on being database-independant (such a non-reason if you ask me)
>>>>>>>>
>>>>>>>> commercial: it's often too expensive to investigate better designs or to
>>>>>>>> hire better developers. (again a bad reason, but sadly true)
>>>>>>>>
>>>>>>>> performance: this is actually a valid reason. Sometimes performance can
>>>>>>>> increase dramatically by de-normalizing. (call it cache, call it
>>>>>>>> datawarehouse, whatever)
>>>>>>>>
>>>>>>>> It is of course also possible that your analisys was incorrect. Perhaps the
>>>>>>>> values you mention appear similar, but are in fact two different values.
>>>>>>>>
>>>>>>>> The bottomline however is that you need to think for yourself (as you did by
>>>>>>>> asking this question...), and not follow an example for which you don't know
>>>>>>>> (can't know) the underlying reasons.
>>>>>>>>
>>>>>>>> Hope this helps,
>>>>>>>> Maurice.
>>>>>>>>
>>>>>>>>
>>>>>>>> "vovan" wrote in message
>>>>>>>>


>>>>>>>>> On Wednesday, September 12, 2007 4:49 PM david wrote:

>>>>>>>>> It's been a while but I did accoutning work for 8 years way back when
>>>>>>>>> and 16 places is not bad for storing the same type of information.
>>>>>>>>> It's the same value but used differently. Partly has to do with
>>>>>>>>> Generally Accepted Accounting Principles (GAAP) and how things are
>>>>>>>>> entered and tracked for General Ledger purposes. You have to reduce
>>>>>>>>> your cash account, increase at least one expense account, probably
>>>>>>>>> reduce accounts payable if the check was to pay and invoice/bill. Then
>>>>>>>>> you need to make your entries for open transactions and closed
>>>>>>>>> (cleared the bank or not), etc. etc. etc.
>>>>>>>>>
>>>>>>>>> it may seem denormalized at first, but its probably not for reasons
>>>>>>>>> that are not clear to you yet. You'll understand as you develop your
>>>>>>>>> system.
>>>>>>>>>
>>>>>>>>> David


>>>>>>>>>> On Sunday, October 19, 2008 7:40 AM Scott wrote:

>>>>>>>>>> Old post but this may help:
>>>>>>>>>>
>>>>>>>>>> I am an accountant and Software developer:
>>>>>>>>>>
>>>>>>>>>> Database performance may increase by this method or over the years an update may have been needed but changing the inital db tables wasn't an option so extra fields/tables added.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> For accounting:
>>>>>>>>>>
>>>>>>>>>> Each transaction has a double entry so Cr Income and Dr Debtors. When this is paid another 2 entries Cr Debtors Dr Bank. Also most databases have a valid checker so you may have a table that just lists all transactions so the db can check against this to ensure all records are there. Some systems like QB you can turn on an Audit feature, this could cause more entries as it logs the details of any change to a transaction so just a text change could reproduce more entries.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> I'm sure they have their reasons, but just make sure you db is efficient and expandable.
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>>
>>>>>>>>>> Regards
>>>>>>>>>>
>>>>>>>>>> Scott
>>>>>>>>>>
>>>>>>>>>> www.parkdrivesolutions.co.uk


>>>>>>>>>>> On Monday, October 25, 2010 2:25 AM Rob Thomson wrote:

>>>>>>>>>>> If you are inserting a set of records in a table, it could have triggered an auto-insertion in another tables, or few tables simultaneously. This explains the duplicating records captured in different tables for item 1234.56.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> The case is even more true to an accounting system, as a set of accounting tables is linked together via interfacing with a number of third-party applications, thus giving an impression of a denormalised structure. For intance, you may purchase a GL module from a vendor, but a Cash module from a different vendor, and there is a common referencing field that connects these two modules in some way, in order to run your accounting system as one integrated system. I came across an interesting book (title : accounting database design)from smashwords.com that i think would clear some of your thoughts. It covers accounting modules design and some query.The book is free for download.


>>>>>>>>>>> Submitted via EggHeadCafe - Software Developer Portal of Choice
>>>>>>>>>>> SharePoint Lists In Excel Via VSTO
>>>>>>>>>>> http://www.eggheadcafe.com/tutorials/aspnet/b56c03b3-35ea-43c5-8b87-2f...518377c
 >> Stay informed about: Multiple entries