Database Structure

This page is a description of the database structure used in webERP - and some rationale if any presents itself to justify it.

The structure is highly normalised to:

General Ledger

General Ledger uses 4/5 tables:

1. chartmaster - the list of GL accounts - an account code, reference to the account group

2. AccountGroups - this allows for chartmaster records to be grouped and sub-grouped (in webERP only) avoiding the AccountGroupsAndChartOfAccounts lengthy hard coding of account codes in the old fashioned way for cost centre reporting. There is one account group to many chartmaster records and there is a foreign key from chartmaster.accountgroup to the accountgroups table primary key

3. chartdetails - linked to the chartmaster on the accountcode, but has a primary key of accountcode and periodno - foreign key to the periods table - this table contains the bfwdbalance on the account and the movement on the account for the period. There is one record in the chartdetails for every combination of period/chartmaster - a cross join is used in the includes/ script to ensure this is always the case before postings are processed. chartdetails also has a field for the budget movement for the period. This structure allows a trial balance to be constructed from any range of periods and avoids any period close functionality - the retained earnings figure is recalculated from the sum of profit and loss accounts as at the period from - plus any opening balance in retained earnings. By storing the account movement for the period in the chartdetails table together with the bfwdbalance a query only needs to retrieve this stored data rather than recalculate the balance from the transactions which could be done - but on a busy database the number of gltrans can get too large to be able to run such calculations without incurring too much processing and having delays in bringing up trial balances/profit and loss and balance sheets. All reporting is driven off the chartdetails table.

4. gltrans - this is where all the journals are held - the transtype is referenced to the systypes table that lists the transaction types that webERP allows. Negative amounts represent credits and positive amounts debits. There is extensive narrative allowed on gltrans records. Obviously the gltrans references the chartmaster accountcode and the periods.periodno table.

5 systypes - is used throughout webERP - lists all the transaction types available in webERP. eg. AR receipts - type 12 , goods received, AR invoices - type 10 , AR credit notes - type 11, AP invoices etc etc. Each transaction type has a tiny integer associated with it to avoid storing long strings in transaction tables which would increase overhead.

Accounts Receivable

Also uses systypes above ... and

1. debtorsmaster - the list of customers - the primary key being the debtor code - debtorno, this table is meant to store all the account information relevant to the customer, payment terms, holdreason (credit status), currency, taxgroup, salestype(price list) - all referenced to a separate individual tables of paymentterms/holdreasons - credit statuses/currencies/taxgroups/salestypes(price lists). Obviously the customer name and billing/postal address details are also in this table.

2. custbranchs - the list of branches or delivery address for each customer - contains the debtorno which is a foreign key back to the debtorsmaster - to uniquely identify a customer then a combination of debtorno and branchcode are required. The custbranch table contains all the information specific to the particular location of the branch of the customer - the details of the physical street address, salesperson, salesarea (geographical area), the delivery time to the customer branch. Also there is an option to address invoices to branches so the table also holds the postal address of the branch as well.

3. Debtortrans - this is where AR transactions are stored - the debtortrans record contains the exchange rate of the transaction to local(functional) currency of the business. All AR transactions are recorded in this table and referenced to systypes for the transaction type. The convention is that the foreign currency amount is always recorded in the DB and the local currency amount is derived by reference to the ovamount - overseas amount and the rate (exchange rate to local currency). debtortrans invoices (transtype=10) need to refer also to a sales order for invoice reprints to show correctly. The amount allocated against the transaction is also recorded in the debtortrans record again in overseas currency - or the currency of the customer as per the debtorsmaster. The debtortrans table has foreign keys to the custbranchs and debtorsmaster tables as well as the periods table. Depending on to which transactions an invoice has been allocated from the difference in exchange between the receipts/credits and the invoices will differ - this is recaclulated each time allocations are modified and the exchange difference (in local currency) is stored in the debtortrans record.

When bringing balances into webERP - since it is an "open-item" system this is the table that you need to import the open invoices and credit notes into - description of each of the fields follows so importing transactions from and existing system can be accomplished:

`debtorno` varchar(10) NOT NULL DEFAULT ,
`branchcode` varchar(10) NOT NULL DEFAULT
`trandate` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

