Database Scheme for FrontAccounting 2.0

Table of Contents

Summary

Property Value
Report created 29-Apr-2008 13:13
DBMS and version MySQL [ 5.0.45-community-nt ]
Server localhost via TCP/IP
Database name account2
User/Login root
Driver and version myodbc5.dll [ 05.01.0004 ]
Table prefix 0_
DTM Schema Reporter version Version 1.17.00, (C) 2003-2008 DTM soft.

Tables, 62 items

Table: 0_areas, 2 fields

Field Name Type and Size Default Nullable Description
area_code integer 0 NULL 1 
description varchar(60) '' NOT NULL West,Arizona,Sweden 

Primary key for table 0_areas

Primary Key Name Field Name
PRIMARY area_code

Indexes for table 0_areas, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes area_code
description No Yes description

Table: 0_bank_accounts, 7 fields

Field Name Type and Size Default Nullable Description
account_code varchar(11) '' NOT NULL 1710,B120 
account_type smallint 0 NOT NULL Cash,Cheque 
bank_account_name varchar(60) '' NOT NULL Current Account 
bank_account_number varchar(100) '' NOT NULL 123-1123 
bank_name varchar(60) '' NOT NULL Wachovia Bank 
bank_address tinytext(255)   NULL The street,city,zip 
bank_curr_code char(3) '' NOT NULL USD,CAD,EUR 

Primary key for table 0_bank_accounts

Primary Key Name Field Name
PRIMARY account_code

Indexes for table 0_bank_accounts, 3 items

Index Name Clustered Unique Fields
PRIMARY No Yes account_code
bank_account_name No No bank_account_name
bank_account_number No No bank_account_number

Table: 0_bank_trans, 12 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
type smallint   NULL GL/AR/AP 
trans_no integer   NULL 103 
bank_act varchar(11)   NULL 1710,B120 
ref varchar(40)   NULL 1234 
trans_date date '0000-00-00' NOT NULL 2008-04-29 
bank_trans_type_id integer unsigned   NULL Cash,Cheque 
amount double   NULL 200.00 
dimension_id integer 0 NOT NULL 1 
dimension2_id integer 0 NOT NULL 2 
person_type_id integer 0 NOT NULL Cust,Supp 
person_id tinyblob(255)   NULL 1001 

Primary key for table 0_bank_trans

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_bank_trans, 5 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
bank_act No No bank_act, ref
type No No type, trans_no

Table: 0_bank_trans_types, 2 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
name varchar(60) '' NOT NULL Cash,Cheque 

Primary key for table 0_bank_trans_types

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_bank_trans_types, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
name No Yes name

Table: 0_bom, 6 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
parent char(20) '' NOT NULL 1 
component char(20) '' NOT NULL 103 (stock id) 
workcentre_added integer 0 NOT NULL 1 
loc_code char(5) '' NOT NULL DEF 
quantity double 1 NOT NULL 1 

Primary key for table 0_bom

Primary Key Name Field Names
PRIMARY parent, component, workcentre_added, loc_code

Indexes for table 0_bom, 11 items

Index Name Clustered Unique Fields
PRIMARY No Yes parent
PRIMARY No Yes component
PRIMARY No Yes workcentre_added
PRIMARY No Yes loc_code
component No No component
id No No id
loc_code No No loc_code
parent No No parent, loc_code
Parent_2 No No parent
workcentre_added No No workcentre_added

Table: 0_budget_trans, 11 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
counter integer 0 NULL 1 
type smallint 0 NOT NULL GL/AR/AP 
type_no bigint 1 NOT NULL 103 
tran_date date '0000-00-00' NOT NULL 2008-04-29 
account varchar(11) '' NOT NULL 1710,B240 
memo_ tinytext(255) '' NOT NULL Memo text 
amount double 0 NOT NULL 200.00 
dimension_id integer 0 NULL 1 
dimension2_id integer 0 NULL 2 
person_type_id integer   NULL Cust,Supp 
person_id tinyblob(255)   NULL 1001 

Primary key for table 0_budget_trans

Primary Key Name Field Name
PRIMARY counter

Indexes for table 0_budget_trans, 3 items

Index Name Clustered Unique Fields
PRIMARY No Yes counter
Type_and_Number No No type, type_no

Table: 0_chart_class, 3 fields

Field Name Type and Size Default Nullable Description
cid integer 0 NOT NULL 1 
class_name varchar(60) '' NOT NULL Assets
balance_sheet tinyint 0 NOT NULL 1 

Primary key for table 0_chart_class

Primary Key Name Field Name
PRIMARY cid

Indexes for table 0_chart_class, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes cid

Table: 0_chart_master, 5 fields

Field Name Type and Size Default Nullable Description
account_code varchar(11) '' NOT NULL 1710 
account_code2 varchar(11) '' NULL 100 (Extra field) 
account_name varchar(60) '' NOT NULL Checking Acc
account_type integer 0 NOT NULL Cash/Bank 
tax_code integer 0 NOT NULL 1 (Tax 5) 

Primary key for table 0_chart_master

Primary Key Name Field Name
PRIMARY account_code

Indexes for table 0_chart_master, 3 items

