LPA v0.0.6

Table _sec9_apps

Indexes	Field Name	Data Type
*	app_name	varchar( 128 )
	app_type	varchar( 255 )
	description	varchar( 255 )
Indexes
	pk__sec9_apps	ON app_name
Foreign Keys
	fk__sec9_apps__sec9_log	( app_name ) ref _sec9_log (application)

Table _sec9_groups

Indexes	Field Name	Data Type
*	group_id	int AUTOINCREMENT
	description	varchar( 255 )
Indexes
	pk__sec9_groups	ON group_id
	description	ON description

Table _sec9_groups_apps

Indexes	Field Name	Data Type
*	group_id	int
*	app_name	varchar( 128 )
	priv_access	varchar( 1 )
	priv_insert	varchar( 1 )
	priv_delete	varchar( 1 )
	priv_update	varchar( 1 )
	priv_export	varchar( 1 )
	priv_print	varchar( 1 )
Indexes
	pk__sec9_groups_apps	ON group_id, app_name
	_sec9_groups_apps_ibfk_2	ON app_name
Foreign Keys
	fk__sec9_groups_apps__sec9_apps	( app_name ) ref _sec9_apps (app_name)
	fk__sec9_groups_apps__sec9_groups	( group_id ) ref _sec9_groups (group_id)

Table _sec9_log

Indexes	Field Name	Data Type
*	id	int AUTOINCREMENT
	inserted_date	datetime
*	username	varchar( 90 )
*	application	varchar( 200 )
*	creator	varchar( 30 )
*	ip_user	varchar( 32 )
*	action	varchar( 30 )
	description	text
Indexes
	pk__sec9_log	ON id
Foreign Keys
	fk__sec9_log__sec9_users	( username ) ref _sec9_users (login)

Table _sec9_users

Indexes	Field Name	Data Type
*	login	varchar( 255 )
*	pswd	varchar( 255 )
	name	varchar( 64 )
	email	varchar( 255 )
	active	varchar( 1 )
	activation_code	varchar( 32 )
	priv_admin	varchar( 1 )
Indexes
	pk__sec9_users	ON login

Table _sec9_users_groups

Indexes	Field Name	Data Type
*	login	varchar( 255 )
*	group_id	int
Indexes
	pk__sec9_users_groups	ON login, group_id
	_sec9_users_groups_ibfk_2	ON group_id
Foreign Keys
	fk__sec9_users_groups__sec9_groups	( group_id ) ref _sec9_groups (group_id)
	fk__sec9_users_groups__sec9_users	( login ) ref _sec9_users (login)

Table audit

Indexes	Field Name	Data Type
*	audit_id	int AUTOINCREMENT
*	audit_date	date
	shift_id	int
*	auditor_id	int
	audit_level	int
	ws_group_id	int
	ws_id	int
*	status_cd	char( 3 )
*	frequency	enum
Indexes
	pk_audit	ON audit_id
	foreign_key01	ON audit_level
Foreign Keys
	fk_audit_auditor	( auditor_id ) ref auditor (auditor_id)
	fk_audit_shift	( shift_id ) ref shift (shift_id)
	fk_audit_status	( status_cd ) ref status (status_cd)
	fk_audit_workstation	( ws_id ) ref workstation (ws_id)
	fk_audit_workstation_group	( ws_group_id ) ref workstation_group (ws_group_id)
	fk_audit_audit_level	( audit_level ) ref audit_level (audit_level_id)
	fk_audit_frequency	( frequency ) ref frequency (frequency)

Table audit_level

Indexes	Field Name	Data Type
*	audit_level_id	int
*	audit_level_desc	varchar( 255 )
Indexes
	pk_audit_level	ON audit_level_id

Table audit_workstation

Indexes	Field Name	Data Type
*	audit_ws_id	int AUTOINCREMENT
*	audit_id	int
*	ws_id	int
	status_cd	char( 3 )
Indexes
	pk_audit_workstation	ON audit_ws_id
Foreign Keys
	fk_audit_workstation_audit	( audit_id ) ref audit (audit_id)
	fk_audit_workstation_status	( status_cd ) ref status (status_cd)
	fk_audit_workstation_workstation	( ws_id ) ref workstation (ws_id)

Table auditor

Indexes	Field Name	Data Type
*	auditor_id	int AUTOINCREMENT
*	active	boolean DEFAULT 1
*	login	varchar( 255 )
*	audit_level_id	int
Indexes
	pk_auditor	ON auditor_id
Foreign Keys
	fk_auditor__sec9_users	( login ) ref _sec9_users (login)
	fk_auditor_audit_level	( audit_level_id ) ref audit_level (audit_level_id)

Table frequency

Indexes	Field Name	Data Type
*	frequency	char( 1 )
*	days	smallint
*	frequency_desc	varchar( 255 )
Indexes
	pk_frequency	ON frequency

Table issue

Indexes	Field Name	Data Type
*	issue_id	int AUTOINCREMENT
*	answer_id	int
	issue_cat_id	int
	issue_notes	varchar( 255 )
	due_date	date
*	closed	boolean DEFAULT 0
	close_comment	varchar( 255 )
	close_date	date
	close_login	varchar( 255 )
	close_audit_id	int
	assigned_login	varchar( 255 )
	opened_login	varchar( 255 )
