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

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.

Accounting System Design

Accounting System Revolution

 In our modern world today, it is undisputable fact, that most of the corporate world has and is continuously changing and adapting to new technology, especially in the area of computerisation, in order to remain competitive in the business world. One of the greatest importance in any corporate industry, is adopting a robust and powerful accounting application, that are not just user-friendly, but capable of providing the flexibility and scalability needed in a rapid changing environment.

A powerful accounting application, depends fundamentally on a well structured and designed database. The traditional method of designing and creating a flat-file database is no longer viable and economical, as it has numerous flaw and limitation comparing to a relational database. Most of the existing database today, are developed using the relational database management system (RDBMS) approach, of which it is capable of enforcing greater data integrity and consistency, maximizing storage space efficiency and eliminating redundant data.

Database

A database is a place where data are stored in columns, and rows in a table, just like a spreadsheet, a database consist of one or several tables. A table consist of many columns, known as fields, and each fields consist of many rows, called records. Data stored in a table, can be retrieved, updated or even deleted through executing a set of instruction to a database. This set of instruction is what we call SQL statement.

When the first database was created, its design was not in perfect form. The model of the design, was to store data in a single stream of bytes. This is known as a flat-file database. A flat-file database is inefficient, given the lack of scalability and storage capacity.

Relational Database

A relational database model is designed to consist several tables that can be joined together via the use of common related fields. The link of two or more tables is achieved through the use of primary key and foreign keys, known as a relationship. The advantage of a relational database over a flat-file database, is its ability to store data in different tables, with minimal duplication.

Primary Key (PK)

A primary key is an identifier, that uniquely identify a record stored in a table. By assigning a primary key to a particular field in a table, we can uniquely retrieve, update or delete certain records from a table. A primary key, can relate to other primary key created in another table. A primary field cannot be null, means it must be populated with value. A user cannot insert a value in a primary field twice, as a primary field is a unique field, and it cannot contain two row of records with the same value.

Foreign Key (FK)

A primary key is known as a foreign key, if it links to a primary key of another table. A value entered in a foreign field, should be the same value entered in the primary field of another table. You could not enter a value as a foreign key, that are not initially entered or exist in a primary field of another table.

Normalisation Concept

Normalisation is a guideline that shows the method or way of designing a well-structured database. Under normalization methodology, we can restructure database by simply following the below main three steps:

First Normal Form
Second Normal Form
Third Normal Form

First Normal Form

In the first normal form, a database designer is required to identify the type and group of data that each data item will fall in, and then decide which data should be used as the basis of creating individual table to contain them. Let’s take an example of creating a phone book database. A phone book, generally consist of name, data of birth, address, phone number, place of work, and other details. We know that our main item data, is Name and Location. So, we create a table called Name_T to store the name, date of birth, and phone number of each individual. We also create a table called Location_T to hold data on address and place of work. Our next task, under the first normal form, is to eliminate repeating groups of data. We know that, under the Name_T table, it is very likely that two or more person, may share the same name, and as for the address, it is possible that more than one person could be staying in the same place, therefore we could end up typing the same name or address twice in each of the tables. In order to ensure there are no duplication of data in each table, we need to identify a particular field to be a primary key. A primary key, is a unique identifier that identify a particular records in a table, and it ensure that a value entered in its field can never be re-entered twice. This enforce data integrity and consistency. In our Name_T table, we assign the field Name to be a primary field, and set it as primary key, and change the fieldname Name to Name_ID. We then, set the field address as a primary key, changing its fieldname address to Address_ID. We then create another table called, Customer Details_T table to store the Name_ID and Address_ID field.By assigning the Name and Address as a primary key, we can now have more than one record that shares the same name and location.

Second Normal Form

No other non-key field are independent of the primary key. We must ensure that all existing fields in a table, must depent on the primary key. We know that the Name_T table, contains the date of birth field, and it is possible that more than one person has the same date of birth, thus, we need to create a separate table specifically to hold the date of birth data, and we rename the date of birth to DOB_ID, and set it as a primary key.

Third Normal Form

When we reached the second normal form, we almost complete normalizing our database structure. In the third normal form, it’s basically ensuring that all non-key fields are now fully dependent on the primary key. We identify one more field, that bring us to our third normal form. We could have more than one person working in the same place, thus, it is logical to create the field place of work as a separate table. We rename the existing fieldname place of work to POW_ID, giving reference to a primary key created in a new table called Place_of_Work_T table.