Index Name Clustered Unique Fields
PRIMARY No Yes account_code
account_code No No account_code
account_name No No account_name

Table: 0_chart_types, 4 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1
name varchar(60) '' NOT NULL Sales 
class_id tinyint 0 NOT NULL 3 
parent integer -1 NOT NULL  

Primary key for table 0_chart_types

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_chart_types, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
name No Yes name

Table: 0_comments, 4 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
type integer 0 NOT NULL 10 (sys_types) 
id integer 0 NOT NULL 1 
date_ date '0000-00-00' NULL 2008-04-29 
memo_ tinytext(255)   NULL Memo text 

Table: 0_company, 49 fields

Field Name Type and Size Default Nullable Description
coy_code integer 1 NOT NULL 1 
coy_name varchar(60) '' NOT NULL Training Co. 
gst_no varchar(25) '' NOT NULL Tax ID 
coy_no varchar(25) '0' NOT NULL 123456789 
tax_prd integer 1 NOT NULL 1 month 
tax_last integer 1 NOT NULL 1 month back 
postal_address tinytext(255) '' NOT NULL The street,city,zip 
phone varchar(30) '' NOT NULL 999-999-999 
fax varchar(30) '' NOT NULL 888-888-888 
email varchar(100) '' NOT NULL delta@delta.com 
coy_logo varchar(100) '' NOT NULL company.jpg 
domicile varchar(55) '' NOT NULL Nevada 
curr_default char(3) '' NOT NULL USD 
debtors_act varchar(11) '' NOT NULL 1500 
pyt_discount_act varchar(11) '' NOT NULL 4250 
creditors_act varchar(11) '' NOT NULL 2630 
grn_act varchar(11) '' NOT NULL 1430 
exchange_diff_act varchar(11) '' NOT NULL 4260 
purch_exchange_diff_act varchar(11) '' NOT NULL 4220 
retained_earnings_act varchar(11) '' NOT NULL 2050 
freight_act varchar(11) '' NOT NULL 3800 
default_sales_act varchar(11) '' NOT NULL 3000 
default_sales_discount_act varchar(11) '' NOT NULL 3000 
default_prompt_payment_act varchar(11) '' NOT NULL 3200 
default_inventory_act varchar(11) '' NOT NULL 1420 
default_cogs_act varchar(11) '' NOT NULL 4010 
default_adj_act varchar(11) '' NOT NULL 4210 
default_inv_sales_act varchar(11) '' NOT NULL 3000 
default_assembly_act varchar(11) '' NOT NULL 1410 
payroll_act varchar(11) '' NOT NULL 5000 
custom1_name varchar(60) '' NOT NULL  
custom2_name varchar(60) '' NOT NULL  
custom3_name varchar(60) '' NOT NULL  
custom1_value varchar(100) '' NOT NULL  
custom2_value varchar(100) '' NOT NULL  
custom3_value varchar(100) '' NOT NULL  
allow_negative_stock tinyint 0 NOT NULL 0 
po_over_receive integer 10 NOT NULL 10 
po_over_charge integer 10 NOT NULL 10 
default_credit_limit integer 1000 NOT NULL 1000 
default_workorder_required integer 20 NOT NULL 20 
default_dim_required integer 20 NOT NULL 20 
past_due_days integer 30 NOT NULL 30 
use_dimension tinyint 0 NULL 1 
f_year integer 1 NOT NULL 1 
no_item_list tinyint 0 NOT NULL 0 
no_customer_list tinyint 0 NOT NULL 0 
no_supplier_list tinyint 0 NOT NULL 0 
base_sales integer -1 NOT NULL -1 

Primary key for table 0_company

Primary Key Name Field Name
PRIMARY coy_code

Indexes for table 0_company, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes coy_code

Table: 0_credit_status, 3 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
reason_description char(100) '' NOT NULL Good History 
dissallow_invoices tinyint 0 NOT NULL 0 

Primary key for table 0_credit_status

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_credit_status, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
reason_description No Yes reason_description

Table: 0_currencies, 5 fields

Field Name Type and Size Default Nullable Description
currency varchar(60) '' NOT NULL US Dollars 
curr_abrev char(3) '' NOT NULL USD 
curr_symbol varchar(10) '' NOT NULL $ 
country varchar(100) '' NOT NULL United States 
hundreds_name varchar(15) '' NOT NULL Cents 

Primary key for table 0_currencies

Primary Key Name Field Name
PRIMARY curr_abrev

Indexes for table 0_currencies, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes curr_abrev

Table: 0_cust_allocations, 7 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
amt double unsigned   NULL 200.00 
date_alloc date '0000-00-00' NOT NULL 2008-04-29 
trans_no_from integer   NULL 10 
trans_type_from integer   NULL 10 (Inv) 
trans_no_to integer   NULL 30 
trans_type_to integer   NULL 11 (Cre) 

Primary key for table 0_cust_allocations

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_cust_allocations, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_cust_branch, 19 fields

