Free Accounting Software

Search Inside Our Free Bookkeeping Software Site









Download This Book


Search Inside This Book


Accounting Database Design eBook



This book will walk you through Step-by-step, employing real-life conventional business processes and book-keeping techniques in developing real-world database engine for an accounting system that are robust, scalable and secured, using Microsoft SQL Server as the development tool. The author discusses the type of accounting modules, table design, entity-relationship and normalization concept.


Table of Content


Chapter 1 : Database Design
Relational Database
Primary Key (PK)
Foreign Key (FK)
Normalization Concept
- First Normal Form
- Second Normal Form
- Third Normal Form
What is SQL?
Installing Microsoft SQL Server (Personal Edition)
Creating a Database

Chapter 2 : Developing the Journals Table
Normalization Journal Table
- First Normal Form
- Second Normal Form
- Third Normal Form
Designing Chart of Account Table
Designing Journal Table
Designing Sales Table
Designing Product Table

Chapter 3 : Developing the Inventory Table
Normalizing Inventory Table
- First Normal Form
- Second Normal Form
- Third Normal Form
Designing Stock Movement Table
Designing Stock Balance Table
Designing Product Account Set Table
Designing Product Category Table

Chapter 4 : Developing the Purchase Table
Normalizing Purchase Table
- First Normal Form
- Second Normal Form
- Third Normal Form
Designing Creditor Table

Chapter 5 : Developing the Sales Table
Normalizing Sale Table
- First Normal Form
- Second Normal Form
- Third Normal Form
Designing Customer Table

Chapter 6 : Developing the Cash Table
Normalizing Cash Table
- First Normal Form
- Second Normal Form
- Third Normal Form
Designing Bank Table

Chapter 7 : Developing the Asset Table
Normalizing Asset Table
- First Normal Form
- Second Normal Form
- Third Normal Form


Chapter 8 : Creating Reports from Journals Table
Using SQL to produce Trial Balance
How It Works - Select Query for Trial Balance Listing
Using SQL to produce Income Statement
How it Works - Select Query for Income Statement
Using SQL to produce Income Statement by Segment
How it Works - Select Query for Income Statement by Segment
Using SQL to produce Balance Sheet
How it Works - Select Query for Balance Sheet
Using SQL to produce Transaction Listing
How it Works - Select Query for Transaction Listing


Chapter 9 : Creating Reports from Inventory Table
Using SQL to produce Stock Movement Report
How It Works - Select Query for Stock Movement Report
Using SQL to produce Stock Ageing & Balance Report
How It Works - Select Query for Stock Ageing & Balance Report


Chapter 10 : Creating Reports from Purchase Table
Using SQL to produce Accounts Payable Ageing Report
How It Works - Select Query for Accounts Payable Ageing Report
Using SQL to produce Accounts Payable Payment Status Report
How It Works - Select Query for Payable Payment Status Report


Chapter 11 : Creating Reports from Sales Table
Using SQL to produce Accounts Receivable Ageing Report
How It Works - Select Query for Accounts Receivable Ageing Report
Using SQL to produce Accounts Receivable Collection Status Report
How It Works - Select Query for Accounts Receivable Collection Report
Using SQL to produce Sales Analysis Report
How It Works - Select Query for Sales Analysis Report

Chapter 12 : Creating Reports from Cash Table
Using SQL to produce Cash Flow Forecast
How It Works - Select Query for Cash Flow Forecast Report
Using SQL to produce Cash Flow Summary Statement
How It Works - Select Query for Cash Flow Summary Statement Report
Using SQL to produce Cash Flow Periodic Statement
How It Works - Select Query for Cash Flow Periodic Statement Report
Using SQL to produce Bank Reconciliation Statement
How It Works - Select Query for Bank Reconciliation Statement

Chapter 13 : Creating Reports from Asset Table
Using SQL to produce Asset Summary
How It Works - Select Query for Asset Summary Report
Using SQL to produce Asset Movement Report
How It Works - Select Query for Asset Movement Report



Purchase from Our Official Site


Enter Name, Date and Time


Below are some commentaries from the author of accounting database design :


