|
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
Database
Relational Database
Primary Key (PK)
Foreign Key (FK)
Normalization Concept
- First Normal Form
- Second Normal Form
- Third Normal Form
What is SQL?
Transact-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
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 request@accountingdes.com. 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
Temp_Product_Category_T
Product_Category_T
Temp_Product_Accountset_T
Product_Accountset_T
Temp_Product_T
Product_T
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
Temp_Creditors
Creditors
Document_T
Temp_Stock_T
Stock_Movement_T
Pur_Stock_Movement_T
Stock_Balance_T
Journal_T
Purchases_T
Purchases_T2
Temp_Purchase
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 :
Temp_Customers
Customers
Temp_Shippers
Shippers
Temp_Employees
Employees
Orders
Stock_Movement_T2
Temp_Stock_T
Sales_T
Sales_T2
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 :
Temp_Bank
Bank_T
Cash_T
Temp_Cash_T
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
Temp_COA
COA_T
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
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 request@accountingdes.com. 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.
| 
Free Accounting Software