Field Name Type and Size Default Nullable Description
branch_code integer 0 NULL 10 
debtor_no integer 0 NOT NULL 1001 
br_name varchar(60) '' NOT NULL Main 
br_address tinytext(255) '' NOT NULL The street,city,zip 
area integer   NULL 1 (West) 
salesman integer 0 NOT NULL 1 
phone varchar(30) '' NOT NULL 999-999-999 
fax varchar(30) '' NOT NULL 888-888-888 
contact_name varchar(60) '' NOT NULL Mark Spencer 
email varchar(100) '' NOT NULL delta@delta.com 
default_location varchar(5) '' NOT NULL DEF 
tax_group_id integer   NULL 1 (Tax 5) 
sales_account varchar(11)   NULL 3000 
sales_discount_account varchar(11)   NULL 3200 
receivables_account varchar(11)   NULL 1500 
payment_discount_account varchar(11)   NULL 3000 
default_ship_via integer 1 NOT NULL 1 
disable_trans tinyint 0 NOT NULL 0 
br_post_address tinytext(255) '' NOT NULL The street,city,zip 

Primary key for table 0_cust_branch

Primary Key Name Field Names
PRIMARY branch_code, debtor_no

Indexes for table 0_cust_branch, 4 items

Index Name Clustered Unique Fields
PRIMARY No Yes branch_code
PRIMARY No Yes debtor_no
branch_code No No branch_code
br_name No No br_name

Table: 0_debtor_trans, 19 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
trans_no integer unsigned 0 NOT NULL 1 
type smallint unsigned 0 NOT NULL Inv,Cre 
version tinyint unsigned 0 NOT NULL 0 
debtor_no integer unsigned   NULL 1001 
branch_code integer -1 NOT NULL 1 
tran_date date '0000-00-00' NOT NULL 2008-04-29 
due_date date '0000-00-00' NOT NULL 2008-05-15 
reference varchar(60) '' NOT NULL 1234 
tpe integer 0 NOT NULL 0 
order_ integer 0 NOT NULL 1 
ov_amount double 0 NOT NULL 200.00 
ov_gst double 0 NOT NULL 10.00 
ov_freight double 0 NOT NULL 10.00 
ov_freight_tax double 0 NOT NULL .50 
ov_discount double 0 NOT NULL 0 
alloc double 0 NOT NULL 100.00 
rate double 1 NOT NULL 1 
ship_via integer   NULL 1 
trans_link integer 0 NOT NULL 0 

Primary key for table 0_debtor_trans

Primary Key Name Field Names
PRIMARY trans_no, type

Indexes for table 0_debtor_trans, 4 items

Index Name Clustered Unique Fields
PRIMARY No Yes trans_no
PRIMARY No Yes type
debtor_no No No debtor_no, branch_code

Table: 0_debtor_trans_details, 11 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
debtor_trans_no integer   NULL 1 
debtor_trans_type integer   NULL Inv,Cre 
stock_id varchar(20) '' NOT NULL 102 
description tinytext(255)   NULL The description 
unit_price double 0 NOT NULL 100 
unit_tax double 0 NOT NULL 5 
quantity double 0 NOT NULL 1 
discount_percent double 0 NOT NULL 0 
standard_cost double 0 NOT NULL 40 
qty_done double 0 NOT NULL 0 

Primary key for table 0_debtor_trans_details

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_debtor_trans_details, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_debtor_trans_tax_details, 8 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
debtor_trans_no integer   NULL 1 
debtor_trans_type integer   NULL inv/cre 
tax_type_id integer 0 NOT NULL 1 (Tax 5) 
tax_type_name varchar(60)   NULL Tax 5 
rate double 0 NOT NULL 1 
included_in_price tinyint 0 NOT NULL 0 
amount double 0 NOT NULL 5.00 

Primary key for table 0_debtor_trans_tax_details

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_debtor_trans_tax_details, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_debtors_master, 14 fields

Field Name Type and Size Default Nullable Description
debtor_no integer 0 NULL 1001 
name varchar(60) '' NOT NULL Lucky Luke Inc. 
address tinytext(255)   NULL The street,city,zip 
email varchar(100) '' NOT NULL delta@delta.com 
tax_id varchar(55) '' NOT NULL Tax ID 
curr_code char(3) '' NOT NULL USD 
sales_type integer 1 NOT NULL 2 (Wholesale) 
dimension_id integer 0 NOT NULL 0 
dimension2_id integer 0 NOT NULL 0 
credit_status integer 0 NOT NULL 1 
payment_terms integer   NULL 1 
discount double 0 NOT NULL 30 
pymt_discount double 0 NOT NULL 0 
credit_limit float 1000 NOT NULL 1000 

Primary key for table 0_debtors_master

Primary Key Name Field Name
PRIMARY debtor_no

Indexes for table 0_debtors_master, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes debtor_no
name No Yes name

Table: 0_dimensions, 7 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
reference varchar(60) '' NOT NULL 1234 
name varchar(60) '' NOT NULL Development 
type_ tinyint 1 NOT NULL 1 or 2 
closed tinyint 0 NOT NULL 0 
date_ date '0000-00-00' NOT NULL 2008-04-29 
due_date date '0000-00-00' NOT NULL 2009-04-29 

Primary key for table 0_dimensions

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_dimensions, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
reference No Yes reference

Table: 0_exchange_rates, 5 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
curr_code char(3) '' NOT NULL CAD 
rate_buy double 0 NOT NULL 0.7900 
rate_sell double 0 NOT NULL 0.7900 
date_ date '0000-00-00' NOT NULL 2008-04-29 

