This schema description will introduce you to sql-ledger underlying database tables and will enable you to create your custom reports using SQL or import data using CSV import features of Postgresql. Please see sql queries for examples of SQL using this schema.
1. Most tables have a primary key column named id. This key is appears as tablename_id as foreign key in other tables. For example customer_id column in tables is foreign key referring to id column in customer table. This id column gets unique sequence number from database sequence also named id.
2. Contrary to what new users might think customernumber, vendornumber, partnumber, invnumber etc. are not primary keys in customer, vendor, parts, ar/ap tables. These columns can also have duplicate values. You can prevent duplicate values by creating UNIQUE indexes on these columns. As mentioned above, in all tables, primary key is the column named id.
Debit/credit records for all financial tranactions are stored in this table. trans_id is the foreign key in this table which refers to id in ar, ap, gl tables.
Purchase invoices and AP transctions header information goes into this table. invoice column contains true for invoices and false for AP transactions.
Sales invoices and AR transactions header information goes into this table. invoice column contains true for invoices and false for AP transactions.
Stores assembly components for a given assembly. id is id for the given assembly from parts table. parts_id is id for a assembly component from parts table.
If you have activated the audittrial in System→Audit Control menu option, a row contain the username and transaction reference is inserted into this table for each transaction addition, deletion or editing activity. This table can grow very large on busy systems. You can also remove past audittrial record using System→Audit Control menu option. There are no reports in the system which show data from this table.
Type of business. This table is used to group customers. You can also specify the discount percent applicable to that particular group of customers.
This table contains Chart of accounts.
Customer master record.
Customer-tax information is stored in this table. One row for each customer/tax combination.
System defaults
Departments
Department transactions. One row for each transcation/deparmetn which contains a department reference.
One row for each user/employee.
Exchange rate for a particular date is added to this table if you use foreign currency on a transaction and there is no exchange rate in this table for that transcation date. A maximum of two rates are added to this table for a given date. One for buy and one for sell.
General Index for Financial Information or GIFI allows you to create an alternate chart of accounts which can be linked to main chart of accounts through the GIFI column on Add Account form. Financial reports can be generated by accno or gifi_accno. You can also consider it an alternate method of group the accounts on chart.
Contains one row for each general ledger transaction.
Inventory movement is recorded in this table when you use Ship or Receive options on ''Shipping' module.
Invoice detail lines for sales and purchase invoices go into this table. Qty is -ve for purchase invoices and +ve for sale invoices (unless you change sign when entering invoices for recording returns).
One row per language defined. These languages are used to translate the parts descriptions into another language to print on invoices etc. Actual translations are stored in translation table.
This table can contain one or more rows for any particular part. Make and model information is entered on Add Part screen. This information can be used to further classify or group the parts. For example you can specify which models use a particular part. You can filter on make/model on parts on Parts report.
This tables records header information for four type of transactions; Sales Order, Purchase Order, Quotation and Request for Quotations. vendor_id is NULL if this is a Sales Order or Quotation. customer_id is NULL If this is a Purchase Order or RFQ. Boolean column quotation further differentiates between an Order and a Quotation.
Detail lines for Orders and Quotations go into this table.
Contains master records of parts, services, assembly, labour/overhead. inventory_accno_id is NULL for service items.
Per customer pricing for a part is stored in this table. This information is stored from Add Part form. Multiple prices with different quantity breaks can be entered for one customer.
Parts groups allow you to group your parts, services etc according to any criteria.
One row for each part/tax combination which is specified on Add Part screen.
Per vendor cost for a part as well as lead time and vendor part number are stored in this table. This information is updated through Add Part form.
One row per price group. Price group can be specified on the customer screen to group one or more customers to specific parts prices. You specify prices for parts group on Add Part screen.
One row per project. Project can be linked to sale, purchase or general ledger transaction detail lines. You can run reports filter them on projects.
Whenever you schedule a transaction, its id and other information for is stored in this table.
One row for each tranaction/formtype combination to be mailed when a recurrening transaction is created and marked to email one or more forms upon generation.
Same as recurringemail but for printing of forms.
One row is added to this table when you create Ship To address in customer or vendor form. trans_id is the customer or vendor id for which shipto address has been created. A row is also added when you specify different shipto on invoice. In this case trans_id is the id of that transaction.
Standard industry codes. Used to group customers.
One row for each tax defined. Tax are defined by marking them Tax on Add Account form.
One row for each description translated to a language defined in language table.
Contains master records for each vendor.
Taxes applicable to each vendor are defined in this table. One row for each vendor/tax combination.
One row for each warehouse defined.
You you run System→Yearend process, a journal transaction is generated in the gl table (with detail lines in acc_trans). id for the transaction along with the date is stored in this table. (one row per year end).