Sunday, January 23, 2011

Designing and Developing the Inventory System

An interactive blog for accountingdes.com website. Your preferred choice of free accounting software provider.

As discussed earlier, the Product Table (refer snapshot above) was created to keep track on each product item’s name, description, supplier code and its account set code. This table is still not in perfect form, as Dave, would also want to keep track on the movement of each of the product sold, the costing method applied on each product items and the pricing of each of its product items. We will again, follow the step-by-step normalization process to further identify key elements that should be broken down further away from the Product Table.

First Normal Form


We know that, not all products in MaxCorp are purchased from the same source of supplier, it could be a product sourced from different supplier or a supplier could also be supplying MaxCorp more than one product type. It is logical, at this point, to create a separate table for supplier, referencing the Product Table via a foreign key named Cred_ID_VC, being a primary key assigned in the Creditor Table.


Second Normal Form

At this stage, we have further identified another key element, the product category, as MaxCorp will also need to group its sale by category, in order to better analyze the marketability and profitability of each product type. We branch out the Pdt_Category_VC field, to create a separate table, named, Product Table_Category, assigning the field Pdt_Category_VC, as the primary key, having linked to the Product Table, as a foreign key.

Third Normal Form

We have not created any field to capture the quantity movement and balance of each product items. Create a table named, Stock_Movement Table to capture the physical movement of each product sold by MaxCorp, and a table named, Stock_Balance Table to monitor the current quantity balance of each product items.



Designing Stock Movement Table

 


This table (refer snapshot above) would keep track of each product movement in MaxCorp warehouse. A row of records would be inserted into this table when MaxCorp delivers products to its customer, receiving incoming goods from its supplier, or even transferring goods between different warehouse locations. In our table, we only maintain one location for storing MaxCorp’s goods, and should MaxCorp expands its business further in future, it may establish branches that would require more than one warehouse to store its product, then MaxCorp may need to create a separate table just to store warehousing information. The Descrip_VC field would accommodate the delivery note and goods receive note numbering for each product items moving in and out from MaxCorp’s warehouse.




Designing Stock Balance Table

This table will hold the latest balance of each product items, after accumulating or deducting the previous balance of each product items, to illustrate this further, assuming that, MaxCorp purchase 2 units of item A at $1.50 each. The system would record this information in this table after updating the Stock_Movement Table. When item A is sold to a customer, the system would then insert a new record in the Stock_Movement Table and will update the balance of item A in Stock_Balance Table simultaneously. For each update in the Stock_Balance Table, the system will first identify the matching product item in the Stock_Balance Table, if it exist, the system would update its quantity and unit price, by replacing its current quantity and unit price, and if, it is a new product item, a new record would then be inserted. This table plays a significant influence on product pricing. We will have a better understanding, as we discuss further on the costing method field created in the Product AccountSet Table.



Designing Product Account Setting Table

The Cost_Method_VC field plays an important role in determining how a product item should be priced, when a product is sold to a customer. At present, we have two alternative in pricing MaxCorp product, the first-in-first-out (FIFO) method and the cost average method. Applying the first method, would price MaxCorp’s goods on the oldest price first, as for the latter, at an average price. The quantity and pricing of each product item is referenced to the Stock_Balance Table.


Besides determining the pricing method of its out-going goods, Dave would also want the double-entries to flow into the Journal Table, to record the cost of the out-going goods and the cost of purchasing the goods from its suppliers. To achieve this, we need to assign a default inventory account code to record the cost of in-coming and out-going of each product item. In the Stock_IN field, a clearing account (Payable_IN) would need to be assigned to place its purchase cost temporarily, while awaiting billing from respective supplier and a clearing account (Shipment_IN) is assigned to capture its shipment cost, while pending subsequent billing to its customer.


Designing Product Category Table

This table contains three important fields, with the last field requiring Dave, to assign two default account code for effecting the double-entries into the Journal Table, each time a user raises an invoice to its customer. We will assign the field Pdt_Category_VC as the primary key, having reference to the Product Table. Firstly, under each product category, Dave would need to assign a default account code for the cost of goods sold amount, to record the pricing cost of its product, secondly, a default account code to record the sales amount of each invoices raised from the client application.

Next. We will learn the steps in designing and developing the purchase table which will keep track of our purchasing transaction and payment to our supplier, stay tuned..

Friday, January 7, 2011

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..

Structured Query Language

What is SQL?

SQL, an abbreviation for Structured Query Language, is a language used to execute a set of instruction directed to a database. When you go to an auto-teller machine, to withdraw money, you need to press certain button, to instruct the machine what to do, when you go to the Internet, you use your keyboard or mouse to navigate or search for your favorite website, you are telling your machine what to do. All this are possible with the help of SQL. It is a universal language that receives instruction from a “front-end” object that will then compile and send the instruction back to a “back-end” object. The front-end object is an application tool, such as VB, C++, and the back-end object, is a database system, that helps to store data. The instruction received from a front-end application, generally perform the following task:

  • Select existing data
  • Insert new data
  • Update existing data
  • Delete existing data
SQL is a language governed by the American National Standards Institute (ANSI), a standard committee that consists of database experts from industry and software vendors. Thus, SQL is a universal and open language, meaning that, it is not owned by any industry.

Transact-SQL

DBMS, or database management system, is a software product that holds and store data. A number of famous DBMS worth noting, are, IBM DB2, MySQL, Sybase Adaptive Server, Oracle, Microsoft Access, and Microsoft SQL Server. These various DBMS, would have their own type of SQL version, generally differ in terms of syntax and features, but, they all complied to the American National Standards Institute (ANSI) SQL Standard. In our exercise contained in this book, we will be using Microsoft SQL Server as our DBMS in employing the use of Transact Structured Query Language (T-SQL), Microsoft’s version of SQL.