Developing the Journals Table

A General Database Design Concept for DES 1.2 (free accountingdes.com software)

The Journal table would contain the double-entry information of an entry performed by a user. Let us take a case of a company, known as MaxCorp. MaxCorp is in the business of trading computer hardware and software. It needs to have a system to keep track on all its business transaction, and also a system that can produce relevant accounting reports on every close of each month. Dave, the Accountant, would normally raise an invoice to a customer on a Sales Form screen, and information such as date of invoice, product, customer name, pricing would be entered on the face of the Sales Form screen, and would be stored in the below fields created in the Journal Table.

First Normal Form

We understand that, MaxCorp’s customer may receive several invoices in a week from its billing department, and some, merely once in a year, depending on the number of purchases made by its customer. As shown on the table above, currently the Journal Table can only fit in two transactions for each customer and Dave would have to insert a new line of records, having entering the Customer name again, if the same customer buy from MaxCorp for the third time. This is inefficient, as Dave, is repeating groups of data, by entering the customer name twice. To begin our first step of normalization, we will break the Journal Table into a Sales Table and a Journal Table. We will then, assign an Inv_ID_VC fieldname as the primary key in the Sale Table. By creating a separate Sales Table, Dave would be able to raise as many invoice to a customer without having to repeat its customer name again. By creating a separate Sale Table, we are eliminating duplication of data and making use of the storage space of each field more efficiently, as each customer’s name are only created once.

Second Normal Form

We aware that, a customer can buy more than one type of product from MaxCorp, thus, we need to further break up the Journals Table into a Product Table, a Sales Table and a Journals Table. We will assign the fieldname, Pdt_ID_VC as the primary key in the Product Table, and as a foreign key in the Sales Table referencing to a particular product residing in the Product Table. We are establishing a many-to-many relationship between the Sales and Product Table, by connecting these tables via a common field, where a customer may buy different types of products from MaxCorp, and a product could be purchased by different types of customers.

Third Normal Form

So far, we have created two tables out of Journals Table, the Sale Table and Product Table. At this point, our task is incomplete, Dave has voiced his concern on the issue of data integrity. He is worried, especially on numerical fields that hold important figures, vital to the preparation of logical and comprehensive financial reports to MaxCorp’s management. He wants to have a database that runs on a real-time basis, where whenever an invoice is issued, an entry would be automatically posted in the Journals, when a collection is made from a customer or payment made to a creditor, an entry would also be posted in the Journals, without having the need for manual entry, simply said, a real-time processing system.

Having understood Dave requisition, we need to redefine the structure of the database design; we know that, he wants a real-time processing for all the posting of journals, the self-creating double-entry records for each level of order processing performed by Dave.

After a brainstorming session with Dave, we understood that, he wants a database that consist a group of tables, representative of each accounting module, to have a direct interface with the Journals Table. The Journal Table is the central repository that records all back-end double-entries performed by the client-application for all order processing transaction, and all front-end transaction performed by the user.

Dave explained, that, for each double-entry performed, a debit, and a credit entry would take place, example, if MaxCorp need to bill a customer, a debit and credit amount would be posted in the Journal Table, debiting an X amount in a Debtor account and crediting the corresponding amount in the Sales account. When he collects from its customer, and then making payment to its creditors on its purchase due, all these transaction would be recorded also as a double-entry in the Journals Table.

With the above knowledge shared by Dave, we need to break the Journals Table further, into another table, known as a Chart of Accounts Table (COA). This Table allows Dave to create account code (GL_ID) that uniquely identify each elements of accounts name, that eventually make up as one of the elements in the COA Table.

Before any transaction is being posted as a double-entry in the Journals Table, user would need to drill-down the dropdown list box, to select the appropriate account code (GL_ID), if it does not exist, user would be prompted to create the account code beforehand in the COA Table. Some of the posting can be pre-defined by the user during set-up stage, for example, double-entry for a customer billing can be pre-defined to debit and credit to a fix Sales and Debtors account, and some transaction would require user selection of account code during order processing process.

A list of important fields with their data type attribute, has been identified in the COA Table, which would have the GL_ID field assigned as primary key, referencing the Journal Table as a foreign key.

Now, Create the COA Table by first Opening the SQL Server 2000, then, select Database | AIS | Table, right click on your mouse, and select New Table.

Key in the following fieldnames, data-type, length, and its Allow Null attributes in the COA_T Table as shown below:

Designing Chart of Accounts Table

The GL_Name_VC field would hold the different type of account name, in which are uniquely identified by an account code located in the GL_ID field. The BS_Category_VC field would hold the types of accounts category, to which each individual accounts name will fit in. Segment_VC field stores the different type of business units to be created in MaxCorp. Lastly, user has the option to freeze the account code, by changing its status from active to a non-active account code, preventing the selection of the account code for posting purposes.

Key in the following fieldnames, data-type, length, and its Allow Null attributes in the Journal_T Table as shown below:

Designing Journal Table

The Journal table would have a field, called Doc_No_VC that will hold a set of double-entry records performed by a user or by an auto-generated entry performed by the system. Each journal entry would require the following information to be provided, in the remaining fieldnames, the GL_ID field, that will capture the account code of each journal entry, the invoice number (under Inv_ID_VC field) that is assigned as a foreign key, referencing to a particular set of records in the Sale_T table, amount of each transaction posting (under Amount_NU), date of the transaction posting (under Date_DT), Description, Period and Year.

Key in the following fieldnames, data-type, length, and its Allow Null attributes in the Sale_T Table as shown below:

Designing Sales Table

This table contains information on sales transacted with each customer, holding information on product, customer and invoice number. We assign the Inv_ID_VC field as a primary field, as invoice number would be the best candidate for being a unique identifier and as a foreign key in the Journal_T table that will uniquely identify a row of records related to a transaction’s double-entry located separately in the Journal Table. The Sales Table merely hold a number of key identifier that are linked to several other tables, namely, the Pdt_ID_VC would link to a Product Table, giving further details, on name, description, category and supplier of each product items. We shall explore further on this table as we develop further in our database design under Chapter 5.

Designing Product Table

We will assign Pdt_ID_VC as the primary key field for Product Table, which holds key information of each product’s name. We will get to know the usefulness of account set field, as we touches on Chapter 3 onwards.

Summary

In this Chapter, we have created the Journal Table, and then we learned how to normalize this table into several tables, going through the normalization process, from first normal form to third normal form. In Chapter 3, we will discuss how to normalize the Product Table further, giving us an insight on the types of fields needed in a Product table.

To summaries, in this chapter, we have discussed:

Ø      The Normalization Concept (First Normal Form to Third Normal Form)

Ø      The use of Primary and foreign keys

Ø      The purpose of a Chart of Accounts and its relevant fields

Ø      The purpose of a Journal Table and its relevant fields

Ø      The purpose of a Sales Table and its relevant fields

Ø      The purpose of a Product Table and its relevant fields  

Next We will touches on developing the Inventory Table, Subscribe to this blog for further updates..