Tuesday, December 18, 2007

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.