Suggestion required for Accounting Software core infrastructure

[ahmed_one]

Dear All,

I am on the beginning stage on C# development. Currently I am developing an Voucher based Accounting Software with following:

1- Visual Studio 2010
2- Programming Language: C#
3- Database: MySql 5

At present, Database structure is as follows:

1.accAccounts (For holding information about Chart of Accounts like Account Code, Name, Subsidiary or Detail, Opening Balance etc)

2.vMaster (For Vouhcer Master information, Like Voucher Number, Date, Voucher Type (i.e Payment/Receipt/Journal), Payee Name, Remarks, Debit Account, Credit Account, Amount)

3. vDetails (For Voucher Detail information, used for further allocation of Expenses/Assets uses AccCode, Debit Amount, Credit Amount)

4. TranPost (All transactions from vMaster and vDetails will be posted in this table)

The General idea is this: User create new Voucher, enter transactions for Payment/receipt/journal etc. Then save the voucher. After approval of supervisor, Voucher will post and all transactions were copied to TransPost, once posted vMaster and vDetails were restricted to further editing.

Ofcourse when data is available in TransPost table, generation of Trial Balance, Balance Sheet & Income Statement will be based upon it.

Now my Question is: Is this the correct approach for development of Accounting Software? I am mainly concern about Year end closing process. Should I use same vMaster/vDetails/TransPost tables for every Accounting period or should I create new tables for each period? Assuming 50-80k transactions per year. What is the recommended way? For accMaster I think it should be the global table for All accounting periods, if yes, then it needs also in Master/Detail structure for holding different Opening balances for each year.

As mentioned, I am a beginner and what I've learned so far is by 'learn-by-doing' approach and guidance of all experience programmers in the forums like this. So I am in need of guidance again.

Looking forward for suggestions from all Masters specially those who are in development of Accounting Softwares.

Thanks in advance

Ahmed

 

[jmcilhinney]

Should I use same vMaster/vDetails/TransPost tables for every Accounting period or should I create new tables for each period?

Absolutely you should use the same tables. You always use the same table for the same data and if you need to be able to filter the data by date then you add a date column. If you want data for a single financial year then you specify that date range in the WHERE clause of your query.

 

[ahmed_one]

Absolutely you should use the same tables. You always use the same table for the same data and if you need to be able to filter the data by date then you add a date column. If you want data for a single financial year then you specify that date range in the WHERE clause of your query.

Thanks a lot for prompt response jmcilhinney, but I am wondering about the data volume say 50-80k per financial year. If we use same tables, doesn't it make processing slow at some stage lets say after 4-5 financial year data?

How about I add a column for Financial Year instead of date field, and tag every transaction with financial year like 2008-09, 2009-10 etc?

 

[jmcilhinney]

Thanks a lot for prompt response jmcilhinney, but I am wondering about the data volume say 50-80k per financial year. If we use same tables, doesn't it make processing slow at some stage lets say after 4-5 financial year data?

How about I add a column for Financial Year instead of date field, and tag every transaction with financial year like 2008-09, 2009-10 etc?

Yes, that would work. I'd probably simplify that down to just one number and put an index on that column. You could basically do both, i.e. have a date field and a fiscal year field and make the second column calculated from the first. I'm not sure about MySQL but SQL Server allows you to specify that calculated values are persisted. You'd do that for values that won't change after the initial insert, which would be the case here.

 

[ahmed_one]

Yes, that would work. I'd probably simplify that down to just one number and put an index on that column. You could basically do both, i.e. have a date field and a fiscal year field and make the second column calculated from the first. I'm not sure about MySQL but SQL Server allows you to specify that calculated values are persisted. You'd do that for values that won't change after the initial insert, which would be the case here.

Thanks again for your reply.

I will definitely follow your guidelines. But what about data volume? if we use one table for multiple years transactions, assuming 50-80k transactions per year, would it cause any slow processing for database operations?

 

[jmcilhinney]

Thanks again for your reply.

I will definitely follow your guidelines. But what about data volume? if we use one table for multiple years transactions, assuming 50-80k transactions per year, would it cause any slow processing for database operations?

Enterprise-grade databases like MySQL can handle millions of records.

 

[ahmed_one]

Enterprise-grade databases like MySQL can handle millions of records.

Awesome....


Once again thanks for great guidelines.

Best regards

Ahmed

 

[ahmed_one]

Hi, In continuation with this thread, I've now add a Table FYear with ID, YearDesc fields. Is it possible to use something like a application level/Global variable in which I can save ID of a particular Year and insert it with every transactions created?

My idea is:
Enter multiple Financial year in FYear Table like:
ID YearDesc
1 2007-08
2 2008-09
3 2009-10
4 2010-11
5 2011-12
etc

And create a login screen where user can enter Username/password while also selecting Financial Year/ID from combobox. Then in the application, this Fin Year/ID will be used to validate every entries alongwith insertion of records with user selected year?

Thanks

Ahmed

 

[jmcilhinney]

Do you really need a table for financial year? Can't you just store a year in the other table?

 

[ahmed_one]

Do you really need a table for financial year? Can't you just store a year in the other table?

So that User can select Financial Year from combobox and Admin can Active or In-Active mark to restrict access to data entry user for mistakenly enter in wrong Financial year.

This is why I've created a table, and need a global/application level variable to store year id when user select it from combobox.