How did you get started as a writer?

The idea of writing a book, was not something I had in mind. It started as a coincidence. I have been working as an Accountant for many years. Back then, the computer software, accounting software in particular was revolutionalizing the way accountants work. As I started learning the user manual, which we all do, before we start to operate a machine, I realised there is this sense of  curiosity of understanding the mechanism of the accounting software I was using. That, literally drives me to find out books that touches on Accounting Information System. Unfortunately, it is dissapointing to find most of these books, does not actually explain the mechanism of the functionality of a real accounting system. Most of these books only covers the theoritical concept of it, and not the technical aspect of it. That triggers my instinct to document all these knowledge and experience in a form of a book.


Tell us about your book? What sets it apart from the rest of the Accounting Information Book available today?

If you are looking for one that teaches on real database design and sql programming, then this might be the one you have been searching for.  I given the title : Accounting Database Design, instead of the conventional term of Accounting Information System, as I think it is only logical to use the conventional term, if you are literally talking about accounting system concept and theory. I incorporate the word design, because, the ingredient of this book, contains some technical guidance on creating tables for accounting modules, technical know how on entity relationship (linking your tables as an integrated system) and also some good lesson on sql programming. Sql in short, stands for structured query languange is a server-side scripting, that you need to master in order to create those financial reports (profit and loss, balance sheet, cash flow) that every Accountant or business manager would need.


What makes you think that your book will sell?

Well, the intention of writing this book is not to profit from it. The fact that, the scarcity of such book is what drives me to write it down in the first place. If you are an accountant, a system engineer, a database designer or developer, this would serves as your source of information. An accountant would be equipped with GAAP knowledge, a system engineer would possess the designing skill, and to link and assimilate these two-side knowledge is what this book is trying to achieve. You would need this if your company is going through a system migration, developing in-house accounting system or churning out customised reports.

Do you have any review on your book?

I have some that are harsh. The way of finding out whether you are buying the right book is to browse through a sample of pages of the book. In smashwords, readers are encouraged to browse some pages of the book and that will allow readers the discretion to decide whether to purchase the book. Of course, if you find the book interesting, I would encourage readers to purchase it directly from our official website (USD10.00). The benefits of purchasing directly from our official site, is you will get a free pdf copy of the accounting database data diagram, detailing the relationship links between all the accounting tables, created from the book. If you are purchasing directly from our site, please send us your name, date and time of purchase to We will cross-check your purchase details against the information contained in our merchant account profile and will release a download link to our book by responding to your mail.

How did you get into ebook publishing?

The book was written few years ago. Back then, digital publishing was not that popular. The traditional way of getting your book out in those bookstore, is the only way. I remembered those moments where I struggle to find an agent, an editor and a reputable publisher. Getting an agent is easy, not without a fee, but to get a trustworthy editor was demoralising. I lamented the decision of finding one, as I could not bear the pain of criticism. They would literally throw out any dissatisfaction or correction to you if they think there are some grammatical error or shortcoming from your writing skills. I recalled sending multiple mails to some potential publisher with a copy of my writing samples and all of them turned me down literally. I have given up hope of finding ways to publish the book for a few years, when suddenly one day I was casually browsing some ebook publishing site that I happenned to saw smashwords giving indie authors the privileges that most of us would dream of. Smashwords gives us the liberty of designing and pricing our books and best of all, at no charges, except for a small share of your book sales.


What do you think of digital publishing?

The word digital publishing to me, means having all your books, magazine or even newspapers in digital format. I think the publishing industry is facing a technology revolution, where most of our day to day reading would basically be on device platform. If you think Kindle was not popular 3 years back, look at the people on the streets, they are all reading their news from their iPad, smartphone, ebook reader and of course from your office desktop. The growing popularity of these technology gadgets is gradually changing the publishing industry business model from paperback to digital platform as the ultimate delivery channel. I think they would be great shift of publishing partnership from printing to digital broadcaster. I no longer buy newspaper off the street, as I indulge the pleasure of reading news from CNN website.



Accounting In SQL eBook



