Dumped on 2008-08-19

Index of database - techfile

Table: cbocountry

Lookup table for country on order

cbocountry Structure
F-Key Name Type Description
country character varying(15) PRIMARY KEY

destination country

Index - Schema public

Table: cbocourier

Lookup table for courier on order

cbocourier Structure
F-Key Name Type Description
courier character varying(25) PRIMARY KEY

despatch courier

Index - Schema public

Table: cbomachineuse

Lookup table for current use of a product

cbomachineuse Structure
F-Key Name Type Description
machineuse character varying(11) PRIMARY KEY

field describing machine usage (varchar 11)

Index - Schema public

Table: cbomanual

Lookup table for current manuals available (soon to be obsoleted)

cbomanual Structure
F-Key Name Type Description
cbomanual character varying(50) PRIMARY KEY

Index - Schema public

Table: cbopcbtype

lookup for pcb data entry

cbopcbtype Structure
F-Key Name Type Description
pcbtype character varying(10) PRIMARY KEY

field describing pcb stock codes (non-balloon) (varchar 10)

Index - Schema public

Table: t_auth_groups

table to describe the different functional TCL groupings for intranet authorisation

t_auth_groups Structure
F-Key Name Type Description
group_id integer PRIMARY KEY DEFAULT nextval('group_id_seq'::text)

primary key
group_name character varying(20)

group name

Tables referencing this one via Foreign Key Constraints:

idx_group group_id

Index - Schema public

Table: t_bom

Table to record a component of a product and its type

t_bom Structure
F-Key Name Type Description
bom_id integer PRIMARY KEY DEFAULT nextval('bom_serial'::text)
bom_item character varying(50)
bom_date date DEFAULT ('now'::text)::date

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_bug

Something to do with software bugs reported by sales team but never got used

t_bug Structure
F-Key Name Type Description
bug_id integer PRIMARY KEY DEFAULT nextval('bug_serial'::text)
bug_detail text
bug_date date DEFAULT date('now'::text)
bug_status boolean
bug_action text
idx_bug_id bug_id idx_bug_status bug_status

Index - Schema public

Table: t_cpld

Lookup table for balloon cpld types

t_cpld Structure
F-Key Name Type Description
cpld character varying(40) PRIMARY KEY
cplddesc text
cpld_dir character varying(50)
current smallint
idx_cpld cpld

Index - Schema public

Table: t_cust

Linked customer table for t_bug - can be obsoleted

t_cust Structure
F-Key Name Type Description
cust_id integer PRIMARY KEY DEFAULT nextval('cust_serial'::text)
cust_date date
cust_name character varying(60)
cust_code character varying(8)
cust_address text
cust_postcode character varying(10)
cust_phone character varying(30)
cust_mail character varying(60)
rep_name character varying(60)
bug_id integer
idx_cust_name cust_name idx_cust_postcode cust_postcode

Index - Schema public

Table: t_customer

table containing customer data

t_customer Structure
F-Key Name Type Description
customer_id integer PRIMARY KEY DEFAULT nextval('customer_id_seq'::text)

primary key (integer)
customer_code character varying(8) UNIQUE

Dimensions customer account code (varchar 8)

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_customer_contact

List of names and email linked to a customer account

t_customer_contact Structure
F-Key Name Type Description
contact_id integer PRIMARY KEY DEFAULT nextval('contact_id_seq'::text)

primary key (integer)
t_customer.customer_id customer_id integer UNIQUE#1

foreign key linked to t_customer (integer)
name character varying(40) UNIQUE#1

Name of contact as will appear on auto-email alerts (varchar 40)
email character varying(64) UNIQUE#1

email address for contact (varchar 64)
option boolean

flag to indicate whether contact wishes alerts or not via email (boolean)

Index - Schema public

Table: t_data_changes

Table to track changes made to production data by a user

t_data_changes Structure
F-Key Name Type Description
dc_id integer PRIMARY KEY DEFAULT nextval('t_data_ch_seq'::text)

primary key (integer)
dc_date date

date of change made to data (date)
dc_name character varying(15)

log in name of current user making change (varchar 15)
dc_str text

reason for change (text)
dc_prodid integer

production id affected by change (integer)

Index - Schema public

Table: t_hwbom

table updated daily from Dimensions with the hardware bom for a serialised product

t_hwbom Structure
F-Key Name Type Description
hwbom_id integer DEFAULT nextval('hwbom_id_seq'::text)

unique identifier, primary key
tblfinalbuild.spec_id spec_id integer UNIQUE#1

serialised spec id from tblfinalbuild for which we are tracing the hwbom
tblfinalbuild.spec_id spec_id_bom integer UNIQUE#1

component spec_id from tblfinalbuild is part of build
bom_level integer UNIQUE#1

level of spec this spec_bom is found at in the build
date_from date DEFAULT '2007-05-14'::date

date record inserted
date_to date

date record superceded by new spec+spec_id set of info
quantity integer UNIQUE#1

number of spec_bom in spec
supplier character varying(8) UNIQUE#1

supplier code from Dimensions

Index - Schema public

Table: t_ipkg

Balloon ipkg table

