Coding an accounting database from scratch? [closed]

Posted on

Question :

My new medical practice is picking up fast and my homemade Libreoffice Calc spreadsheet is quickly becoming cumbersome to track patient visits, accounts receivable, overhead reconciliation, etc. Most practices use some sort of practice management software to handle accounting as well as scheduling, paying bills, submitting insurance claims, etc. but my budget can’t afford to pay for overpriced software. Besides, I like to do things my own way, as well as support open source software. (I’ve already determined that I don’t need this function to come from my electronic charts)

Google searching didn’t yield any templates and all of the accounting programs found were built for traditional businesses with purchasing, warehousing, human resource management, etc. that aren’t adaptable for my needs and are unusable. You wouldn’t think that I would be the first physician to attempt something like this, but the only thing that I could find is a design plan here:

So now, I’m considering importing my spreadsheet into Libreoffice Base and slowly developing a custom accounting program for myself. However, I have no experience with databases and I don’t want to spend a large amount of time on this if the amount or complexity of coding is beyond what I am willing to sacrifice from my study time. After browsing through textbooks at the local B&N, the first 1/3 of most books on MySQL, Filemaker Pro, and Access seem applicable for what is needed (complex mathematical modeling and data analysis is overkill), but I’m still hesitant about heading into this endeavor if the work load is larger than I care to handle.

So, is it doable for someone with remote programming experience (qbasic, visual basic) and basic spreadsheet knowledge to code a simple custom accounting database? If so, is LibreOffice Base sufficient? What program or textbook would you recommend?

Thank you!

Answer :

I have used almost all accounting software, currently use and modify Sql-Ledger – and so turning my database client’s workflow system into a full, double-entry accounting system was so easy it was almost a relief; client had been using my system to bill their own (news-copy) clients because their internal itemisation of services spoke their own language whereas no accounting package would. Their accounts section then had to go and reproduce it all again externally for book keeping.

Finally the client’s accountant and I sat down and exchanged notes. I modified the application within a week. It has never broken, produced a bug, or failed. It does all their accounts and no none else’s. It tracks assets, cash flow, everything except payroll – which they didn’t need.

Every business of any size knows, you just can’t get anything off the shelf that works.

My advice is don’t listen to the doom-and-gloom advice of anybody who doesn’t understand both database schema and accounting. This combination seems to be just unusual enough to have software vendors rubbing their hands with glee, issuing warnings of dire consequences to would-be developers, spending most of their time organising vendor-lockin components to their software, or arrangements with large accounting franchises. And, like successful snake-oil vendors, they laugh all the way to the bank.

You are going to need to do some heavy reading.

Solid data modeling books by old warhorses that actually talk about accounting:

Martin Fowler, Analysis Patterns

Hay, Enterprise Model Patterns

But it’d probably be easier to use an open source ERP like OpenERP

I would recommend going one of two directions.

The first, if you want to get into building accounting software, but are really in the business of making money as a doctor, is to get involved in an open source project, like LedgerSMB. The advantage is that if you really want to do this as a way to help support your business, you can learn as much as you want to (or as little as you want to also) and there are experts you can learn from in a hands-on way. Also keep in mind that Ohloh puts LedgerSMB as approx 40k lines of SQL code (excluding blanks and comments), plus 49k lines of Perl, plus a similar number of lines in a bunch of other languages combined. Accounting software is not trivial. The second advantage here is that it gives you live participation with a community of people who are doing this and so you get a chance to make the software do exactly what you want. You can then spend your copious free time integrating an EMR with it (or hire someone to do so) ;-).

The second option you have is to buy a commercial software and just integrate what you are doing there business-process-wise. The advantage here is you can buy some practice management software that will handle appointment tracking, billing, and the like or you can spend less and go with something like Quickbooks. This gives you some choice but also a lot of tradeoffs and very little flexibility once tradeoffs are committed to.

TL; DR: If you want to get into the field, join with people who are doing it. If you just want software, you can buy it.

Accounting is very complex and is mission critical for any business. The most central piece of an accounting system is the general ledger. Building your own G/L is pretty hard to justify. You should buy a well-supported desktop G/L, like QuickBooks. There are some good, cost-effective choices available.

The other functions in your business, even ones that are related to accounting (outside of G/L) can be specialized. Focus on custom-building these areas, if you find that a general purpose package really doesn’t handle your needs.

I have built customized subledgers for areas like cost accounting and profitability reporting for businesses with highly specialized needs, but they still used off-the-shelf G/L software. You won’t necessarily end up with a perfectly suited, fully integrated custom system, but you don’t have time to build such a system and still operate your practice.

So, is it doable for someone with remote programming experience (qbasic, visual basic) and basic spreadsheet knowledge to code a simple custom accounting database? If so, is LibreOffice Base sufficient? What program or textbook would you recommend?

I have a different opinion. As long you are willing to put in the time and effort, you can build a simple accounting application. I’m not talking about developing a high-end application that has all the necessary features like you can find in an ERP software. Yes, visual basic is one programming language which I have personally adopted as the front-end GUI in building a simple accounting system. I’m not sure if LibreOffice base is good enough, but what you need is to have some good grasp on database designing, in order to build a robust and scalable database system. Next, you need to understand the concept of double-entry system, which is your system check and balance. I do not have a specific program to recommend, but you may download a free ebook from that will provide you the necessary understanding of the basic accounting database designing concept and the double-entry rules which makes all your reports and entries makes sense.

Leave a Reply

Your email address will not be published. Required fields are marked *