Primary key for table 0_exchange_rates

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_exchange_rates, 3 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
curr_code No Yes curr_code, date_

Table: 0_fiscal_year, 4 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
begin date '0000-00-00' NULL 2007-01-01 
end date '0000-00-00' NULL 2007-12-31 
closed tinyint 0 NOT NULL 0 

Primary key for table 0_fiscal_year

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_fiscal_year, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_gl_trans, 11 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
counter integer 0 NULL 1 
type smallint 0 NOT NULL GL/AR/AP 
type_no bigint 1 NOT NULL 101 
tran_date date '0000-00-00' NOT NULL 2008-04-29 
account varchar(11) '' NOT NULL 1710 
memo_ text '' NOT NULL Memo text 
amount double 0 NOT NULL -200.00 
dimension_id integer 0 NOT NULL 1 
dimension2_id integer 0 NOT NULL 2 
person_type_id integer   NULL Cust/Supp 
person_id tinyblob(255)   NULL 1001 

Primary key for table 0_gl_trans

Primary Key Name Field Name
PRIMARY counter

Indexes for table 0_gl_trans, 3 items

Index Name Clustered Unique Fields
PRIMARY No Yes counter
Type_and_Number No No type, type_no

Table: 0_grn_batch, 6 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
supplier_id integer 0 NOT NULL 2001 
purch_order_no integer   NULL 1 
reference varchar(60) '' NOT NULL 1234 
delivery_date date '0000-00-00' NOT NULL 2008-04-29 
loc_code varchar(5)   NULL DEF 

Primary key for table 0_grn_batch

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_grn_batch, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_grn_items, 7 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1
grn_batch_id integer   NULL 1 
po_detail_item integer 0 NOT NULL 1 
item_code varchar(20) '' NOT NULL 102 (stock id) 
description tinytext(255)   NULL Row description 
qty_recd double 0 NOT NULL 1 
quantity_inv double 0 NOT NULL 0 

Primary key for table 0_grn_items

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_grn_items, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_item_tax_type_exemptions, 2 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
item_tax_type_id integer 0 NOT NULL 1 
tax_type_id integer 0 NOT NULL 2 (Tax 10) 

Primary key for table 0_item_tax_type_exemptions

Primary Key Name Field Names
PRIMARY item_tax_type_id, tax_type_id

Indexes for table 0_item_tax_type_exemptions, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes item_tax_type_id
PRIMARY No Yes tax_type_id

Table: 0_item_tax_types, 3 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
name varchar(60) '' NOT NULL Normal 
exempt tinyint 0 NOT NULL 1 

Primary key for table 0_item_tax_types

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_item_tax_types, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
name No Yes name

Table: 0_item_units, 3 fields, units of measure

Field Name Type and Size Default Nullable Description
abbr varchar(20) '' NOT NULL ea 
name varchar(40) '' NOT NULL Each 
decimals tinyint 0 NOT NULL 2 

Primary key for table 0_item_units

Primary Key Name Field Name
PRIMARY abbr

Indexes for table 0_item_units, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes abbr
name No Yes name

Table: 0_loc_stock, 3 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
loc_code char(5) '' NOT NULL DEF 
stock_id char(20) '' NOT NULL 102 (stock id) 
reorder_level bigint 0 NOT NULL 10 

Primary key for table 0_loc_stock

Primary Key Name Field Names
PRIMARY loc_code, stock_id

Indexes for table 0_loc_stock, 3 items

Index Name Clustered Unique Fields
PRIMARY No Yes loc_code
PRIMARY No Yes stock_id
stock_id No No stock_id

Table: 0_locations, 7 fields

Field Name Type and Size Default Nullable Description
loc_code varchar(5) '' NOT NULL DEF 
location_name varchar(60) '' NOT NULL Default 
delivery_address tinytext(255) '' NOT NULL The street,city,zip 
phone varchar(30) '' NOT NULL 999-999-999 
fax varchar(30) '' NOT NULL 888-888-888 
email varchar(100) '' NOT NULL delta@delta.com 
contact varchar(30) '' NOT NULL Stockerman 

Primary key for table 0_locations

Primary Key Name Field Name
PRIMARY loc_code

Indexes for table 0_locations, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes loc_code

Table: 0_movement_types, 2 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
name varchar(60) '' NOT NULL Adjustment 

Primary key for table 0_movement_types

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_movement_types, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
name No Yes name

Table: 0_payment_terms, 4 fields

Field Name Type and Size Default Nullable Description
terms_indicator integer 0 NULL 1 
terms char(80) '' NOT NULL Netto 10 days 
days_before_due smallint 0 NOT NULL 10 
day_in_following_month smallint 0 NOT NULL 0 

Primary key for table 0_payment_terms

Primary Key Name Field Name
PRIMARY terms_indicator

Indexes for table 0_payment_terms, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes terms_indicator
terms No Yes terms

Table: 0_prices, 5 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
stock_id varchar(20) '' NOT NULL 102 (stock id) 
sales_type_id integer 0 NOT NULL 2 (Wholesale) 
curr_abrev char(3) '' NOT NULL USD 
price double 0 NOT NULL 200.00 