*	create_date	datetime DEFAULT CURRENT_TIMESTAMP
Indexes
	pk_issue	ON issue_id
Foreign Keys
	fk_issue_issue_category	( issue_cat_id ) ref issue_category (issue_cat_id)
	fk_issue_question_answer	( answer_id ) ref question_answer (answer_id)
	fk_issue__sec9_users	( close_login ) ref _sec9_users (login)
	fk_issue_audit	( close_audit_id ) ref audit (audit_id)

Table issue_category

Indexes	Field Name	Data Type
*	issue_cat_id	int AUTOINCREMENT
*	issue_cat_desc	varchar( 255 )
Indexes
	pk_issue_category	ON issue_cat_id

Table process

Indexes	Field Name	Data Type
*	process_id	int AUTOINCREMENT
*	process_desc	varchar( 255 )
Indexes
	pk_process	ON process_id

Table question

Indexes	Field Name	Data Type
*	question_id	int AUTOINCREMENT
*	audit_level_id	int
*	process_id	int
*	question_desc	varchar( 255 )
*	create_date	date
*	active	boolean DEFAULT 0
	question_hint	varchar( 255 )
*	frequency	enum
Indexes
	pk_question	ON question_id
Foreign Keys
	fk_question_audit_level	( audit_level_id ) ref audit_level (audit_level_id)
	fk_question_process	( process_id ) ref process (process_id)
	fk_question_frequency	( frequency ) ref frequency (frequency)

Table question_answer

Indexes	Field Name	Data Type
*	answer_id	int UNSIGNED AUTOINCREMENT
*	question_id	int
	answer	boolean
*	audit_ws_id	int
Indexes
	pk_question_answer	ON answer_id
Foreign Keys
	fk_question_answer_question	( question_id ) ref question (question_id)
	fk_question_answer_audit_workstation	( audit_ws_id ) ref audit_workstation (audit_ws_id)

Table report

Indexes	Field Name	Data Type
*	report_id	int AUTOINCREMENT
*	report_desc	varchar( 255 )
*	metric	varchar( 25 )
*	group	varchar( 25 )
	date_scale	char( 2 )
	date_range	char( 2 )
	login	varchar( 255 )
Indexes
	pk_report	ON report_id
Foreign Keys
	fk_report__sec9_users	( login ) ref _sec9_users (login)

Table report_schedule

Indexes	Field Name	Data Type
*	schedule_id	int AUTOINCREMENT
*	report_id	int
	email_list	varchar( 50 )
	frequency	enum
	multiplier	tinyint
	offset	tinyint
Indexes
	pk_report_schedule	ON schedule_id
Foreign Keys
	fk_report_schedule_report	( report_id ) ref report (report_id)

Table shift

Indexes	Field Name	Data Type
*	shift_id	int AUTOINCREMENT
*	shift_desc	varchar( 255 )
Indexes
	pk_shift	ON shift_id

Table status

Indexes	Field Name	Data Type
*	status_cd	char( 3 )
*	status_desc	varchar( 255 )
Indexes
	pk_status	ON status_cd

Table workstation

Indexes	Field Name	Data Type
*	ws_id	int AUTOINCREMENT
	ws_desc	varchar( 255 )
Indexes
	pk_workstation	ON ws_id

Table workstation_group

Indexes	Field Name	Data Type
*	ws_group_id	int AUTOINCREMENT
	ws_group_desc	varchar( 255 )
	ws_group_class_id	int
	ws_group_class_num	int
	ws_parent_group_id	int
Indexes
	pk_workstation_group	ON ws_group_id
Foreign Keys
	fk_workstation_group_workstation_group_class	( ws_group_class_id ) ref workstation_group_class (ws_group_class_id)
	fk_workstation_group_workstation_group	( ws_parent_group_id ) ref workstation_group (ws_group_id)

Table workstation_group_class

Indexes	Field Name	Data Type
*	ws_group_class_id	int AUTOINCREMENT
*	ws_group_class_desc	varchar( 255 )
	ws_group_class_parent_id	int
*	unique	boolean DEFAULT 1
Indexes
	pk_workstation_group_class	ON ws_group_class_id
Foreign Keys
	fk_workstation_group_class_workstation_group_class	( ws_group_class_parent_id ) ref workstation_group_class (ws_group_class_id)

Table workstation_process

Indexes	Field Name	Data Type
*	ws_process_id	int AUTOINCREMENT
*	ws_id	int
*	process_id	int
Indexes
	pk_workstation_process	ON ws_process_id
Foreign Keys
	fk_workstation_process_process	( process_id ) ref process (process_id)
	fk_workstation_process_workstation	( ws_id ) ref workstation (ws_id)

Table workstation_workstation_group

Indexes	Field Name	Data Type
*	ws_ws_group_id	int AUTOINCREMENT
*	ws_id	int
*	ws_group_id	int
Indexes
	pk_workstation_workstation_group	ON ws_ws_group_id
Foreign Keys
	fk_workstation_workstation_group_workstation	( ws_id ) ref workstation (ws_id)
	fk_workstation_workstation_group_workstation_group	( ws_group_id ) ref workstation_group (ws_group_id)