Core Acct'g Des. 1

[TallOne]

Hello Everyone,

I'd like to implement an accounting system with my programs and would like some input from someone more experienced than myself! Any advice of how you design compared to what I'm doing would be greatly, greatly appreciated.

Requirements: Accounts Receivable, Accounts Payable, General Ledger(Double-Entry), Company, Division. I need to be able to post and match partial/full payments/receipts to AP/AR. I need to see beginning balance(monthly) and monthly closings while updating the new beginning balance. And there's alot more but I need to get a solid core accounting design first with the minimums and work from there. I want to start off on the right foot first though and would enjoy any opinions! So here's what I'm thinking so far based on what I've seen and read. Please note the table and fieldnames are for clarity and not to be construed as my actual naming conventions

tblMainAccout
ActId int No Duplicates
ActNum int No Duplicates
ActName char No Nulls
ActBal money No Nulls Default 0
FK_ActTypeID -normal balance Debit/Credit
FK_ActCodeID -Asset,Liability,Revenue,Expense,Equity

The SubAccount table will be updated by the SubAccount monthly. After the update that's the only exact point in time they are identical in that total debits/credits = the subaccount total debits/credits. New transactions for the month are only updated to the tblsubaccount and this table will contain the current balace at any point in time. I think this way I can get a beginning and current ActBalance without having to recalculate, easier reporting, and just a checks and balance system that I may verify at any given point... etc. even though it violates normalization.

tblSubAccout
ActId int NO Duplicates
ActNum int No Duplicates
ActName char No Nulls
ActBal money
FK_ActType -normal balance Debit/Credit?
FK_ActCode -Asset, Liability,Revenue,Expense,Equity