Primary key for table 0_prices

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_prices, 4 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
price No Yes stock_id, sales_type_id, curr_abrev

Table: 0_purch_data, 6 fields

Field Name Type and Size Default Nullable Description
supplier_id integer 0 NOT NULL 2001 
stock_id char(20) '' NOT NULL 102 (stock id) 
price double 0 NOT NULL 100 
suppliers_uom char(50) '' NOT NULL TV 
conversion_factor double 1 NOT NULL 1 
supplier_description char(50) '' NOT NULL Lovely TV 

Primary key for table 0_purch_data

Primary Key Name Field Names
PRIMARY supplier_id, stock_id

Indexes for table 0_purch_data, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes supplier_id
PRIMARY No Yes stock_id

Table: 0_purch_order_details, 11 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
po_detail_item integer 0 NULL 1 
order_no integer 0 NOT NULL 1 
item_code varchar(20) '' NOT NULL 102 (stock id) 
description tinytext(255)   NULL Row text 
delivery_date date '0000-00-00' NOT NULL 2008-05-15 
qty_invoiced double 0 NOT NULL 0 
unit_price double 0 NOT NULL 50 
act_price double 0 NOT NULL 50 
std_cost_unit double 0 NOT NULL 20 
quantity_ordered double 0 NOT NULL 1 
quantity_received double 0 NOT NULL 0 

Primary key for table 0_purch_order_details

Primary Key Name Field Name
PRIMARY po_detail_item

Indexes for table 0_purch_order_details, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes po_detail_item

Table: 0_purch_orders, 9 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
order_no integer 0 NULL 1 
version tinyint unsigned 0 NOT NULL 0 
supplier_id integer 0 NOT NULL 2001 
comments tinytext(255)   NULL Memo text 
ord_date date '0000-00-00' NOT NULL 2008-04-29 
reference tinytext(255) '' NOT NULL 1234 
requisition_no tinytext(255)   NULL 238 
into_stock_location varchar(5) '' NOT NULL DEF 
delivery_address tinytext(255) '' NOT NULL The street,city,zip 

Primary key for table 0_purch_orders

Primary Key Name Field Name
PRIMARY order_no

Indexes for table 0_purch_orders, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes order_no

Table: 0_refs, 3 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NOT NULL 1 
type integer 0 NOT NULL 10 (sys_types) 
reference varchar(100) '' NOT NULL 1234 

Primary key for table 0_refs

Primary Key Name Field Names
PRIMARY id, type

Indexes for table 0_refs, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
PRIMARY No Yes type

Table: 0_sales_order_details, 8 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
order_no integer 0 NOT NULL 1 
stk_code varchar(20) '' NOT NULL 102 (stock id) 
description tinytext(255)   NULL Row text 
qty_sent double 0 NOT NULL 1 
unit_price double 0 NOT NULL 200 
quantity double 0 NOT NULL 1 
discount_percent double 0 NOT NULL 0 

Primary key for table 0_sales_order_details

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_sales_order_details, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_sales_orders, 17 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
order_no integer 0 NULL 1 
version tinyint unsigned 0 NOT NULL 0 
type tinyint 0 NOT NULL 0 
debtor_no integer 0 NOT NULL 1001 
branch_code integer 0 NOT NULL 1 
customer_ref tinytext(255) '' NOT NULL Mr Reffer 
comments tinytext(255)   NULL Memo text 
ord_date date '0000-00-00' NOT NULL 2008-04-29 
order_type integer 0 NOT NULL 0 
ship_via integer 0 NOT NULL 1 
delivery_address tinytext(255) '' NOT NULL The street,city,zip 
contact_phone varchar(30)   NULL 999-999-999 
contact_email varchar(100)   NULL delta@delta@com 
deliver_to tinytext(255) '' NOT NULL The street,city,zip 
freight_cost double 0 NOT NULL 10 
from_stk_loc varchar(5) '' NOT NULL DEF 
delivery_date date '0000-00-00' NOT NULL 2008-05-15 

Primary key for table 0_sales_orders

Primary Key Name Field Name
PRIMARY order_no

Indexes for table 0_sales_orders, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes order_no

Table: 0_sales_types, 4 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 2 
sales_type char(50) '' NOT NULL Wholesale 
tax_included integer 0 NOT NULL 0 
factor double 1 NOT NULL 1 

Primary key for table 0_sales_types

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_sales_types, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
sales_type No Yes sales_type

Table: 0_salesman, 8 fields

Field Name Type and Size Default Nullable Description
salesman_code integer 0 NULL 1 
salesman_name varchar(60) '' NOT NULL Marc Spencer 
salesman_phone varchar(30) '' NOT NULL 999-999-999 
salesman_fax varchar(30) '' NOT NULL 888-888-888 
salesman_email varchar(100) '' NOT NULL delta@delta.com 
provision double 0 NOT NULL 5 
break_pt double 0 NOT NULL 1000 
provision2 double 0 NOT NULL 4 

Primary key for table 0_salesman

Primary Key Name Field Name
PRIMARY salesman_code

Indexes for table 0_salesman, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes salesman_code
salesman_name No Yes salesman_name

Table: 0_shippers, 5 fields