Learn how to write SQL for an accounting system. You will learn how to develop various modular graphical user interface with strong business logic control, how to create accounting tables and how to write powerful SQL statement involving the use of joins, subqueries, cursors, advanced data filtering, constraints, stored procedures and triggers in building a complete functional accounting system.

Table of Content


Chapter 1 : Inventory System


Inventory Table Structure


Inventory Graphical User Interface (GUI) :
Product Category GUI
Product Accounts Setting GUI
Product GUI


Business Logic Control:
Product Category Entry
Accounting Code Entry
Stock Item Entry


Chapter 2   Procurement System


Procurement Table Structure


Procurement Graphical User Interface (GUI) :
Vendor Setup GUI
Goods Received Note GUI
Purchase Invoice GUI
Purchase Credit Note GUI


Business Logic Control:
Goods Received Note Entry
Purchase Invoice Entry
Purchase Credit Note Entry


Chapter 3   Sales & Distribution System


Sales & Distribution Table Structure :


Sales & Distribution Graphical User Interface (GUI) :
Customer Setup GUI
Shipping Company Setup GUI
Employee Profile GUI
Delivery Order GUI
Sales Invoice GUI
Sales Credit Note GUI

Business Logic Control:
Delivery Order Entry
Sales Invoice Entry
Sales Credit Note Entry


Chapter 4   Cash Book System


Cash Book Table Structure :


Cash Book Graphical User Interface (GUI) :
Banker Setup GUI
Collection/Receipt GUI
Payment/Remittance GUI


Business Logic Control:
Banker Setup Entry
Collection Entry
Payment Entry


Chapter 5   Double-Entry System


Chart of Account and Journal Table Structure


Chart of Account and Journal GUI :
Chart of Accounts GUI
Journal Entry GUI
Closing Year-End GUI


Business Logic Control:
Account Code Entry in Chart of Accounts
Journal Entry
Closing of Current Year Earnings


Chapter 6   Inventory Back-End Scripting


Data Input :
Product Category Creation
Accounting Code Creation
Stock Item Creation


Product Category Triggers :
- create_product_category
- update_product_category
- delete_product_category


Product Accountset Triggers :
- create_acctset
- update_ acctset
- delete_ acctset


Product Triggers :
- create_item
- update_item
- delete_ item


Chapter 7   Procurement Back-End Scripting


Data Input :
Goods Received Note Transaction
Purchase Invoice Transaction
Purchase Credit Note Transaction


Creditors Triggers :
- create_creditor
- update_creditor
- delete_creditor

Goods Received Note Triggers :
- item_order_update
- item_order


Purchase Invoice Trigger :
- invoicing_purchase_update


Purchase Credit Note Trigger :
- credit_note_purchase_update


Chapter 8   Sales & Distribution Back-End Scripting


Data Input :
Delivery Order Transaction
Sales Invoice Transaction
Sales Credit Note Transaction


Customers Triggers :
- create_customer
- update_ customer
- delete_ customer


Shipping Triggers :
- create_shipper_code
- update_shipper_code
- delete_shipper_code


Employee Triggers :
- create_employee
- update_ employee
- delete_ employee


Delivery Order Trigger :
- item_order_update
- item_order


Sales Invoice Trigger :
- invoicing_order_update


Sales Credit Note Trigger :
- credit_note_update


Chapter 9   Cash Book Back-End Scripting


Data Input :
Collection Transaction
Payment Transaction


Banker Triggers :
- create_cash
- update_ cash
- delete_ cash


Collection Triggers :
- check_duplicate_chq_no
- receipt_cust_update


Payment Triggers :
- pymnt_cred_update


Chapter 10   Double-Entry Back-End Scripting


Data Input :
Account Code Creation
Journal Entry
Transfer to Retained Earnings


Chart of Accounts Triggers :
- create_coa
- update_ coa
- delete_ coa


Chart of Accounts Stored Procedure :
- select_coa


Closing Year-End Stored Procedure :
- select_retained_earnings


Financial Statements Query :
Chart of Accounts : coa_setup
Trial Balance : select_trial_balance
Balance Sheet : select_BS_without_acctcode
Profit and Loss : select_profit_loss