t_ipkg Structure
F-Key Name Type Description
ipkg_id integer PRIMARY KEY DEFAULT nextval('ipkg_serial'::text)

primary key
ipkg_ipkg character varying(45)

ipkg directory name (less arm extension)
ipkg_desc text

description of the ipkg
installable boolean DEFAULT true

boolean to indicate if ipkg is to be offered for install on megadongle
directoryname character varying(100)

original directory in which ipkg arm directory is found
synth character varying(10)

column required for megadongle to send the correct lock command for synth to speak
lang character varying(2)

column required for megadongle to send the correct lock command for lang to speak
voice character varying(6)

column required for megadongle to send the correct lock command for voice to speak
idx_ipkg_ipkg ipkg_ipkg

Index - Schema public

Table: t_ipkg_archive

Records state of a particular ipkg version. NB: No longer applicable, use t_product_release

t_ipkg_archive Structure
F-Key Name Type Description
ipkg_archive_id integer PRIMARY KEY DEFAULT nextval('ipkg_archive_serial'::text)

primary key
ipkg_version_id integer NOT NULL

foreign key link to t_ipkg_version
ipkg_archive_state character varying(15)

state of ipkg, eg PRODUCTION,TESTING,STABLE,UNSTABLE,ARCHIVE
ipkg_archive_date date

date record inserted
ipkg_archive_name character varying(15)

name of person logging the state
ipkg_archive_current smallint

boolean flag to indicate if state is current
idx_ipkg_archive_id ipkg_archive_id idx_ipkg_archive_name ipkg_archive_name

Index - Schema public

Table: t_ipkg_dependency

Records ipkgs dependent upon each other for megadongle to look up and install

t_ipkg_dependency Structure
F-Key Name Type Description
ipkg_version_id integer PRIMARY KEY

foreign key linked to ipkg_version_id
ipkg_id integer PRIMARY KEY

foreign key linked to t_ipkg - indicates what ipkg to look up and install current version in same release for an ipkg_version_id

Index - Schema public

Table: t_ipkg_key

holds key-generating info for an ipkg

t_ipkg_key Structure
F-Key Name Type Description
key_id integer PRIMARY KEY DEFAULT nextval('ipkg_key_serial'::text)

primary key
t_ipkg_version.ipkg_version_id ipkg_version_id integer UNIQUE#1 NOT NULL

foreign key - link to ipkg version table key
key character varying(20) UNIQUE#1 NOT NULL

code for ttscrypt
conf character varying(20) UNIQUE#1 NOT NULL

conf file for loon installer setconf command
depends character varying(20)

indicates core that runs the encryption algorithm
idx_key_id key_id idx_key_ipkg_id ipkg_version_id

Index - Schema public

Table: t_ipkg_release_version

link table to show which ipkg versions are in an ipkg release collection

t_ipkg_release_version Structure
F-Key Name Type Description
t_release_ipkg.ipkg_rel_id ipkg_rel_id integer PRIMARY KEY

foreign key references t_release_ipkg
t_ipkg_version.ipkg_version_id ipkg_version_id integer PRIMARY KEY

foreign key references t_ipkg_version

Index - Schema public

Table: t_ipkg_required

Records ipkgs required to be automatically installed by megadongle

t_ipkg_required Structure
F-Key Name Type Description
ipkg_id integer PRIMARY KEY

foreign key linked to t_ipkg and looked up by megadongle code

Index - Schema public

Table: t_ipkg_version

Records versions of each balloon ipkg in t_ipkg

t_ipkg_version Structure
F-Key Name Type Description
ipkg_version_id integer PRIMARY KEY DEFAULT nextval('ipkg_version_serial'::text)

primary key
ipkg_id integer NOT NULL

foreign key linked to t_ipkg: one ipkg may have many versions
ipkg_filename character varying(50)

string filename for this ipkg version
ipkg_version character varying(20)

version number of this ipkg, from the parent control file
ipkg_size integer

size of the built ipkg
ipkg_desc text

description of ipkg version: this supercedes the ipkg description really
ipkg_date date DEFAULT date('now'::text)

date this version is first recorded in the database
license boolean

true if this version of speech ipkg requires license

Tables referencing this one via Foreign Key Constraints:

idx_ipkg_date ipkg_date idx_ipkg_filename ipkg_filename

Index - Schema public

Table: t_loon

Balloon board main table

t_loon Structure
F-Key Name Type Description
loon_id integer PRIMARY KEY DEFAULT nextval('loon_serial'::text)

primary key
loon_onboard character varying(16)

balloon embedded onboard ID
loon_barric character varying(10)

PCB ID of balloon given by Barric manufacturer
loon_date_in_stock date DEFAULT date('now'::text)

Date this balloon board entered into techfile database
type_stockcode character varying(20)

type of balloon, stock code matches Dimensions, linked to t_loon_type, should be replaced with t_loon_type.type_ID asap
state character varying(1)

state board is in at one time: V=uninitialised; P=programmed;R=in for repair; D=assigned as R&D/demo;A=available for production

Tables referencing this one via Foreign Key Constraints:

idx_loon_barric loon_barric idx_loon_id loon_id idx_loon_onboard loon_onboard idx_loon_type type_stockcode

Index - Schema public

Table: t_loon_base

Records base installs made on a balloon in t_loon

t_loon_base Structure
F-Key Name Type Description
loon_base_id integer PRIMARY KEY DEFAULT nextval('base_serial'::text)

primary key
loon_id integer NOT NULL

foreign key link to t_loon: one board can have many base installs
loon_base_date date DEFAULT date('now'::text)

date of this base install
cpld character varying(40)

cpld image used: obsolete, replace this data with the correct swrel_id for each record and drop column
prodimgs character varying(8)

prodimgs used: obsolete, replace this data with the correct swrel_id for each record and drop column
inst_name character varying(30)

name of user logged into megadongle programmer performing base install
t_swrelease.swrel_id swrel_id integer

foreign key, software release installed on the balloon board
t_release_ipkg.ipkg_rel_id ipkg_rel_id integer

foreign key: ipkg release selected for this board, so megadongle can display correct ipkgs forinstall
idx_loon_base_id loon_base_id idx_loon_cpld cpld idx_loon_loon_id loon_id

Index - Schema public

Table: t_loon_ipkg

Records ipkg versions from t_ipkg_version as installed on balloon in t_loon

t_loon_ipkg Structure
F-Key Name Type Description
loon_ipkg_id integer PRIMARY KEY DEFAULT nextval('loon_ipkg_serial'::text)

primary key
loon_id integer

foreign key link to t_loon
ipkg_version_id integer

foreign key link to t_ipkg_version
inst_date date DEFAULT date('now'::text)

date this ipkg version installed on this balloon
inst_name character varying(30)

name of person requesting the install
rem_date date

date this ipkg removed from this balloon where applicable
state character varying(10)

string column state will be INSTALLED but set to REMOVED where applicable
locked boolean

boolean set to true if installed ipkg has been locked, false if not locked. pre-lock-mechanism records may show null

Tables referencing this one via Foreign Key Constraints:

idx_lh_inst inst_date idx_lh_ipkg ipkg_version_id idx_lh_loon loon_id

Index - Schema public

Table: t_loon_key

holds license key info for a balloon

t_loon_key Structure
F-Key Name Type Description
loon_key_id integer PRIMARY KEY DEFAULT nextval('loon_key_serial'::text)

primary key
t_loon_ipkg.loon_ipkg_id loon_ipkg_id integer NOT NULL

foreign key - link to t_loon_ipkg table
loon_ipkg_key character varying(6) NOT NULL

key value for ttscrypt for this loon and this voice
loon_ipkg_date date

date voice unlocked
ttscrypt character varying(40)

version of core used to create key
unlocker character varying(15)

user name of unlock function
configured boolean DEFAULT false

false after install per key until specifically locked or unlocked by megadongle configure pages
idx_loon_key_id loon_key_id idx_loon_key_ipkg_id loon_ipkg_id

Index - Schema public

Table: t_loon_notes

Records comments about balloon in t_loon

t_loon_notes Structure
F-Key Name Type Description
loon_notes_id integer PRIMARY KEY DEFAULT nextval('loon_notes_serial'::text)

primary key
loon_id integer NOT NULL

foreign key link to t_loon
loon_notes_date date

date note entered
loon_notes_name character varying(20)

name of person entering note
loon_notes text

text field for the note about the balloon board
idx_lnloon_id loon_id idx_loon_notes_id loon_notes_id

Index - Schema public

Table: t_loon_repair

Balloon board repair table linked to t_loon

t_loon_repair Structure
F-Key Name Type Description
loon_repair_id integer PRIMARY KEY DEFAULT nextval('loon_repair_serial'::text)

primary key (integer)
indate date

date record added to table (date)
inname character varying(15)

name of person entering record (varchar 15)
fault text

description of the fault found with this board (text)
outdate date

date board flagged as being repaired (date)
outname character varying(15)

name of person flagging board as fixed (varchar 15)
repair text

details of repair (text)
fixed boolean

flag set to true when board is fixed (boolean)
mserial character varying(15)

ummm...er...(varchar 15)
t_loon.loon_id loon_id integer

foreign key linked to t_loon (integer)
idx_loon_fixed fixed

Index - Schema public

Table: t_loon_swrel

t_loon_swrel Structure
F-Key Name Type Description
type_id integer

foreign key link to t_loon_type (integer)
t_swrelease.swrel_id swrel_id integer

foreign key link to t_swrelease (integer)
current boolean

flag to indicate if record is currently true or false (boolean)

Index - Schema public

Table: t_loon_type

Lookup table to types of balloon board

t_loon_type Structure
F-Key Name Type Description
type_stockcode character varying(20) PRIMARY KEY

Dimensions stock code number for a balloon board type (varchar 20 not null)
type_desc text

Dimensions description for this board type (text)
type_nand integer

Size of onboard NAND for megadongle look-up to see if an ipkg can be installed (integer)
type_proc integer

size of processor on this ballon type (integer)
type_ram integer

size of RAM on this balloon type (integer)
obsolete boolean

flagged as true when board obsoleted (boolean)
type_id integer DEFAULT nextval('loontype_id_seq'::text)

primary key (integer)
idx_type_stock type_stockcode

Index - Schema public

Table: t_mainboard

Table to track individual mainboards

t_mainboard Structure
F-Key Name Type Description
mainboard_id integer PRIMARY KEY

Mainboard ID
mainboard_stockcode character varying(20)

Stockcode on board
mainboard_date date

Date board booked in to TCL
mainboard_state character varying(1)

Current production state of board
mainboard_type_id integer NOT NULL

Type ID from t_mainboard_type

Index - Schema public

Table: t_mainboard_type

Table to track mainboard revisions

t_mainboard_type Structure
F-Key Name Type Description
mainboard_rev character varying(20)

The mainboard revision code
mainboard_desc text

A description of the mainboard revision
mainboard_type_id integer PRIMARY KEY

The id for the revision
obsolete bit(1)

True or false obsolesence of mainboard

Index - Schema public

Table: t_pcb_track

table to track pcbs, superceding tblpcbserial pending data import

t_pcb_track Structure
F-Key Name Type Description
pcb_id integer PRIMARY KEY DEFAULT nextval('pcb_id_seq'::text)

primary key (integer)
pcb_serial character varying(10) UNIQUE

pcb unique serial number (varchar 10)
pcb_date date DEFAULT '2007-05-14'::date

date record entered (date)
delivery_ref integer

Our purchase order reference (integer)
batch_ref character varying(20)

batch reference (varchar 20)
t_supplier.supplier_ref_id supplier_ref_id integer

Suppliers reference (integer)

Index - Schema public

Table: t_php_auth

Intranet site user log-in and authorisation level table

t_php_auth Structure
F-Key Name Type Description
id integer PRIMARY KEY DEFAULT nextval('t_php_auth_id_seq'::text)

primary key
name character varying(20)

user log-in name for intranet site
passwd character varying(10)

user log-in password for intranet site
email character varying(30)

default email address for this user

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_php_auth_groups

table linking people in t_php_auth with groups in t_auth_groups

t_php_auth_groups Structure
F-Key Name Type Description
t_php_auth.id id integer

foreign key linked to t_php_auth
t_auth_groups.group_id group_id integer

foreign key linked to t_auth_group

Index - Schema public

Table: t_prodimgs

Lookup table for t_loon/megadongle to find prodimgs - soon to be obsoleted

t_prodimgs Structure
F-Key Name Type Description
prodimgs character varying(8) PRIMARY KEY

matches directory name containing balloon core software bundle (varchar 8)
prod_desc text

describes prodimgs (text)
current smallint

flags if software current or not (boolean)

Index - Schema public

Table: t_product_release

product release table links a product with which releases it has had

t_product_release Structure
F-Key Name Type Description
spec_id integer PRIMARY KEY

foreign key link to tblfinalbuild (integer)
t_release.release_id release_id integer PRIMARY KEY

foreign key link to t_release (integer)
state character varying(20) PRIMARY KEY

state of this release for this product, ie PRODUCTION, UPGRADE, ARCHIVE etc (varchar 20 not null)

Index - Schema public

Table: t_project

R&D projects table

t_project Structure
F-Key Name Type Description
proj_id integer PRIMARY KEY DEFAULT nextval('proj_serial'::text)
proj_title character varying(40)
proj_status character varying(10)
proj_overview text
proj_name character varying(20)
proj_sdate date
proj_fdate date
proj_parent_id integer

Tables referencing this one via Foreign Key Constraints:

idx_proj_id proj_id idx_proj_status proj_status

Index - Schema public

Table: t_project_ce

Projects ce information linked to t_project

t_project_ce Structure
F-Key Name Type Description
ce_id integer PRIMARY KEY DEFAULT nextval('proj_ce_serial'::text)
proj_id integer NOT NULL
ce_date date
ce_notes text
idx_ce_id ce_id idx_ce_proj_id proj_id

Index - Schema public

Table: t_project_changes

Projects changes information linked to t_project

t_project_changes Structure
F-Key Name Type Description
change_id integer PRIMARY KEY DEFAULT nextval('proj_change_serial'::text)
t_project.proj_id proj_id integer NOT NULL
change_date date DEFAULT ('now'::text)::date
change_ref character varying(15)
change_pre text
change_post text
change_name character varying(20)
idx_change_ref_id change_ref idx_proj_ch_proj_id proj_id idx_proj_change_id change_id

Index - Schema public

Table: t_project_doc

t_project_doc Structure
F-Key Name Type Description
doc_id integer PRIMARY KEY DEFAULT nextval('proj_doc_serial'::text)
t_project.proj_id proj_id integer
doc_filename character varying(25)

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_project_doc_perm

t_project_doc_perm Structure
F-Key Name Type Description
t_project_doc.doc_id doc_id integer PRIMARY KEY
t_php_auth.id auth_id integer PRIMARY KEY
doc_read boolean NOT NULL DEFAULT false
doc_write boolean NOT NULL DEFAULT false

Index - Schema public

Table: t_project_notes

Projects notes linked to t_project

t_project_notes Structure
F-Key Name Type Description
notes_id integer PRIMARY KEY DEFAULT nextval('proj_notes_serial'::text)
proj_id integer NOT NULL
notes_date date
notes_name character varying(20)
notes text
idx_notes_id notes_id idx_notes_proj_id proj_id

Index - Schema public

Table: t_project_perm

t_project_perm Structure
F-Key Name Type Description
t_project.proj_id proj_perm_id integer PRIMARY KEY
t_php_auth.id auth_id integer PRIMARY KEY
proj_read boolean NOT NULL DEFAULT false
proj_write boolean NOT NULL DEFAULT false

Index - Schema public

Table: t_project_status

Lookup table for status of project in t_project

t_project_status Structure
F-Key Name Type Description
status character varying(10) PRIMARY KEY
idx_proj_status2 status

Index - Schema public

Table: t_release

Main table of product releases past and present

t_release Structure
F-Key Name Type Description
release_id integer PRIMARY KEY DEFAULT nextval('release_serial'::text)

primary key (integer)
release character varying(45) NOT NULL

name of release (varchar 45)
release_date date DEFAULT ('now'::text)::date

date release added to database (date)
release_notes text

description of the release (text)
t_swrelease.swrel_id swrel_id integer

foreign key link to t_swrelease, the software that goes in this product release (integer)
t_bom.bom_id bom_id integer

foreign key link to t_bom: use the new t_hwbom method of tracking hwboms in patch v6.0.4 then drop this
t_release_ipkg.ipkg_rel_id ipkg_rel_id integer

foreign key link to an ipkg release to be used on this product release: not implemented as such

Tables referencing this one via Foreign Key Constraints:

idx_release release idx_release_id release_id

Index - Schema public

Table: t_release_ipkg

collections of ipkgs that can go together in different product releases

t_release_ipkg Structure
F-Key Name Type Description
ipkg_rel_id integer PRIMARY KEY DEFAULT nextval(' ipkg_rel_serial '::text)

primary key unique integer
ipkg_rel_item character varying(50)

brief description of ipkg release 50 chars
ipkg_rel_date date DEFAULT now()

date entry made

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_serial_release

links an instance of a product to a release

t_serial_release Structure
F-Key Name Type Description
sr_id integer PRIMARY KEY DEFAULT nextval('sr_serial'::text)

primary key (integer)
tblproduction.productionid productionid integer NOT NULL

foreign key link to tblproduction (integer)
t_release.release_id release_id integer NOT NULL

foreign key link to t_release (integer)
sr_date date

date this release assigned to this product instance (date)
sr_current boolean DEFAULT true

shows true until this production id is later linked to a different release (boolean)

Index - Schema public

Table: t_supplier

table to track pcb suppliers

t_supplier Structure
F-Key Name Type Description
supplier_ref_id integer PRIMARY KEY DEFAULT nextval('supplier_id_seq'::text)

primary key (integer)
supplier character varying(10)

name of supplier (varchar 10)

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_swcore

Individual software items that make up a swrelease

t_swcore Structure
F-Key Name Type Description
swcore_id integer PRIMARY KEY DEFAULT nextval('swcore_id_seq'::text)

primary key unique integer
swcore_item character varying(70)

name of software component 70 chars
swcore_date date DEFAULT now()

date entry made
swcore_dir character varying(50)

directory under tclsoftware library where file is kept (varchar 50)
swcore_desc text

brief description of record, text field
t_swcore_type.swtype_id swtype_id integer

foreign key linked to t_swcore_type

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_swcore_type

t_swcore_type Structure
F-Key Name Type Description
swtype_id integer PRIMARY KEY DEFAULT nextval('swtype_id_seq'::text)

primary key
swtype character varying(10)

type of core software, column required for megadongle automated loon programming,10 chars

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_swrelease

Assembly name for a collective bunch of software core items

t_swrelease Structure
F-Key Name Type Description
swrel_id integer PRIMARY KEY DEFAULT nextval('swrel_id_seq'::text)

primary key unique integer
swrel_item character varying(40)

name of software release 40 chars
swrel_date date DEFAULT now()

date swrelease entered into db

Tables referencing this one via Foreign Key Constraints:

Index - Schema public

Table: t_swrelease_swcore

link table listing software components in the swrelease assembly

t_swrelease_swcore Structure
F-Key Name Type Description
t_swrelease.swrel_id swrel_id integer PRIMARY KEY

foreign key references t_swrelease
t_swcore.swcore_id swcore_id integer PRIMARY KEY

foreign key references t_swcore

Index - Schema public

Table: t_swversion

lookup for roms - to be dropped

t_swversion Structure
F-Key Name Type Description
swversion character varying(20) PRIMARY KEY
idx_swversion swversion

Index - Schema public

Table: tblbom

contains hw bom info for previous version of R&D projects database - can possibly be dropped

tblbom Structure
F-Key Name Type Description
bomid integer PRIMARY KEY DEFAULT nextval('seq_bomid'::text)
components character varying(50)
projectid integer
isnew smallint
details character varying(50)
pcbid character varying(10)
access_part_number character varying(7)
supplier character varying(10)
id_tblbom_pcbid pcbid

Index - Schema public

Table: tblce

contains ce info for previous version of R&D projects database - can possibly be dropped

tblce Structure
F-Key Name Type Description
ceid integer PRIMARY KEY DEFAULT nextval('seq_ceid'::text)
projectid integer
cedate date
cetester character varying(15)
id_tblce_projectid projectid

Index - Schema public

Table: tblchangelink

Link table to allow many change notes to apply to many products

tblchangelink Structure
F-Key Name Type Description
changelinkid integer PRIMARY KEY DEFAULT nextval('seq_changelinkid'::text)

primary key (integer)
changenotesid integer NOT NULL

foreign key references tblchangenotes (integer)
code character varying(20)

relates to spec in tblfinalbuild, could be updated to the newer spec_id PK (varchar 20)
id_tblchangelink_changenotesid changenotesid

Index - Schema public

Table: tblchangenotes

Change notes table

tblchangenotes Structure
F-Key Name Type Description
changenotesid integer PRIMARY KEY DEFAULT nextval('seq_changenotesid'::text)

primary key (integer)
dateofchange date

date change made (date)
enteredby character varying(10)

name of person entering the change note data (varchar 10)
changeinitiated character varying(15)

name of person requesting the change (varchar 15)
changeauthorised character varying(15)

name of manager authorising the change (varchar 15)
reasonforchange text

reason the change is being implemented (text)
changemethod text

how the change is to be effected (text)
changenoteno integer NOT NULL

number of the change note (integer)
modelupdated smallint

column supposed to be 0 if model not been updated to match change, -1 if it has: doubt this is used (smallint)
serialbuild character varying(50)

machine serialnumber the change takes effect from: this would be much better described by date of production (varchar 50)
serialpcb character varying(25)

pcb serialnumber the change takes effect from: this would be much better described by date of production (varchar 25)

Index - Schema public

Table: tblcomments

contains comments for previous version of R&D projects database - can possibly be dropped

tblcomments Structure
F-Key Name Type Description
commentid integer PRIMARY KEY DEFAULT nextval('seq_commentid'::text)
projectid integer
datecomment date
comment text
hyperlink character varying(50)
id_tblcomments_projectid projectid

Index - Schema public

Table: tblconfig

holds database versioning details

tblconfig Structure
F-Key Name Type Description
recordno integer PRIMARY KEY DEFAULT nextval('seq_configrecordno'::text)

primary key (integer)
versionno character varying(50)

version string for db to which this entry relates (varchar 50)
date_changed date

date new version rolled out
script character varying(50)

where to find the patch in svn

Index - Schema public

Table: tblerrors

Table that msaccess front end writes errors to

tblerrors Structure
F-Key Name Type Description
errorid integer PRIMARY KEY DEFAULT nextval('seq_errorid'::text)
errorno integer NOT NULL
errordescription text
username character varying(30)
entrydateold timestamp with time zone
functionname character varying(100)
entrydate timestamp(0) with time zone
id_tblerrors_errorno errorno

Index - Schema public

Table: tblfinalbuild

Product specification table

tblfinalbuild Structure
F-Key Name Type Description
spec character varying(22) NOT NULL

Dimensions stock code for a product or component varchar(22)
serialised smallint

0 if no serial number, -1 if has a serial number (smallint)
description text

description of spec, should match Dimensions (text)
comment character varying(255)

in-house comments pertaining to this product (varchar 255)
shortage smallint

used to be flagged as -1 when this db helped monitor stock levels: can be obsoleted (smallint)
bought_in smallint

flagged as -1 if this spec is bought in rather than made in house, else 0 (smallint)
clicks smallint

flagged as -1 if this spec is despatched with click switches, else 0 (smallint)
neckstraps smallint
screwdrivers smallint

flagged as -1 if this spec is despatched with a screwdriver, else 0 (smallint)
remotekbd smallint

flagged as -1 if this spec is despatched with a remote keyboard, else 0 (smallint)
manual character varying(50)

type of manual this spec is despatched with, selected from cbomanual and should really be set up to be a foreign key (varchar 50)
con077a smallint

flagged as -1 if this spec is despatched with a charger extension lead CON077A, else 0 (smallint)
advocate smallint

flagged as -1 if this spec is of the advocate family, else 0 (smallint)
hidden smallint

flagged as -1 if this spec is NOT required to appear on the packing list, else 0 (smallint)
emballoon smallint

flagged as -1 if this spec has an embedded balloon board, else 0 (smallint)
spec_id integer PRIMARY KEY DEFAULT nextval('spec_serial'::text)

primary key (integer)
pcb_qty integer DEFAULT 1

number of pcb (non-balloon) this spec has, look-up for build paperwork (integer)
addserial boolean

true if adding orders module is required to generate a serial number for this item, else false (boolean)

Tables referencing this one via Foreign Key Constraints:

id_tblfinalbuild_serialised serialised

Index - Schema public

Table: tblfrmaddordersopen

lookup table from msaccess TCLgui.mdb to prevent multiple data entry: drop table when TCLgui is dropped