Field Name Type and Size Default Nullable Description
shipper_id integer 0 NULL 1 
shipper_name varchar(60) '' NOT NULL Internet 
phone varchar(30) '' NOT NULL  
contact tinytext(255) '' NOT NULL  
address tinytext(255) '' NOT NULL  

Primary key for table 0_shippers

Primary Key Name Field Name
PRIMARY shipper_id

Indexes for table 0_shippers, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes shipper_id
name No Yes shipper_name

Table: 0_stock_category, 6 fields

Field Name Type and Size Default Nullable Description
category_id integer 0 NULL 1 
description varchar(60) '' NOT NULL System 
stock_act varchar(11)   NULL 1420 
cogs_act varchar(11)   NULL 4000 
adj_gl_act varchar(11)   NULL 1420 
purch_price_var_act varchar(11)   NULL 4000 

Primary key for table 0_stock_category

Primary Key Name Field Name
PRIMARY category_id

Indexes for table 0_stock_category, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes category_id
description No Yes description

Table: 0_stock_master, 19 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
stock_id varchar(20) '' NOT NULL 102 
category_id integer 0 NOT NULL 1 
tax_type_id integer 0 NOT NULL 1 
description varchar(200) '' NOT NULL TV 
long_description tinytext(255) '' NOT NULL Lovely TV 
units varchar(20) 'each' NOT NULL ea 
mb_flag char(1) 'B' NOT NULL B 
sales_account varchar(11) '' NOT NULL 3000 
cogs_account varchar(11) '' NOT NULL 4000 
inventory_account varchar(11) '' NOT NULL 1400 
adjustment_account varchar(11) '' NOT NULL 1420 
assembly_account varchar(11) '' NOT NULL 1420 
dimension_id integer   NULL 0 
dimension2_id integer   NULL 0 
actual_cost double 0 NOT NULL 20 
last_cost double 0 NOT NULL 20 
material_cost double 0 NOT NULL 20 
labour_cost double 0 NOT NULL 0 
overhead_cost double 0 NOT NULL 0 

Primary key for table 0_stock_master

Primary Key Name Field Name
PRIMARY stock_id

Indexes for table 0_stock_master, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes stock_id

Table: 0_stock_moves, 13 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
trans_id integer 0 NULL 1 
trans_no integer 0 NOT NULL 1 
stock_id char(20) '' NOT NULL 102 (stock id) 
type smallint 0 NOT NULL 1 (Adjustment) 
loc_code char(5) '' NOT NULL DEF 
tran_date date '0000-00-00' NOT NULL 2008-04-29 
person_id integer   NULL 1001 
price double 0 NOT NULL 200 
reference char(40) '' NOT NULL Reference 
qty double 1 NOT NULL 1 
discount_percent double 0 NOT NULL 0 
standard_cost double 0 NOT NULL 20 
visible tinyint 1 NOT NULL 1 

Primary key for table 0_stock_moves

Primary Key Name Field Name
PRIMARY trans_id

Indexes for table 0_stock_moves, 3 items

Index Name Clustered Unique Fields
PRIMARY No Yes trans_id
type No No type, trans_no

Table: 0_supp_allocations, 7 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1
amt double unsigned   NULL 200
date_alloc date '0000-00-00' NOT NULL 2008-04-29
trans_no_from integer   NULL 1 
trans_type_from integer   NULL 1 
trans_no_to integer   NULL 10 
trans_type_to integer   NULL 1 

Primary key for table 0_supp_allocations

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_supp_allocations, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_supp_invoice_items, 12 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
supp_trans_no integer   NULL 1 
supp_trans_type integer   NULL Inv 
gl_code varchar(11) '0' NOT NULL 4000 
grn_item_id integer   NULL 1 
po_detail_item_id integer   NULL 1 
stock_id varchar(20) '' NOT NULL 102 (stock id) 
description tinytext(255)   NULL Row text 
quantity double 0 NOT NULL 1 
unit_price double 0 NOT NULL 50 
unit_tax double 0 NOT NULL 0 
memo_ tinytext(255)   NULL Memo text 

Primary key for table 0_supp_invoice_items

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_supp_invoice_items, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_supp_invoice_tax_items, 8 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
supp_trans_no integer   NULL 1 
supp_trans_type integer   NULL Inv 
tax_type_id integer 0 NOT NULL 1 
tax_type_name varchar(60)   NULL Tax 5 
rate double 0 NOT NULL 1 
included_in_price tinyint 0 NOT NULL 0 
amount double 0 NOT NULL .05 

Primary key for table 0_supp_invoice_tax_items

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_supp_invoice_tax_items, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_supp_trans, 12 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
trans_no integer unsigned 0 NOT NULL 1 
type smallint unsigned 0 NOT NULL Inv 
supplier_id integer unsigned   NULL 2001 
reference tinytext(255) '' NOT NULL Reference 
supp_reference varchar(60) '' NOT NULL Supp reference 
tran_date date '0000-00-00' NOT NULL 2008-04-29 
due_date date '0000-00-00' NOT NULL 2008-05-15 
ov_amount double 0 NOT NULL 100 
ov_discount double 0 NOT NULL 0 
ov_gst double 0 NOT NULL 5 
rate double 1 NOT NULL 1 
alloc double 0 NOT NULL 0 