How I see it is..The MainAccount[ActNum] field can contain only numbers in a valid range for the chart of accounts say 10000000 - 9999999. All Account numbers added to the MainAccount will be added to the SubAccount table also. The difference being in the sub account table, additional actnum (like in AP VendorAccount#, in AR CustomerAccount#, JC/job costing#, etc) can be added outside(and inside for subsidiary G/L accounts) of this range but in a child relationship with the actnum being the sum of all child balances.
So I guess you could say the SubAccount is the current detail(and at month-end exact detail) of the MainAccount. With me? I'm still trying to picture this myself.

Now for the transactions that make up the ActBal. I'm thinking like an invoicing system where an invoice object is made up of a headertable and so I create one for the accounting transaction.

tblTransactionHeader
PK_HeaderId int
Date -DateTime/Transaction Date
Code -Journal Code-AP,AR,JC(JobCost),PR(PayRoll),GJ(GenJournal)
Reversing -Boolean
Description varchar

In the most simple transaction, there should be at least two items (one debit and one credit) that update their respective accounts. The relationship will be one HeaderId to many ItemId's. So I have table like this.

tblTransactionItems

PK_TransactionItemID int
FK_HeaderID int
Amount money
DueDate date/time Nulls allowed
FK_tblSubAccount/ActID
FK_tblSubAccount/ActID

So now I can add multiple-account transactions for one accounting process such as Sales. For example a non-inventory credit sale of $10 with 5% tax. Credit ActId for Sales $10, ActId for SalesTax Payable $.50, Debit ActId for Accounts Receivable $10.50. Three transaction items related to one Transaction Header. Let's say the very next transaction is a payment received from the customer for $10. I would create a new transaction with the two items- debit to the cash ActID and a credit to the customers Acccounts Receivable ActID (which should be a child of a Parent Accounts Recievable SubAcount/ActId) each for $10. After a few sales assume the customer pays taxes on some sales and not others, by looking at the data so far I have no Idea which transaction matches with the others. If other words, which payment is paying off which debt? And on the Payables side which debt is being exausted by which payment.

So an additional table for the core accounting design would be a table that matches payments to debts whether AP or AR like so..

tblTransactionMatching
FK_TransactionItemID/Payment
FK_TransactionItemID/Debt
Amount Money >0

Entries in this table would follow: For AP the FK_TransactionItemID/Payment would point to the current TransactionItem for cash going out and FK_TransactionID/Debt would be a historical TransactionItemID for the accrued debt. The amount is the portion of debt being extinguished; For AR the FK_TransactionItemID/Payment would be the current TransactionItem for cash coming in and FK_TransactionID/Debt would be a historical TransactionItemID

Does this sound like a solid core design or close to other industry standard core accounting designs? If so/not do you have any suggestions?

How would I implement multiple companies that have any number of divisions without having to create individual MainAccount and SubAccount tables for each?

Any suggestions or discussions are welcomed and encouraged. Thanks.

 

[lespaul]

My advice is going to be: buy a canned system.

I worked as an accountant for 10 years before moving to IT and becoming a programmer and I wouldn't design an accounting program!!

Both Peachtree and Quickbooks are designed to be very flexible. They both have many Chart of Accounts to select from or you can design your own. I know that Peachtree allows you to have different departments within an account (like 60000.100, 60000.200, 60000.300 would be the 60000 account for 3 different departments so you can run financial reports for all departments or by department).

I personally prefer Peachtree, but it's designed more for accountants to use/setup/support for their clients while Quickbooks is geared more towards the small business owner who really doesn't want to understand how it all works, just wants it to work.


Leslie

 

[SQLSister]

I second Leslie, accounting business rules are extremely complicated. And subject to change each year. Cheaper to buy a system than develop it, by far. And it's a business critical function, a mistake in programming could bankrupt a company or cost big bucks in legal fees.

Questions about posting. See faq183-874

 

[flugh]

I have been working on an Access database for the last couple years, modifying it as needed, to generate billing and payroll for a small company. And after seeing an experienced bookeeper come in here and have her way with Quickbooks, I strongly agree with the 'buy a canned system' suggestion if you need mission-critical performance (and money always is, isn't it?). Ever try to debug a few lines of code while the boss is hovering over your shoulder waiting for an amount for a check? Yeesh!!

----
JBR

 

[bzsurf03]

I agree with lespaul. What advantage is the company going to have after developing the accounting system in house. Everything you described is covered in the simplest of off-the-shelf apps. Put your time and resources into places where your company needs you most (where you gain a competitive advantage).

 

[willir]

TallOne

Leslie and I agree on this -- in the past and today -- it may be far more effecient and less costly to purchase a financial application than develop your own system.

To justify the expense, risk, liability and time spent on developing your own system...
- How many transactions will you process in a year? Will an existing system support the same * 5 to seven years?
- Is your system in the "ball park" of Quick Books, Peach Tree (less than $200), AccPac (less than $10,000 with the LAN package), or Oracle or SAP - big bucks here? Note that QuickBooks and Peach Tree have a very strong flavour for a relational database. Oracle and SAP are obviously relational.
- What advanatage do you gain by developing your own system? Additional functionality, unique situation(s) for your type of business? Feather in your hat? Resell your applications to others?
- A financial system is subject to legislation. Are you willing to be liable for accounting mistakes? Can you "prove" to creditors, banks and the government that your system has integrity? I am hoping an audit will suffice, but you may want to follow up on this issue. Remeber that not only do you have to produce balanced books, but I suspect you also have to demonstrate that the "books" could not be "edited" after the fact.
- Do you have the time and expertize to develop the system? The design will be fairly straight forward. The real work is coding the data flow of the GL transactions.

...Moving on
I actually like your design. But I do have some questions?

Is your subledger akin to the invoice header / invoice detail? If so, then a more typical approach would be to have ...

tblMainAccout
ActId long int - primary key, No Duplicates
ActNum - char No Duplicates
ActName char No Nulls
...

tblSubAccout
SubActID long int - primary key
ActId long int - foreign key to tblMainAccout
SubActNum char No Duplicates
...

IF the SubAccount table is not like a child table, then do you really need to create two tables for Account / SubAccount? I strongly suspect you can maintain the same heirarchy as an account / subledger, and perhaps even more easily.
[tt]
Account SubAccount
1000100 001001
1000100 001002
1000100 001003
1000110 001001
1000110 001002[/tt]

Add a balance file which captures balances for year + period...

tblBalance
ActID - foreign key to tblMainAccout
Year - char or int
Period - char or int
Balance

Primary key = ActID + Year + Period

Why? When you close a month or year, the number should not change - hence storing the balance can save a tremendous amount of CPU time recalcuating 99,000 transaactions. But storing one balance on the Accout or even your subaccount table will be insufficient. How will you calculate / review last year's balances? Or last quarter, etc...

Add an Amin table to capture various soft coded entries...

tblAdmin
AdminCode - primary key
AdminText - char field, various needs
Comment

How will you identify various special accounts?[tt]
AdminCode AdminText
TRADEPAY - 2000100-001001 (GL Trades payable)
BANKACCT - 1000500-001001 (GL Bank account)
Bank - 5551212 (Bank Account)
FEDTAX - 20005000001001 (GL Tax Account)
TaxRef - 11234455 (Fed Tax Account)
SALESTAX - 5 (Sales tax rate)[/tt]

...sort of a catch all for specific GL accounts and important "numbers", and whatever else.

A better design for this table would be to have
[tt]
AdminType AdminCode AdminText
GL TRADEPAY - 2000100-001001
GL BANKACCT - 1000500-001001
GL FEDTAX - 20005000001001
TAX SALESTAX - 5
ACCT Bank - 5551212
ACCT TaxAcct - 11234455 [/tt]


Transactions

tblTransactionHeader
TransID
PK_HeaderId int
Date -DateTime/Transaction Date
PostYrPeriod - char or int
Code -Journal Code-AP,AR,JC(JobCost),PR(PayRoll),GJ(GenJournal)

You need to capture what period a transaction is posted to. Several things happen here... The transaction date should be the actual date the transaction occurred on. The posting period can be the same or different (month end, year end, or accrual) Obviously, if you post to a past period, balances and month end / year end have to be re-done.

DueDate - should this be captured in the transaction file, or in the Invoice file? I do not feel comfortable seeing a DueDate in the transaction table.

The tweaks go on.

As I said, I do like your design - well thought out. But I hope you can see that it can still benefit from tweaking. Tweaks which can be found in shrink wrap applications.

Richard

 

[TallOne]

Hi Everyone!
Please accept my apology for the timeliness of this repsonse. To be quite honest I still havent decided which direction to go. I'm in no hurry as this is a side project for me. I'd like to thank everone for their response with special thanks to Richard. I'm aware of the accounting software packages available, and without debating their usefulness in the marketplace, suffice it to say I'm moving forward with my own core design and would appreciate any input from all who's viewing this post. Using your feedback, which I do appreciate, I've tweaked my core structure like this.

Yes the subledger is akin to the invoice header / invoice detail. So here's what I got:

tblMainAccout
ActId long int - primary key, No Duplicates
ActNum - char No Duplicates
ActName char No Nulls
FK_ActTypeID -normal balance Debit/Credit
FK_ActCodeID -Asset,Liability,Revenue,Expense,Equity
...
tblSubAccout
SubActID long int - primary key
ActId long int - foreign key to tblMainAccout
SubActNum char No Duplicates

I definately needed the fiscalperiod field Thanks! I'm not sure why you added TransID

tblTransactionHeader
PK_HeaderId int
Date -DateTime/Transaction Date
FiscalPeriod -char fiscalyear/fiscalmonth
Code -Journal Code-AP,AR,JC(JobCost),PR(PayRoll),GJ(GenJournal)
Reversing -Boolean
Description varchar

The only question I have in taking your advice about moving the DueDate field is that I'm wondering if it's more related to the transaction rather than the invoice/purchaseorder. The core systems seems more self contained this way...I'm still debating this one though.


tblTransactionItems
PK_TransactionItemID int
DueDate date/time Nulls allowed
FK_HeaderID int
FK_tblSubAccount/ActID
FK_tblSubAccount/ActID
Amount money

tblTransactionMatching
FK_TransactionItemID/Payment
FK_TransactionItemID/Debt
Amount Money >0

The addition of this table is very much welcomed in the design. Thank You! Now to verify the integrity of the ledger(other than debits=credits) I can add all transactions for the fiscal period + last fiscal period's ending balance and compare to current ending balance. Prior period financial statements can be more easily accessed without countless computations. Thanks.

tblBalance
ActID - foreign key to tblMainAccout
FiscalYear - int
FiscalPeriod - int, 1-12
Balance


AdminCode AdminText
TRADEPAY - 2000100-001001 (GL Trades payable)
BANKACCT - 1000500-001001 (GL Bank account)
Bank - 5551212 (Bank Account)
FEDTAX - 20005000001001 (GL Tax Account)
TaxRef - 11234455 (Fed Tax Account)
SALESTAX - 5 (Sales tax rate)

>How will you identify various special accounts?
I'm mainly working on the core structure for now, but here's a few thoughts. I think that most of these issues are not dependent upon the core structure. For example SalesTax rate will be stored in an updateable tax table. There are other issues related to taxing that will require other tables. For example labor, taxable in one city, county, state but not taxable in another. Is material sales taxable? What if the material is used to improve real estate? Does the program charge sales tax on the invoice or accrue sales tax payable? Which state do you withhold state income tax? These things will not be hard coded in the program. So there will be a tax rules table that stores what product types are taxable in the specific local,county,state and maintained by the administrator. FEDTAX, BANKACCT, TRADEPAY? I'll have a generic G/L Schema from which a customer may choose. When AP or Cash Reciepts people make entries they will have the option to post to the account of their choice as long as it's ActCode is Liab, likewise with posting a lockbox deposit or the such for AR. I do like the catch all admin table though and will use it.

<The real work is coding the data flow of the GL transactions.

I agree. That's why I want to make sure this "core" is selfcontained(encapsulated if you will) and done right the first time. I only want to write this once and reuse the code.

Final note:
Special thanks to you Richard! Bravo! And once again sorry about late posting. I'm still pondering the inclusion of Company/Division and how to incorporate it into the design. It's gotta be there! Anyone got any ideas?

[Underline]tblMainAccout[/Underline]
FK_CompanyID
FK_DivID
ActId int No Duplicates
ActNum int No Duplicates
ActName char No Nulls
ActBal money No Nulls Default 0
FK_ActTypeID -normal balance Debit/Credit
FK_ActCodeID -Asset,Liability,Revenue,Expense,Equity

Primary key = FK_CompanyID + FK_DivID + ActNum????? How does this affect tblSubAccount??

 

[ceh4702]

Maybe you should find a class somewhere on Accounting Packages like Quickbooks. This can let you see what it is like. You may find a salesman of canned products that will let you have an evaluation copy. I dont know how much quickbooks costs but it is a pretty popular product.

If you do not like my post feel free to point out your opinion or my errors.

 

[TallOne]

Hi ceh4702,

Thanks for the reply. I know what Quickbooks looks like and how to use it. I don't believe a class in Quickbooks will help my query, unless they teach accounting database design.
Later