Purchase Accounting In SQL


Enter Name, Date and Time


Below are some commentaries from the author of Accounting In SQL :


Why coming out this book with the title Accounting IN SQL?

The root to SQL is programming. But, if you ask a programmer to code for an accounting system, does the programmer knows how to code, to begin with? Does that person knows what is accounting concept, double-entry principle, GAAP and other accounting treatment that are adopted in our working world? With this book, hopefully it may shade some lights.


Where do you see current ERP and business software is heading in our next generation?

Current ERP and business software is still thriving in PC-based, but eventually as time pass, we will slowly see some of these ERP and business software move towards the mobile-based platform.Technology is fast changing the human culture and life style. The way we communicate and socialize is so different compared to a decade ago.


Do you think your book will sell?

Again, it is not profit motivated, but what I would like to do with this book, is to share some of my knowledge and experiences with those who are interested in accounting system development. There are a lot of missing business rules and weaknesses in some of the existing business applications, and hopefully with this book, these group of people may gain an insight into some of the proper development guideline and techniques, which was incorporated in DES 1.2.

Do you have any review on your book?

Nop, not for this book, but there are some negative comments made on my first ebook, which I feel a bit discriminative and uncalled for. As a writer, I appreciate readers to give their fair share of thoughts, as it would help me to improve some of the shortcomings in some of the books which I have written. For those who are interested in buying a copy of these ebooks, I request that you send us your name, date and time of purchase to We will cross-check your purchase details against the information contained in our merchant account profile and will release a download link to our book by responding to your mail.

What areas do you think the current business application is lacking and can be improvised, and why is it so?


One apparent missing features in most of the application or ERP system, is audit trail. An audit trail not only can tell the end-user the process flow of an accounting transaction in the system, but, if we can see the flow of each transactional posting, we can determine the source of each transaction entry and we can pinpoint or gauge any missing elements (e.g. business rules) along the process. Another area which I believe, would be forthcoming, is having an in-built tool that can allow us easy access to the accounting database. We can develop a search engine tools, similar to Google Search Engine, which can then allow us to retrieve any kind of information that we want from the accounting system for further analysis. Just imagine the depth of information which we can obtained, if we can trace the flow of a particular documents or product item from its original source to the end of the process flow. The search result may shows us where is the product item is originally purchased, at what price and the location of the supplier. We can see to whom and where will this product item will be deliverred. By having this information, we are able to improve on our delivery and procurement system, with better update on customers' product ordering status.


What do you think of the role of accountant in the next generation?

I believe the role of accounting profession would not only be confined to pure accounting concepts and standards. Technology is fast over-taking the role of some of the work performed by the accountants. In the 90s we see emerging bookkeeping software that not only substitute the bookkeeping function, but also these application can also produce financial statements, with a click of a button. In the 20th century, we see tremendous integration of work processes across multiple functional department in various industry, hence, the emergence of enterprise resource planning (ERP). In the next decade, I believe, the next great exploration would be on business intelligence and social interaction. Business Intelligence (BI) would helps organization in making better decision-making, better predictive analysis and better forecasting. As for social interaction, most of the outsourcing provider are leveraging on social-media in improving their customer service via communicating with their clients via live-chat and social-networking. With these vast amount of data maintained in a database environment, we can expect to see greater auditing tools and forensic software being developed to assist auditors in performing their system auditing and audit testing analysis. We also can see some new development in the taxation area. Currently, there are tax software being developed for deferred tax computation, value added tax (VAT) reporting and other tax-related automation. In the next phase of growth, I think we can see some integration of all these taxation computation and reporting in a centralized system, given the vast expansion of our global economy. This will provide organisation a better overview of their current taxation status and exposure, which in turn will allow business leaders to make better taxation planning and restructuring across their overseas operations.






Copyright 2012 DES. All rights reserved.

All brand and product names are trademarks or registered trademarks of the publisher.

No part of this documentation or content may be reproduced or transmitted in any form or by any means, electronic or mechanical,

including photocopying, recording,or by any information storage or retrievel system, without prior

written permission from the publisher