Primary key for table 0_supp_trans

Primary Key Name Field Names
PRIMARY trans_no, type

Indexes for table 0_supp_trans, 6 items

Index Name Clustered Unique Fields
PRIMARY No Yes trans_no
PRIMARY No Yes type
supplier_id No No supplier_id
SupplierID_2 No No supplier_id, supp_reference
type No No type

Table: 0_suppliers, 13 fields

Field Name Type and Size Default Nullable Description
supplier_id integer 0 NULL 2001 
supp_name varchar(60) '' NOT NULL Super Trooper 
address tinytext(255) '' NOT NULL The street,city,zip 
email varchar(100) '' NOT NULL delta@delta.com 
bank_account varchar(60) '' NOT NULL 123456789 
curr_code char(3)   NULL USD 
payment_terms integer   NULL 1 
dimension_id integer 0 NULL 0 
dimension2_id integer 0 NULL 0 
tax_group_id integer   NULL 1 
purchase_account varchar(11)   NULL 4000 
payable_account varchar(11)   NULL 1700 
payment_discount_account varchar(11)   NULL 1700 

Primary key for table 0_suppliers

Primary Key Name Field Name
PRIMARY supplier_id

Indexes for table 0_suppliers, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes supplier_id

Table: 0_sys_types, 4 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
type_id smallint 0 NOT NULL 10 
type_name varchar(60) '' NOT NULL Customer Invoice 
type_no integer 1 NOT NULL 1 
next_reference varchar(100) '' NOT NULL 1235 

Primary key for table 0_sys_types

Primary Key Name Field Name
PRIMARY type_id

Indexes for table 0_sys_types, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes type_id

Table: 0_tax_group_items, 4 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
tax_group_id integer 0 NOT NULL 1 
tax_type_id integer 0 NOT NULL 1 
rate double 0 NOT NULL 5 
included_in_price tinyint 0 NOT NULL 0 

Primary key for table 0_tax_group_items

Primary Key Name Field Names
PRIMARY tax_group_id, tax_type_id

Indexes for table 0_tax_group_items, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes tax_group_id
PRIMARY No Yes tax_type_id

Table: 0_tax_groups, 3 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
name varchar(60) '' NOT NULL Tax 5 
tax_shipping tinyint 0 NOT NULL 0 

Primary key for table 0_tax_groups

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_tax_groups, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
name No Yes name

Table: 0_tax_types, 5 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
rate double 0 NOT NULL 5 
sales_gl_code varchar(11) '' NOT NULL 2660 
purchasing_gl_code varchar(11) '' NOT NULL 2680 
name varchar(60) '' NOT NULL Tax 5 

Primary key for table 0_tax_types

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_tax_types, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_users, 21 fields

Field Name Type and Size Default Nullable Description
user_id varchar(60) '' NOT NULL admin 
password varchar(100) '' NOT NULL md5(password) 
real_name varchar(100) '' NOT NULL Administrator 
full_access integer 1 NOT NULL 3 
phone varchar(30) '' NOT NULL 999-999-999 
email varchar(100)   NULL delta@delta.com 
language varchar(20)   NULL en_US 
date_format tinyint 0 NOT NULL 0 (MMDDYYYY) 
date_sep tinyint 0 NOT NULL / 
tho_sep tinyint 0 NOT NULL , 
dec_sep tinyint 0 NOT NULL . 
theme varchar(20) 'default' NOT NULL default 
page_size varchar(20) 'A4' NOT NULL Letter 
prices_dec smallint 2 NOT NULL 2 
qty_dec smallint 2 NOT NULL 2 
rates_dec smallint 4 NOT NULL 4 
percent_dec smallint 1 NOT NULL 1 
show_gl tinyint 1 NOT NULL 1 
show_codes tinyint 0 NOT NULL 1 
show_hints tinyint 0 NOT NULL 0 
last_visit_date datetime   NULL Timestamp 

Primary key for table 0_users

Primary Key Name Field Name
PRIMARY user_id

Indexes for table 0_users, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes user_id

Table: 0_voided, 4 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
type integer 0 NOT NULL 10 (sys_types)
id integer 0 NOT NULL 1
date_ date '0000-00-00' NOT NULL 2008-04-29
memo_ tinytext(255) '' NOT NULL Memo text

Primary key for table 0_voided

Primary Key Name Field Names
PRIMARY type, id

Indexes for table 0_voided, 2 items

Index Name Clustered Unique Fields
id No Yes type, id

Table: 0_wo_issue_items, 4 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
stock_id varchar(40)   NULL 102 (stock id) 
issue_id integer   NULL 1 
qty_issued double   NULL 1 

Primary key for table 0_wo_issue_items

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_wo_issue_items, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_wo_issues, 6 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
issue_no integer 0 NULL 1 
workorder_id integer 0 NOT NULL 1 
reference varchar(100)   NULL Reference 
issue_date date   NULL 2008-04-29 
loc_code varchar(5)   NULL DEF 
workcentre_id integer   NULL 1 

Primary key for table 0_wo_issues

Primary Key Name Field Name
PRIMARY issue_no

Indexes for table 0_wo_issues, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes issue_no