`reference` varchar(20) NOT NULL DEFAULT ,
- `tpe` char(2) NOT NULL DEFAULT , This corresponds to the sales type and is taken from the debtorno record - it was envisaged that this could be different to the customer's sales type (price list) initially.
  • `order_` int(11) NOT NULL DEFAULT '0', - this corresponds to a sales order number that the invocie was created from. It is required to be set for reprints of invoices since some of the information comes from the sales order.
  • `rate` double NOT NULL DEFAULT '0', This is the exchange rate at which the ovamount, ovgst etc are converted at to get the local currency (i.e. the business's functional currency) amount
- `ovamount` double NOT NULL DEFAULT '0', This is the amount of the transaction net of freight and tax in the customer's currency
- `ovgst` double NOT NULL DEFAULT '0', This is the amount of the tax on the transaction (nil for receipts) in the customer's currency
- `ovfreight` double NOT NULL DEFAULT '0', The amount of the freight in the customer's currency
- `ovdiscount` double NOT NULL DEFAULT '0', The amount of the discount on payments in the customer's currency - nil for invoices and credits
- `diffonexch` double NOT NULL DEFAULT '0', This represents the total difference on exchange on this transaction e.g. where an invoice is allocated by several different receipts and each at a different exchange rate to the invoice the exchange difference calculated and stored here maybe the result of a number of calculations
- `alloc` double NOT NULL DEFAULT '0', This is the amount in the customer's currency that has been allocated to this transaction. In the case of an invoice the amount that the customer has either paid or had credit notes that have reduced the amount owing on the invoice. The ovamount+ovgst+ovfreight-alloc is therefore the amount left outstanding on an invoice.
  • invtext` text, This holds any narrative comments that are printed on the invoice/credit-note
  • `shipvia` int(11) NOT NULL DEFAULT '0', This is the carrier (and a foreign key to the shippers table) that handled the delivery of the goods on this transaction
`edisent` tinyint(4) NOT NULL DEFAULT '0',
- `consignment` varchar(15) NOT NULL DEFAULT , This is the consignment note reference recorded at the time the invoice/dispatch was processed.

4. custallocs - this table stores the record of the allocations between customer debits and credits - cash/chq/dd receipts and credit notes are allocated to invoices. Only the transaction id - the primary key of debtortrans is stored for the transaction allocated from and the transaction allocated to with the overeseas currency amount allocated. This table allows the inquiries of how an invoice was paid and where a payment was allocated to. It also allows allocations to be modified retrospectively - I think this is relatively unusual functionality in accounting software.

Accounts Payable

Very similar structure to Accounts Receivable with:

1. Suppliers - the master file for all suppliers (Vendors for you Americans) - containing the terms, name and address, bank account details etc

2. suppliercontacts - this can contain as many supplier contacts as necessary for each supplier - with email address, phone and fax numbers - similar to custbranchs (in AR) but much briefer

3. supptrans - similar to debtortrans - containing all the transactions in ap - each transaction with its own type - from systypes

4. suppallocs - similar to custallocs - storing the allocations of supplier invoices and debit notes to payments made

Sales Orders

1. salesorders - hold the header information about orders (and whether or not it is just a quotation or a real order) - data includes delivery address fields and the sales type (price list used)

2. salesorderdetails - this is the line items on sales orders - has a foreign key to the salesorders table on the orderno and contains the quantity and price of the item together with the expected delivery date and the purchase order reference of the customer. salesorderdetails are updated on invoicing for the quantity invoiced. Once an salesorderitem line is complete ie no longer backordered the quantity = quantity invoiced and the line is flagged as complete =1


Has a table dedicated to prices which are held by:
. Currency - referenced to the currencies table
. Sales type (referenced to the customer debtorsmaster) and the sales types table
. Customer - it is possible to have a separate price just for a single customer
. Customer branch - it is possible to have a separate price just for a single branch of a customer

Concurrency Management

Whenever a transaction is initiated a new transaction number is obtained with the GetNextTranNo ( systypes.transtypeid) this function is in the includes/ script - this function first locks the table systypes to ensure that no other users could be given the same transaction number - it is only ever possible for a unique transaction number to be allocated to a transaction. Transaction records can then always be inserted in the knowledge that "there can be only one" of that transaction type - and no possibility for conflicts.
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki