Simple Accounting System Database Design

Based on the diagram that I created earlier, I think I'll need 5 tables all included in a single database. One table will contain allowed users, the second will contain the chart of accounts, the third will be for the transactions and the fourth will be for the details of the transactions, mostly for the accounting entries, the fifth will hold the subsidiary accounts. Here's the detail of the database design:

Database name : SimpleAcc.dbc

Tables:

1. Users 

username c(20)

password c(20) 

2. Charts

acct_code c(8)

acct_desc c(100)

acct_type c(1) - 1 for assets, 2 for liabilities, 3 for capital, 4 for income, 5 for expenses

3. subs 

sub_code c(9)

sub_desc c(100)

sub_type c(1) - 1 customers, 2 suppliers, 3 employees, 4 others.

4. Journal

doc_no c(9)

doc_date d(8)

sub_code c(9) 

trn_type c(1) - 1 suppliers invoice, 2 check voucher, 3 sales invoice, 4 official receipt, 5 debit/credit memo, 6 journal voucher

amount n(10,2)

notes m(4)

5. xfile 

doc_no c(9)

doc_date d(8)

trn_type c(1)

acct_code c(8)

sub_code c(9)

amount n(10,2) - amount will either be positive (debit) or negative (credit)

sign n(1) - 1 means debit, 2 means credit

Ok, it's now time to create the database and table inside Visual Foxpro. The beauty with VFP is it comes with a built in database. You can connect to MS Sql or any other third party databases but since we are doing this quick and dirty, I'll be using the native visual foxpro database. 

~3:11 AM Saturday May 19, 2012