Table: 0_wo_manufacture, 5 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
reference varchar(100)   NULL Reference 
workorder_id integer 0 NOT NULL 1 
quantity double 0 NOT NULL 1 
date_ date '0000-00-00' NOT NULL 2008-04-29 

Primary key for table 0_wo_manufacture

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_wo_manufacture, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_wo_requirements, 8 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
workorder_id integer 0 NOT NULL 1 
stock_id char(20) '' NOT NULL 102 (stock id) 
workcentre integer 0 NOT NULL 1 
units_req double 1 NOT NULL 1 
std_cost double 0 NOT NULL 20 
loc_code char(5) '' NOT NULL DEF 
units_issued double 0 NOT NULL 1 

Primary key for table 0_wo_requirements

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_wo_requirements, 1 item

Index Name Clustered Unique Fields
PRIMARY No Yes id

Table: 0_workcentres, 3 fields

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
name char(40) '' NOT NULL Work Centre 
description char(50) '' NOT NULL In Garage 

Primary key for table 0_workcentres

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_workcentres, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
name No Yes name

Table: 0_workorders, 13 fields, InnoDB free: 7168 kB

Field Name Type and Size Default Nullable Description
id integer 0 NULL 1 
wo_ref varchar(60) '' NOT NULL Reference 
loc_code varchar(5) '' NOT NULL DEF 
units_reqd double 1 NOT NULL 1 
stock_id varchar(20) '' NOT NULL 102 (stock id) 
date_ date '0000-00-00' NOT NULL 2008-04-29 
type tinyint 0 NOT NULL 0 
required_by date '0000-00-00' NOT NULL 2008-04-29 
released_date date '0000-00-00' NOT NULL 2008-04-29 
units_issued double 0 NOT NULL 1 
closed tinyint 0 NOT NULL 0 
released tinyint 0 NOT NULL 0 
additional_costs double 0 NOT NULL 0 

Primary key for table 0_workorders

Primary Key Name Field Name
PRIMARY id

Indexes for table 0_workorders, 2 items

Index Name Clustered Unique Fields
PRIMARY No Yes id
wo_ref No Yes wo_ref

Data Types, 52 items

Type name Code Type and Size Sizeable Nullable Searchable Autoincremenal Scale User Type
bit -7 1 Yes YES YES No   No
tinyint -6 3 Yes YES YES No   No
tinyint unsigned -6 3 Yes YES YES No   No
tinyint auto_increment -6 3 Yes NO YES Yes   No
tinyint unsigned auto_increment -6 3 Yes NO YES Yes   No
bigint -5 19 Yes YES YES No   No
bigint unsigned -5 20 Yes YES YES No   No
bigint auto_increment -5 19 Yes NO YES Yes   No
bigint unsigned auto_increment -5 20 Yes NO YES Yes   No
long varbinary -4 16777215 Yes YES YES No   No
blob -4 65535 Yes YES YES No   No
longblob -4 2147483647 Yes YES YES No   No
tinyblob -4 255 Yes YES YES No   No
mediumblob -4 16777215 Yes YES YES No   No
varbinary -3 255 Yes YES YES No   No
binary -2 255 Yes YES YES No   No
long varchar -1 16777215 Yes YES YES No   No
text -1 65535 Yes YES YES No   No
mediumtext -1 16777215 Yes YES YES No   No
longtext -1 2147483647 Yes YES YES No   No
tinytext -1 255 Yes YES YES No   No
char 1 255 Yes YES YES No   No
numeric 2 19 Yes YES YES No 0..19 No
decimal 3 19 Yes YES YES No 0..19 No
integer 4 10 Yes YES YES No   No
integer unsigned 4 10 Yes YES YES No   No
int 4 10 Yes YES YES No   No
int unsigned 4 10 Yes YES YES No   No
mediumint 4 7 Yes YES YES No   No
mediumint unsigned 4 8 Yes YES YES No   No
integer auto_increment 4 10 Yes NO YES Yes   No
integer unsigned auto_increment 4 10 Yes NO YES Yes   No
int auto_increment 4 10 Yes NO YES Yes   No
int unsigned auto_increment 4 10 Yes NO YES Yes   No
mediumint auto_increment 4 7 Yes NO YES Yes   No
mediumint unsigned auto_increment 4 8 Yes NO YES Yes   No
smallint 5 5 Yes YES YES No   No
smallint unsigned 5 5 Yes YES YES No   No
smallint auto_increment 5 5 Yes NO YES Yes   No
smallint unsigned auto_increment 5 5 Yes NO YES Yes   No
double 6 15 Yes YES YES No 0..4 No
double auto_increment 6 15 Yes NO YES Yes 0..4 No
float 7 7 Yes YES NO No 0..2 No
float auto_increment 7 7 Yes NO NO Yes 0..2 No
double 8 15 Yes YES YES No 0..4 No
double auto_increment 8 15 Yes NO YES Yes 0..4 No
date 9 10 Yes YES YES No   No
time 10 8 Yes YES YES No   No
year 5 4 Yes YES YES No   No
datetime 11 21 No YES YES No   No
timestamp 11 14 No NO YES No   No
varchar 12 255 Yes YES YES No   No