tblfrmaddordersopen Structure
F-Key Name Type Description
recordno integer PRIMARY KEY DEFAULT nextval('seq_addordersopenrecordno'::text)
formopen smallint
swopen smallint
rdswopen smallint
formuser character varying(10)

Index - Schema public

Table: tblpcb_seriallink

link table for pcb and product info. will be superceded by the new pcb table and software release system but data must be collated and imported before dropping this table

tblpcb_seriallink Structure
F-Key Name Type Description
pcbseriallinkid integer PRIMARY KEY DEFAULT nextval('seq_pcbseriallinkid'::text)

primary key (integer)
serialnumber character varying(10)

link to production table (varchar 10)
pcbserialnumber integer

link to pcb table (integer)
softwareissue character varying(20)

issue of software: data now obsolete and software release table used instead (varchar 20)
boardissue character varying(10)

issue of pcb: drop this as info duplicated in pcb table, first check data (varchar 20)
id_tblpcb_seriallink_pcbserialn pcbserialnumber id_tblpcb_seriallink_serialnumb serialnumber id_tblpcb_seriallink_softwareis softwareissue

Index - Schema public

Table: tblpcbserialnumber

main inhouse pcb table: I suggest this data is imported asap into the new pcb table in patch v6.0.4 and obsoleted

tblpcbserialnumber Structure
F-Key Name Type Description
pcbserialnumber integer PRIMARY KEY

unique identifier, primary key, pcb serial number (integer)
datebuilt date

date pcb built (date)
builtby character varying(20)

name of person who built the pcb (varchar 20)
testedby character varying(20)

name of person who tested the pcb (varchar 20)
comments text

comments aboutt his particular pcb (text)
pcbtype character varying(10)

stock code (type) of pcb (varchar 10)
boardissue character varying(4)

issue number of pcb board type (varchar 4)
deleted boolean DEFAULT false

true if pcb is obsoleted but we require the record to be kept (boolean)
idx_pcbdeleted deleted

Index - Schema public

Table: tblproduction

Table having main details for an instance of a product in tblFinalBuild

tblproduction Structure
F-Key Name Type Description
productionid integer PRIMARY KEY DEFAULT nextval('seq_productionid'::text)

primary key (integer)
spec character varying(25)

stock code - obsolete when TCLgui dropped as foreign key in intranet references spec_id (varchar 25)
serialnumber character varying(20)

unique serial number for product (varchar 20)
due_date date

date the item is due to be supplied to customer, taken from Dimensions (date)
builtby character varying(12)

name of person who built the item (varchar 12)
testedby character varying(12)

name of person who tested the built item (varchar 12)
comments text

comments/notes/build details on this item (text)
orderaa integer

Dimensions order number this item is on (integer)
picked smallint

flag set to -1 when item has been picked, else 0 (smallint)
built smallint

flag set to -1 when item has been built, else 0 (smallint)
builtaa smallint

flag set to -1 when item has been selected as work in progress, else 0 (smallint)
despatched smallint

flag is set to -1 when item has been despatched, else 0 (smallint)
returned smallint

flag is set to -1 when item has been returned and not re-despatched, else 0 (smallint)
history smallint

flag is set to -1 when item has return history, else 0 (smallint)
entereddate date

date this record was entered in the database (date)
enteredby character varying(10)

name of the person adding the record (varchar 10)
customerid character varying(7)

customer account code from Dimensions. Should be superceded by customer_id foreign key as soon as front end references and sql changed (varchar 7)
machineuse character varying(11)

current state item is in, eg NORMAL, SOR, LOAN. Should be replaced as FK and a matching integer PK made in cbomachineuse really (varchar 11)
confreportprinted smallint

flag set to -1 when item has been run off on an order conf (to avoid duplication in builds), else 0 (smallint)
gafd smallint

state tracking the item through the workflow process and controlling email alerts, 0 when associated order first goes on, 1 when order email sent to customer, 2 when order has packing list printed and email sent, 3 when order is ticked as despatched or held and email sent (smallint)
fiveyrguarantee smallint

flagged as -1 if customer has paid for a five year guarantee on this item (smallint)
buybackprice double precision

price put on a buy back machine, very seldom used and can possibly be dropped (double precision)
customerref character varying(15)

customer order reference, from Dimensions (varchar 30)
brokendown smallint

flagged as -1 if machine has been dismantled (smallint)
needsrevamping smallint

flagged as -1 if machine is waiting to be done up for resale (smallint)
repairdate date

I really do not know what this column is for!! sorry!! looks like it has not been used for a long time (date)
country character varying(15)

country of item destination, typed in manually. Will be obsoleted when intranet orders module working as address brought from Dimensions on order conf (varchar 15)
courier character varying(50)

courier assigned to deliver item/order (varchar 50)
accessory smallint

flag set to -1 for entire order if found to be accessory only. Can be obsoleted with demise of msaccess tclgui.mdb as php supplies function (smallint)
urgent smallint
despatcheddate date

date item/order was despatched (date)
accupdated smallint

flag set to -1 when type of order (serial or accessory) function has run. can be obsoleted with demise of msaccess tclgui.mdb (smallint)
boolsor smallint

? (smallint)
nextserial integer

column required to hold prefix of serial number for the serialising function, as access thinks 9 is bigger than 10. obsolete when using intranet orders module instead of tclgui (integer)
pickedby character varying(10)

name of person picking item on order (varchar 10)
held character varying(255)

reason for an order having been held (varchar 255)
quantity integer

number of accessory items on order can be summed here, 1 if serialised (integer)
finaltest smallint

flagged as -1 when item has had a final test inspection (smallint)
despatchtest smallint

flagged as -1 when item has had final despatch test (smallint)
orderaaorig integer

makes a copy of original orderaa if item is moved to new order (integer)
customeridorig character varying(7)

makes a copy of original customerid if item is moved to new order (varchar 7)
mnda character varying(20)

if order is for MNDA, their serial number is entered in this column (varchar 20)
pickeddate date

date item was picked (date)
wipdate date

date item had work started on it....not used, do drop (date)
builtdate date

date item build details entered (date)
reprint smallint

flag set to -1 if orderconf is selected for reprint. Can replace by querying GAFD status in intranet php (smallint)
loon_id integer

foreign key link to t_loon for an emballoon machine (integer)
tblfinalbuild.spec_id spec_id integer

foreign key link to tblfinalbuild, supercedes spec when tclgui goes (integer)
dimpk bigint

primary key for this item in Dimensions order table (bigint)
t_customer.customer_id customer_id integer

Tables referencing this one via Foreign Key Constraints:

id_tblproduction_accessory accessory id_tblproduction_confreportprin confreportprinted id_tblproduction_customerid customerid id_tblproduction_customeridorig customeridorig id_tblproduction_customerref customerref id_tblproduction_machineuse machineuse id_tblproduction_spec spec id_tblproduction_urgent urgent

Index - Schema public

Table: tblproject

Old R&D projects table

tblproject Structure
F-Key Name Type Description
projectid integer PRIMARY KEY DEFAULT nextval('seq_projectid'::text)
projectname character varying(40)
projectdesc character varying(200)
datestarted date
ce smallint
bom smallint
completed smallint
toby smallint
chris smallint
startedby character varying(10)
cenn smallint
jennyso smallint
tobyso smallint
hopso smallint
peterso smallint
notes text

Index - Schema public

Table: tblreturn

tracks returns history for a serial numbered product

tblreturn Structure
F-Key Name Type Description
returnid integer PRIMARY KEY DEFAULT nextval('seq_returnid'::text)

primary key (integer)
productionid integer

foreign key link to tblproduction (integer)
lotusdate date

date return record made (date)
lotusnumber integer

SME booking ref (was Lotus Notes) (integer)
specinas character varying(15)

spec of machine when booked in (varchar 15)
comments text

comments about return (text)
accesscustomeraccno character varying(7)

customer the machine is sent IN from (varchar 7)
creditnotenumber integer

seems to be duplicate of redespatchref: find which is referenced by intranet then obsolete the other. Legacy field (integer)
redespatchref integer

Dimensions invoice machine sent back out on (integer)
redespatcheddate date

date of redespatch (date)
accesscustomerout character varying(7)

customer the machine is sent back OUT to (varchar 7)
id_tblreturn_productionid productionid

Index - Schema public

Table: tblsoftware

Stores software info for a product

tblsoftware Structure
F-Key Name Type Description
softwareid integer PRIMARY KEY DEFAULT nextval('seq_softwareid'::text)
softwaredate date
software character varying(50)
serialnumber character varying(50)

Index - Schema public

Table: tblsubassembly

This table was used to store subs of a product before we got original access accounts working correctly, no longer required

tblsubassembly Structure
F-Key Name Type Description
stockcode character varying(15) PRIMARY KEY
description character varying(100)
stocklevel integer NOT NULL
reorderlevel integer NOT NULL

Index - Schema public

Table: tblsubsinbuild

tblsubsinbuild Structure
F-Key Name Type Description
subsinbuildid integer PRIMARY KEY DEFAULT nextval('seq_subsinbuildid'::text)
spec character varying(20)
stockcode character varying(15)
quantity smallint
id_tblsubsinbuild_spec spec id_tblsubsinbuild_stockcode stockcode

Index - Schema public

Table: tblswncomments

tracked comments on old R&D db software - obsolete

tblswncomments Structure
F-Key Name Type Description
swncommentid integer PRIMARY KEY DEFAULT nextval('seq_swncommentid'::text)
swndate date
swncomment text
swprojectid integer
id_tblswncomments_swprojectid swprojectid

Index - Schema public

Table: tblswproject

Old R&D projects software info

tblswproject Structure
F-Key Name Type Description
swprojectid integer PRIMARY KEY
swversion character varying(20)
swtestername character varying(10)
swcompleted smallint
swnickso smallint
swtobyso smallint
swpeterso smallint
swrichardso smallint
proc1 smallint
proc2 smallint
proc3 smallint
proc4 smallint
proc5 smallint
proc6 smallint
proc7 smallint
proc8 smallint
proc9 smallint
proc10 smallint
testnumber integer
datestarted date
startedby character varying(10)
swnotes text

Index - Schema public

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict