Database Version 1.8.54

Table [workstation (old)]

Indexes	Field Name	Data Type
*	workstation	nvarchar( 100 )
*	database_version	nvarchar( 50 )
	last_run_date	datetime
*	visible_flag	bit DEFAULT 1
Indexes
	PK_workstation	ON workstation

Table aql_level

This table represents the actual AQL levels that are looked up based on the percentage and total number of parts. This table is the intersection between AQL Percents and AQL Lots.

Indexes	Field Name	Data Type	Description
*	aql_percent_id	int	Foreign key reference back to aql_percent table.
*	aql_lot_id	int	Foreign key reference back to aql_lot table.
*	value	int	The value which represents the number of measurements needed for the characteristic based on its assigned percentage and the lot range lookup based on the total parts in the lot.
Indexes
	aql_level_primary_key	ON aql_percent_id, aql_lot_id
	Idx_aql_level_aql_percent_id	ON aql_percent_id
	Idx_aql_level_aql_lot_id	ON aql_lot_id
Foreign Keys
	fk_aql_level_aql_percent	( aql_percent_id ) ref aql_percent (aql_percent_id)
	fk_aql_level_aql_lot	( aql_lot_id ) ref aql_lot (aql_lot_id)

Table aql_lot

This table houses the lot ranges for the AQL table. Lots are defined as a FROM and TO value (i.e. 1-5, 6-10). In order to work properly, lots should all be contiguous (no gaps between numbers such as 1-5, 7-10) and no overlapping lots (1-5, 3-7). In addition, the FROM value of the lowest lot must start with one and the last lot must end with -1. In this case, -1 signifies no upper boundary. Therefore, if the true AQL lot is 500,000+, then the FROM value is 500,000 and the TO value is -1.

Indexes	Field Name	Data Type	Description
*	aql_lot_id	int AUTOINCREMENT	The auto-counting primary key of the table.
*	aql_table_id	int	The foreign key reference back to the AQL table to which this lot belongs.
*	lot_from	int	The lower FROM value of the lot. For instance, if the lot is 5-10, the FROM value is 5.
*	lot_to	int	The upper TO value of the lot. For instance, if the lot is 5-10, the TO value would be 10. If representing the last lot where there is no upper boundary, set the TO value to -1. Therefore, if the last lot in the table is 500,000+, then set this field to -1 for that lot.
Indexes
	Pk_aql_lot_aql_lot_id	ON aql_lot_id
	Idx_aql_lot_aql_table_id	ON aql_table_id
Foreign Keys
	fk_aql_lot_aql_table	( aql_table_id ) ref aql_table (aql_table_id)

Table aql_percent

This table defines the AQL percentages (columns) of a 2D AQL matrix table.

Indexes	Field Name	Data Type	Description
*	aql_percent_id	int AUTOINCREMENT	The auto-counting primary key of the table.
*	aql_table_id	int	Foreign key reference back to the AQL table to which this percentage belongs.
*	aql_percent	float	The actual percentage of the AQL column in decimal form.
Indexes
	Pk_aql_percent_aql_percent_id	ON aql_percent_id
	Idx_aql_percent_aql_table_id	ON aql_table_id
Foreign Keys
	fk_aql_percent_aql_table	( aql_table_id ) ref aql_table (aql_table_id)

Table aql_table

Defines a table of AQL levels. Edit flag specifies whether the user can edit the values or if they are hard coded by Prolink from a standard ASQ Table.

Indexes	Field Name	Data Type	Description
*	aql_table_id	int AUTOINCREMENT	Autocounting primary key of the table.
*	aql_table_desc	nvarchar( 200 )	The name of the AQL table.
*	edit_flag	bit DEFAULT 1	Specifies whether or not users can edit the values of the table.
*	default_table	bit	determines the default table
Indexes
	Pk_aql_table_aql_table_id	ON aql_table_id

Table assignable_cause

Indexes	Field Name	Data Type	Description
*	cause_id	int AUTOINCREMENT	The primary key of the table.
	cause_ref	nvarchar( 100 )	The short description or reference of the assignable cause.
*	cause_desc	nvarchar( 500 )	The full description of the assignable cause.
Indexes
	PK_assignable_cause	ON cause_id

Table audit

Stores any audit log activity depending on the settings for auditing. Activity could include everything from EDL jobs running to changes from the ERS administrator.

Indexes	Field Name	Data Type	Description
*	audit_id	int AUTOINCREMENT	The auto counting primary key of the table.
*	effective_date	datetime	The date the event occurred.
*	audit_type_id	int	The type of audit event that occurred. Foreign key to audit_type
*	audit_desc	nvarchar( 500 )	A brief description of the event that occurred.
	user_id	int	The id of the user who caused or performed the action that caused the audit trail.
	object_type	varchar( 50 )	Represents the type of object this audit record effects. For instance, if the audit record is for the creation of a new user, this field would contain 'ers_user' and the table_pk would contain the id of the newly created user.
	table_pk	int	Represents id of the object that is affected by the audit record. For instance, if the audit record is for the creation of a new user, the object_type field would contain 'ers_user' and this field would contain the id of the newly created user.
Indexes
	PK_audit	ON audit_id
	Idx_audit_audit_type_id	ON audit_type_id
Foreign Keys
	FK_audit_ers_user	( user_id ) ref ers_user (user_id)
	fk_audit_audit_type	( audit_type_id ) ref audit_type (audit_type_id)

Table audit_history

Stores the 21 CFR Part 11 audit history for general events such as the signing of documents, password failures, etc.

Indexes	Field Name	Data Type	Description
*	audit_id	int AUTOINCREMENT	The primary key of the table.
*	effective_date	datetime	The date the event occurred.
*	audit_desc	nvarchar( 500 )	The description of what occurred.
	user_id	int	The id of the user who caused the event.
	reason_id	int	The reason id given when the event occurred.
	qcc_file_id	int	The part file id if applicable. Some events will be part file specific. Others will not in which case, this field will be null.
	entry_type	nvarchar( 15 )	The enumerated value of the type of event that occurred. DOCUMENT_SIGNED = 9, FAILED_LOGIN = 10
Indexes
	PK_audit_history	ON audit_id
Foreign Keys
	FK_audit_history_ers_user	( user_id ) ref ers_user (user_id)

Table audit_type

Indexes	Field Name	Data Type	Description
*	audit_type_id	int AUTOINCREMENT	The id of the audit type.
*	audit_type_desc	nvarchar( 100 )	The description of the audit type.
Indexes
	PK_audit_type	ON audit_type_id

Table calc_settings_group

This houses QC-CALC settings groups which replaces the previous admin groups/ADM files in QC-CALC 4.1 and previous. Settings groups differ from user groups in that settings groups are set at the individual copy of QC-CALC so that the copy shares its settings (paths, etc) with other copies of QC-CALC. User groups are mostly for user privileges. This table also links settings groups with specific sets of assignable causes, corrective actions, Part 11 users, and reason codes.

Indexes	Field Name	Data Type	Description
*	settings_group_id	int AUTOINCREMENT	The auto-numbering primary key of the table.
*	settings_group_desc	nvarchar( 100 )	The name of the settings group referred to in Admin tools of QC-CALC.
	settings_group_content	ntext	The content, settings, and locked items of the group itself.
Indexes
	Pk_calc_settings_group_settings_group_id	ON settings_group_id

Table calc_settings_group_action_map

Resolves a many-to-many relationship between settings groups and corrective actions. This table maps specific actions to a settings group. This way, a copy of QC-CALC will only see only the corrective_actions that are mapped to the settings group rather than every action in the system.

Indexes	Field Name	Data Type	Description
*	settings_group_id	int	The link to the settings group.
*	action_id	int	The link to the corrective action.
Indexes
	_4	ON settings_group_id, action_id
	Idx_calc_settings_group_action_map_settings_group_id	ON settings_group_id
	Idx_calc_settings_group_action_map_action_id	ON action_id
Foreign Keys
	fk_calc_settings_group_action_map	( settings_group_id ) ref calc_settings_group (settings_group_id)
	fk_calc_settings_group_action_map_1	( action_id ) ref corrective_action (action_id)

Table calc_settings_group_cause_map

Resolves a many-to-many relationship between settings groups and assignable causes. This table maps specific causes to a settings group. This way, a copy of QC-CALC will only see only the assignable causes that are mapped to the settings group rather than every cause in the system.

Indexes	Field Name	Data Type	Description
*	settings_group_id	int	The link to the settings group.
*	cause_id	int	The link to assignable causes.
Indexes
	_3	ON settings_group_id, cause_id
	Idx_calc_settings_group_cause_map_settings_group_id	ON settings_group_id
	Idx_calc_settings_group_cause_map_cause_id	ON cause_id
Foreign Keys
	fk_calc_settings_group_cause_map	( settings_group_id ) ref calc_settings_group (settings_group_id)
	fk_calc_settings_group_cause_map_1	( cause_id ) ref assignable_cause (cause_id)

Table calc_settings_group_reason_map

Resolves a many-to-many relationship between settings groups and reason codes for the purpose of Part 11 audit trail. This table maps specific reason to a settings group. This way, a copy of QC-CALC will only see only the reasons that are mapped to the settings group rather than every reason code in the system.

Indexes	Field Name	Data Type	Description
*	settings_group_id	int	The settings group in the relationship.
*	reason_id	int	The reason id in the relationship.
Indexes
	_2	ON settings_group_id, reason_id
	Idx_calc_settings_group_reason_map_settings_group_id	ON settings_group_id
	Idx_calc_settings_group_reason_map_reason_id	ON reason_id
Foreign Keys
	fk_calc_settings_group_reason_map	( settings_group_id ) ref calc_settings_group (settings_group_id)
	fk_calc_settings_group_reason_map_2	( reason_id ) ref reason (reason_id)

Table calc_settings_group_user_map

Resolves a many-to-many relationship between settings groups and users for the purpose of Part 11 audit trail. This table maps specific users to a settings group. This way, a copy of QC-CALC will only see the users that are mapped to the settings group rather than every user in the system.

Indexes	Field Name	Data Type	Description
*	settings_group_id	int	The settings group id that is mapping to the user.
*	user_id	int	The user_id in the relationship.
Indexes
	_1	ON settings_group_id, user_id
	Idx_calc_settings_group_user_map_settings_group_id	ON settings_group_id
	Idx_calc_settings_group_user_map_user_id	ON user_id
Foreign Keys
	fk_calc_settings_group_user_map	( settings_group_id ) ref calc_settings_group (settings_group_id)
	fk_calc_settings_group_user_map_2	( user_id ) ref ers_user (user_id)

Table category

Proposed changes v. 1.6.18 - Categories

Indexes	Field Name	Data Type	Description
*	category_id	int AUTOINCREMENT	This is the primary key for the category table.
	parent_id	int	The value of this field points to the parent category.
*	is_dynamic	int	The dynamic category is a template for an actual category name. For example, during the data collection, QC-CALC will replace the [customer] category will with the value of the text or numeric factor called "customer."
*	[desc]	nvarchar( 100 )	This filed contains the name of the category.
Indexes
	Pk_categories	ON category_id
	Pk_category	ON parent_id
Foreign Keys
	fk_category_category	( category_id ) ref category (parent_id)	The foreign key that links parent and child categories

Table constant

Indexes	Field Name	Data Type	Description
*	constant	nvarchar( 50 )	General key/value pair constants used througout the system. The constant column is not unique and can repeat for short lists.
	value	ntext	The value of the key value pair.

Table corrective_action

Indexes	Field Name	Data Type	Description
*	action_id	int AUTOINCREMENT	The primary key of the corrective action table.
	action_ref	nvarchar( 100 )	The short description or reference for the corrective action.
*	action_desc	nvarchar( 500 )	The full description of the corrective action.
Indexes
	PK_corrective_action	ON action_id

Table dim_relation

The main table that defines the type of relationship for a particular dimension (characteristic). It maps to the characteristic and the relationship type (i.e. scatter, whisker, true position).

Indexes	Field Name	Data Type	Description
*	dim_relation_id	int AUTOINCREMENT	The primary key of the table.
*	dim_id	int	The main dimension that defines the relationship. The mapped dimensions are the child dimensions in the relationship.
*	relation_type_id	int	The type of relationship that is being defined. FK to dim_relation_type.
	dim_relation_desc	nvarchar( 100 )	The name of the relationship if existing. This only applies to feature/characteristic relationships at the moment. The feature name is added here and the various dimension records that make up the feature are linked through the dim_relation_map table. For instance, "Circle 1" may be the feature and "Circle 1:X" and "Circle 1:Dia" may be two dimension records mapped to the feature as characteristics.
Indexes
	PK_dim_relation_1	ON dim_relation_id	The primary key of the table.
	FK_dim	ON dim_id	Index on foreign key to dimension table.
	FK_dim_relation_type	ON relation_type_id	Index on foreign key to dim_relation_type table.
Foreign Keys
	FK_dim_relation_dim_relation_type	( relation_type_id ) ref dim_relation_type (relation_type_id)
	FK_dim_relation_dimension	( dim_id ) ref dimension (dim_id)

Table dim_relation_map

The map table that actually defines the constituent dimensions and their roles in the relationship. For instance, if the dim_relation for a particular characteristic is True Position, then this table defines the other characteristics that make up the relationship. The dim_relation table will have the actual true position dimension and this table will contain records for the X, Y, and Diameter.

Indexes	Field Name	Data Type	Description
*	relation_map_id	int AUTOINCREMENT	The primary key of the table.
*	dim_relation_id	int	FK back to the dim_relation table. This table defines the child dimension in the overall relationship.
*	map_dim_id	int	The child dimension.
*	map_dim_desc	varchar( 50 )	The type of child that is being defined. For instance, the true position, this may be the X, Y, or Diameter.
	extra_info	varchar( 255 )	Any extra information that needs to be provided.
Indexes
	PK_dim_relation_map	ON relation_map_id	The primary key of the table.
	FK_dim_relation	ON dim_relation_id	Index on the foreign key to the dim_relation table for joining purposes.
Foreign Keys
	FK_dim_relation_map_dim_relation	( dim_relation_id ) ref dim_relation (dim_relation_id)

Table dim_relation_type

Indexes	Field Name	Data Type	Description
*	relation_type_id	int AUTOINCREMENT	The primary key.
*	relation_type_desc	varchar( 50 )	The dim relation type; scatter, whisker, true position,etc.
Indexes
	PK_dim_relation	ON relation_type_id

Table dimension

Houses all characteristics for the parts being measured.

Indexes	Field Name	Data Type	Description
*	dim_id	int AUTOINCREMENT	The primary key of the table.
*	qcc_file_model_id	int	The link back to the QCC file model or snapshot of the QCC.
*	dim_desc	nvarchar( 100 )	The feature label.
*	dim_number	int	The current ordinal position of the dimension.
*	tol_plus	float	The plus tolerance - Add with nominal to get USL.
*	ctl_upper	float	The currently saved upper control limit.
*	nominal	float	The nominal or target.
*	ctl_lower	float	The currently saved lower control limit.
*	tol_minus	float	The minus tolerance - Add with nominal to get LSL.
*	tol_type	varchar( 50 )	The tolerance type - SSL, SSU, BI or Non toleranced
	dim_source_desc	varchar( 1000 )	The characteristic/dim source description.
	extra_info	nvarchar( 1000 )	A general extra info field on the dimension that can be used for any extra info desired.
	balloon_number	int	The balloon number of the dimension. Added for future implementation.
*	dim_type	varchar( 50 )	The type of dimension (machine, calculated, or manual).
	dim_precision	int	The number of decimal places to the right of the decimal point.
*	unique_dim_number	int DEFAULT 0	The unique dim number. This is a unique id within the part file and stays with the dimension even if the dimensions are reordered.
*	dim_dead_flag	int DEFAULT 0
	transform_info	varchar( 500 )	This field holds the Johnson Transform information if the dimension is non-normal and has been transformed.
*	xbar_mean	float DEFAULT 0	This is the saved mean for the dimension.
*	range_ctl_upper	float DEFAULT 0	This is the saved upper control limit of the range chart.
*	range_mean	float DEFAULT 0	The saved mean of the range chart.
*	range_ctl_lower	float DEFAULT 0	The saved lower control limit of the range chart.
*	sigma_ctl_upper	float DEFAULT 0	The saved upper control limit of the sigma chart.
*	sigma_mean	float DEFAULT 0	The saved mean for the sigma chart.
*	sigma_ctl_lower	float DEFAULT 0	The saved lower control limit of the sigma chart.
*	moving_average_ctl_upper	float DEFAULT 0	The saved upper control limit of the MR chart.
*	moving_average_mean	float DEFAULT 0	The saved mean of the MR chart.
*	moving_average_ctl_lower	float DEFAULT 0	The saved lower control limit of the MR chart.
*	xbar_plot_limit_upper	float DEFAULT 0
*	xbar_plot_limit_lower	float DEFAULT 0
*	range_plot_limit_upper	float DEFAULT 0
*	range_plot_limit_lower	float DEFAULT 0
*	last_written_record	int DEFAULT 0
*	historical_plot_flag	bit DEFAULT 0
*	rt_plots_flag	bit DEFAULT 0
*	prevent_nominal_update_flag	bit DEFAULT 0
*	historical_y_flag	bit DEFAULT 0
*	sigma_value	float DEFAULT 0	The saved sigma value of the dimension.
	calculation_script	nvarchar( 500 ) DEFAULT ''	If the dimension type is calculated, this field holds the equation.
*	rt_plots_graph_type	varchar( 20 ) DEFAULT 'XBar'	An indicator that stores the graph type; normal, scatter, whisker.
*	check_for_trend_flag	bit DEFAULT 0	A flag that indicates whether this dimension should be checked for trends when trend detection is enabled.
*	factor_used_flag	bit DEFAULT 0
	aql_percent_id	int	This field links the characteristic to the AQL percentage of an AQL table if AQL is being used for this part file. This is nullable since not all characteristics use AQL.
Indexes
	PK_dimension	ON dim_id	Primary key of the table.
	FK_model_dim_num	ON qcc_file_model_id, dim_number	Index allowing search on model id and dimension number.
	FK_model_id_unique_dim_num	ON qcc_file_model_id, unique_dim_number	Index allowing search on model id and unique dimension number.
	Idx_dimension_aql_percent_id	ON aql_percent_id
Foreign Keys
	FK_dimension_part_type	( qcc_file_model_id ) ref qcc_file_model (qcc_file_model_id)
	fk_dimension_aql_percent	( aql_percent_id ) ref aql_percent (aql_percent_id)

Table ers_aggregated_export

Indexes	Field Name	Data Type	Description
*	aggregated_export_id	int AUTOINCREMENT	The primary key of the table.
*	effective_date	datetime	The effective or "as of" date of the calculation being saved.
	configuration_id	int	The export config id linked to this statistical value.
	group_id	int	The data group id that was used in the calculation.
	qcc_file_id	int	The qcc file that was used in the calculation.
	dim_id	int	The dimension id that was used in the calculation.
	global_filter_id	int	The global filter that was used in the calculation.
	loop_filter_id	int	The cycling filter in the data group that was used.
	calculation	nvarchar( 50 )	The name of the statistical calculation (i.e. Cpk, Sigma).
	value	float	The value of the statistical calculation.
	dim_label	nvarchar( 200 )

Table ers_alert

This represents an alert in the system. Alerts are typically displayed in QC-Alert or in QC-Mobile and can be targeted to multiple users, groups, etc via the ers_alert_target table.

Indexes	Field Name	Data Type	Description
*	alert_id	int AUTOINCREMENT	The primary key of the table.
	action_guid	varchar( 50 )	A GUID that is optionally stored if the alert was created from an Alert Action in triggers. This allows the system to find an existing alert and update it for escalation purposes.
	action_value	nvarchar( 100 )	The monitored value that is optionally stored if the alert was created from an Alert Action in triggers. This allows the system to find an existing alert and update it for escalation purposes using the GUID and monitored value. For instance, the combination of the unique action (GUID) and "machine 2" (monitored value).
*	effective_date	datetime	The date the alert was created.
*	alert_content	nvarchar( 4000 )	The content of the alert. This should be a JSON object indicating the display color, icon, text, etc.
	status_ind	int	The status of the alert such as new, canceled, and closed.
	status_date	datetime	The date the status changed last.
	author_user_id	int	The author of the alert. In cases of triggers creating alerts, this will be null. This is only used if a user creates an alert.
	priority_ind	int	The priority of the alert. Options are low, medium, and high. Priorities are set from escalating triggers allowing them to sort to the top when displayed.
Indexes
	Pk_ers_alert_alert_id	ON alert_id
	Idx_ers_alert_author_user_id	ON author_user_id
Foreign Keys
	fk_ers_alert_ers_user	( author_user_id ) ref ers_user (user_id)

Table ers_alert_target

This table houses the various groups to which an alert is targeted. Since alerts can be targeted to multiple users or groups, this table resolves the one-to-many nature of the alert to target.

Indexes	Field Name	Data Type	Description
*	target_id	int AUTOINCREMENT	The primary key of the table.
*	alert_id	int	The foreign key back to the ers_alert table.
	settings_group_id	int	Foreign key to the calc_settings_group table when targeting a settings group.
	user_group_id	int	Foreign key to the ers_user_group table when targeting a user group.
	user_id	int	Foreign key to the ers_user table when targeting a user.
	install_id	int	Foreign key to the installation table when targeting a particular app.
Indexes
	Pk_ers_alert_target_target_id	ON target_id
	Idx_ers_alert_target_settings_group_id	ON settings_group_id
	Idx_ers_alert_target_user_group_id	ON user_group_id
	Idx_ers_alert_target_user_id	ON user_id
	Idx_ers_alert_target_install_id	ON install_id
	Idx_ers_alert_target_alert_id	ON alert_id
Foreign Keys
	fk_ers_alert_target_settings_group	( settings_group_id ) ref calc_settings_group (settings_group_id)
	fk_ers_alert_target_user_group	( user_group_id ) ref ers_user_group (user_group_id)
	fk_ers_alert_target_ers_user	( user_id ) ref ers_user (user_id)
	fk_ers_alert_target_install	( install_id ) ref installation (install_id)
	fk_ers_alert_target_ers_alert	( alert_id ) ref ers_alert (alert_id)

Table ers_analysis_history

Indexes	Field Name	Data Type	Description
*	analysis_id	int AUTOINCREMENT	The primary key of the table.
*	date_created	datetime	The date the analysis was run.
*	user_id	int	The user who ran the analysis.
	desc	nvarchar( 100 )	The name of the analysis that was run.
	xml_serialized_object	text	The result of the analysis.
Indexes
	PK_ers_analysis_history	ON analysis_id

Table ers_calendar

This houses all calendars in the system. Calendars (generally for company holiday purposes) house dates by year. Customers can have more than one calendar (for different factories in different countries) and each has the current year and next year with holiday dates. Deliverables can then observe the holidays and will not run when it's a holiday.

Indexes	Field Name	Data Type	Description
*	calendar_id	int AUTOINCREMENT	The autocounting primary key of the table.
*	calendar_desc	nvarchar( 100 )	The name of the calendar.
*	calendar_json	nvarchar(max)	A JSON object that holds the actual years and dates for the calendar.
Indexes
	Pk_ers_calendar_calendar_id	ON calendar_id

Table ers_chart_theme

Indexes	Field Name	Data Type	Description
*	theme_id	int AUTOINCREMENT	The primary key of the table.
*	theme_desc	nvarchar( 50 )	The name of the theme.
*	theme_xml	ntext	The complete xml representation of the theme.
*	owner_user_id	int	The user who owns the resource.
Indexes
	PK_ers_chart_theme	ON theme_id
Foreign Keys
	FK_ers_chart_theme_ers_user	( owner_user_id ) ref ers_user (user_id)

Table ers_configuration

This table represents all scheduled reports and exports in the system.

Indexes	Field Name	Data Type	Description
*	config_id	int AUTOINCREMENT	The primary key of the table.
	config_desc	nvarchar( 50 )	The name of the report or export.
	config_type	int	The type of config (report or export).
	owner_user_id	int	The user who owns the resource.
	filter_id	int	The global filter associated with the config. Can be NULL if none set.
	recur_id	int	The FK to the recurrence table. This no longer used.
	enabled_flag	bit DEFAULT 0	A boolean flag as to whether or not this config is enabled on the scheduler.
	next_rundate	datetime	The next date time this report/export will run assuming it is enabled.
	last_rundate	datetime	The last date/time this config was run.
	report_type	int	The type of report if this config is a report (histogram, xbar, etc).
	report_template_name	nvarchar( 500 )	The template path if this config is a report.
	report_print_type	int	The type of output (print preview, printer, or file).
	report_export_type	int	The type of export if the report is saved as a file (PDF, JPG).
	report_send_email	bit DEFAULT 0	A flag indicating whether the report is emailed upon completion.
	report_open_in_default_app	bit DEFAULT 0	A flag indicating whether or not to open the report.
	report_export_file_name	nvarchar( 500 )	The path to the exported report file.
	report_email_to	nvarchar( 4000 )	The TO field of the email.
	report_email_body	nvarchar( 500 )	The BODY field of the email.
	calc_used	bit	DEPRECATED
	compare_num_lower	float	DEPRECATED
	report_email_cc	nvarchar( 4000 )	The CC field of the email.
	compare_num_upper	float	DEPRECATED
	report_email_subject	nvarchar( 500 )	The SUBJECT of the email.
*	single_part_report_sort	bit DEFAULT 0	DEPRECATED
*	single_part_report_sort_type	bit DEFAULT 0	DEPRECATED
	single_part_report_sort_filter_index	int DEFAULT 0	DEPRECATED
	single_part_report_sort_calculation_type	int DEFAULT 0	DEPRECATED
	printer_name	nvarchar( 100 )	The name of the printer if the report is to be printed.
*	use_default_printer	bit DEFAULT 1	A flag indicating whether or not to use the default printer in Windows.
*	subgroup_size	int DEFAULT 5	The subgroup size to use with the data in the report or export.
	next_run	datetime	The next scheduled run of the report/export.
	recur_date_to_run	datetime	The date to run (for yearly scheduling).
	recur_days_of_week	nvarchar( 100 )	The days of the week to run (for weekly scheduling).
	recur_time_to_run	datetime	The time of the day to run (for daily and weekly scheduling).
	recur_type	int	The type of recurrence (every minute, daily, weekly, etc.)
	recur_interval	int	The interval to run (for minutely or hourly). For example, if you want the report to run every 5 hours, then recur_type would be set to hourly and this value would be 5.
*	owner_locked_flag	bit DEFAULT 1	A flag indicating whether or not other users are allowed to run this config. If locked, users cannot run the report or export.
*	remove_outliers	bit DEFAULT 1	A flag indicating whether or not to remove outliers from statistics.
	report_note	nvarchar( 500 )	A general note to add to the report.
*	debug_flag	bit DEFAULT 0	A flag indicating if we are in debug mode.
	record_governor	int	A record governor for the report. Set this to limit the number of records returned (ex. last 100) within the filter. For instance, if the filter is January of this year, this will govern the records to the last 100 in January of this year.
	install_id	int	This id determines which installation will perform the processing of the report/export.
*	report_email_ind	int DEFAULT 0	This indicates how the attachment to an emailed report will be handled. The default is zero which means the report will be attached. A setting of 1 means the report will be sent as a link to QC-Mobile. The link will be available to anyone regardless as to whether or not s/he is a QC-Mobile user.
	report_email_qcm_const	varchar( 255 )	This nullable field holds the name of the constant of the URL for QC-Mobile should one of the link options be chosen in report_email_ind. NOTE: It only holds the name of the constant and not the URL itself. This allows users to update the URL without having to change all of their reports.
	ers_service_id	int
	ers_service_group_id	int
	run_status	nvarchar( 15 )
	next_run_check_date_utc	datetime
	next_run_utc	datetime
	disable_after	varchar( 50 )	Will eventually house a pipe string that will have either "x\|10" or "dt\|yyyy-mm-dd hh:mm:ss". This will allow you to enable a report/export and then have it automatically disable itself after either x runs or a particular date.
	calendar_id	int	A foreign key reference to the holiday calendar to observe for the report or export.
	period_id	int	A foreign key reference to the production period the report or export will follow. It will not run outside the hours of the production period.
Indexes
	PK_ers_configuration	ON config_id
	idx_ers_configuration	ON install_id
	Idx_ers_configuration_ers_service_id	ON ers_service_id
	Idx_ers_configuration_ers_service_group_id	ON ers_service_group_id
	Idx_ers_configuration_calendar_id	ON calendar_id
	Idx_ers_configuration_period_id	ON period_id
Foreign Keys
	fk_ers_configuration	( install_id ) ref installation (install_id)
	FK_ers_configuration_ers_user	( owner_user_id ) ref ers_user (user_id)
	fk_ers_configuration_ers_service	( ers_service_id ) ref ers_service (ers_service_id)
	fk_ers_configuration_ers_service_group	( ers_service_group_id ) ref ers_service_group (ers_service_group_id)
	FK_ers_configuration_ers_calendar	( calendar_id ) ref ers_calendar (calendar_id)
	fk_ers_configuration_production_period	( period_id ) ref ers_production_period (period_id)

Table ers_configuration_fields

A generic data defined table of fields that are specific to each report.

Indexes	Field Name	Data Type	Description
*	field_id	int AUTOINCREMENT	The primary key of the table.
*	config_id	int	The report/export to which this setting is linked.
*	field_name	nvarchar( 100 )	The name of the field.
	field_value	nvarchar(max)	The value of the field.
	field_blob	image	The binary value of the field if the field stores binary data.
Indexes
	PK_ers_configuration_fields	ON field_id
Foreign Keys
	FK_ers_configuration_fields_ers_configuration	( config_id ) ref ers_configuration (config_id)

Table ers_custom_factor_list

This table stores custom factor lists. A custom factor list allows spelling variations in the factor names (ex. Lot Num vs. Lot No vs. Lot #). These lists are then used in record filters.

Indexes	Field Name	Data Type	Description
*	list_id	int AUTOINCREMENT	The primary key of the table.
*	list_type	nvarchar( 50 )	The type of list (text or numeric).
*	list_desc	nvarchar( 50 )	The name of the list.
*	list_variation	nvarchar( 4000 )	The variation of the names. A comma separated list of values.
Indexes
	PK_ers_custom_factor_list	ON list_id

Table ers_custom_setting

Indexes	Field Name	Data Type
*	field_id	int AUTOINCREMENT
*	field_type	nvarchar( 100 )
*	field_name	nvarchar( 100 )
	field_value	nvarchar(max)
*	owner_user_id	int
Indexes
	PK_ers_configuration_setting	ON field_id
Foreign Keys
	FK_ers_custom_setting_ers_user	( owner_user_id ) ref ers_user (user_id)

Table ers_dashboard

This table represents the highest level dashboard that contains widgets.

Indexes	Field Name	Data Type	Description
*	dashboard_id	int AUTOINCREMENT	The primary key of the table.
*	dashboard_desc	nvarchar( 100 )	The name of the dashboard.
*	row_count	int	The number of rows in the layout.
*	col_count	int	The number of columns in the layout.
*	refresh_rate	int	The number of seconds between refreshes of the dashboard.
*	owner_user_id	int	The owner of the resource.
*	export_image_flag	bit DEFAULT 0	A flag indicating whether or not the dashboard should be exported to an image file.
	export_image_path	nvarchar( 8000 )	The path of the export image.
	export_image_setting	nvarchar( 1000 )	The resolution of the image and FTP settings.
	dashboard_title	nvarchar( 200 )	The title of the dashboard. Can be HTML and dynamic labeling.
	title_group_id	int	The data group associated with any dynamic fields in the title.
	title_global_filter_id	int	The global filter associated with any dynamic fields in the title.
	title_fields	nvarchar( 500 )	The fields within the title.
*	debug_flag	bit DEFAULT 0	A flag indicating that debug mode is enabled/disabled. When enabled, it is automatically disabled after one refresh of the dashboard.
	install_id	int	This id determines which installation will perform the processing of the dashboard.
	ers_service_id	int
	ers_service_group_id	int
	run_status	nvarchar( 15 )
	next_run_check_date_utc	datetime
	next_run_utc	datetime
	enabled_flag	bit
	calendar_id	int	The foreign key reference to the holiday calendar this dashboard should observe. Dashboards will not process on holidays.
	period_id	int	The foreign key reference to the production period to which the dashboard will adhere. If specified, dashboards will not run outside the production period.
Indexes
	PK_dashboard	ON dashboard_id
	idx_ers_dashboard	ON install_id
	Idx_ers_dashboard_ers_service_id	ON ers_service_id
	Idx_ers_dashboard_ers_service_group_id	ON ers_service_group_id
	Idx_ers_dashboard_calendar_id	ON calendar_id
	Idx_ers_dashboard_period_id	ON period_id
Foreign Keys
	FK_ers_dashboard_ers_filter	( title_global_filter_id ) ref ers_filter (filter_id)
	FK_ers_dashboard_ers_group	( title_group_id ) ref ers_group (group_id)
	FK_ers_dashboard_ers_user	( owner_user_id ) ref ers_user (user_id)
	fk_ers_dashboard	( install_id ) ref installation (install_id)
	fk_ers_dashboard_ers_service	( ers_service_id ) ref ers_service (ers_service_id)
	fk_ers_dashboard_ers_service_group	( ers_service_group_id ) ref ers_service_group (ers_service_group_id)
	fk_ers_dashboard_ers_calendar	( calendar_id ) ref ers_calendar (calendar_id)
	fk_ers_dashboard_production_period	( period_id ) ref ers_production_period (period_id)

Table ers_dashboard_widget

This table represents a specific widget on the dashboard.

Indexes	Field Name	Data Type	Description
*	widget_id	int AUTOINCREMENT	The primary key of the table
*	dashboard_id	int	The dashboard to which this widget is linked.
*	widget_type	nvarchar( 50 )	The type of widget (text, radial gauge, andon light).
*	position_row	int	The one-based row position of the widget.
*	position_col	int	The one-based column position of the widget.
	group_id	int	The data group to use to calculate the value of the widget.
	global_filter_id	int	The global filter to use to calculate the value of the widget.
	label_override	nvarchar( 100 )	The dynamic label just above the widget.
*	no_data_show_flag	bit DEFAULT 1	A flag indicating whether you should show the widget if there is no data.
	record_governor	int	A record governor allowing you limit the number of records returned to the latest x from any query.
Indexes
	PK_dashboard_widget	ON widget_id
Foreign Keys
	FK_ers_dashboard_widget_ers_dashboard	( dashboard_id ) ref ers_dashboard (dashboard_id)
	FK_ers_dashboard_widget_ers_filter	( global_filter_id ) ref ers_filter (filter_id)
	FK_ers_dashboard_widget_ers_group	( group_id ) ref ers_group (group_id)

Table ers_dashboard_widget_fields

This table is a generic data defined table of field values for dashboard widgets.

Indexes	Field Name	Data Type	Description
*	field_id	int AUTOINCREMENT	The primary key of the table.
*	widget_id	int	The widget to which this field is linked.
*	field_name	nvarchar( 100 )	The name of the field.
*	field_value	nvarchar( 1000 )	The value of the field.
	field_blob	image	The binary value of the field such as an image (if the field is a binary type).
Indexes
	PK_ers_dashboard_widget_fields	ON field_id
Foreign Keys
	FK_ers_dashboard_widget_fields_ers_dashboard_widget	( widget_id ) ref ers_dashboard_widget (widget_id)

Table ers_displayed_filters

Represents the record filters present in a data group.

Indexes	Field Name	Data Type	Description
*	group_id	int	The data group to which this filter is linked.
*	filter_id	int	The filter that is linked.
*	filter_position	int	The position (order) of the filter in the group.
Indexes
	PK_ers_displayed_filters	ON group_id, filter_id
Foreign Keys
	FK_ers_displayed_filters_ers_filter	( filter_id ) ref ers_filter (filter_id)
	FK_ers_displayed_filters_ers_group	( group_id ) ref ers_group (group_id)

Table ers_external_report

This table houses any external reports that are not a part of ERS as regular resources. This allows reports created in other products to have a database entry and then be published through QC-Mobile. As of creation, this table houses both reports from QC-CALC Real-Time (auto reporting and trend detection) and SPC Office Buddy (Excel Job output).

Indexes	Field Name	Data Type	Description
*	report_id	int AUTOINCREMENT	The autonumbering primary key of the table.
*	resource_type	int	The type of resource as defined by the viewable resources constant. This defines the type of external report.
*	owner_user_id	int	The owner of the report linking to the ers_user table.
*	report_desc	nvarchar( 100 )	The name of the report as its seen in QC-Mobile and ERS. In the case of QC-CALC Real-Time, the name is declared by the user when setting up auto-reporting/trend detection to go to QC-Mobile. In the case of Office Buddy, it is the name of the Excel Job.
*	report_file_name	varchar( 255 )	The name of the file (no path) that was produced. Used to recreate hyperlinks in the original file name and also for the file extension to determine how to view the file.
Indexes
	Pk_ers_external_report_report_id	ON report_id
Foreign Keys
	fk_ers_external_report	( owner_user_id ) ref ers_user (user_id)

Table ers_filter

This table represents both dimension and record filters.

Indexes	Field Name	Data Type	Description
*	filter_id	int AUTOINCREMENT	The primary key of the table.
	filter_type_id	int	DEPRECATED
*	filter_name	nchar( 100 )	The name of the filter.
*	filter_sql	nchar( 2000 )	The SQL generated by the filter.
*	edl_flag	bit	A flag indicating whether or not an edl description should be used.
*	model_option	int	An indicator indicating the model option (latest model or query across models).
	filter_format	nvarchar( 100 )	A custom format field allowing an override to the filter name in reports, etc.
	qcc_file_id	int	The qcc file linked (if one is linked). Used for querying specific features and factors.
*	dim_flag	bit DEFAULT 0
*	owner_user_id	int	The owner of the filter.
*	dim_filter_flag	bit DEFAULT 0	Flag indicating whether this is a dimension filter or record filter. True for dim filter.
*	model_combine_flag	bit DEFAULT 1	Flag indicating whether or not we should combine like dimensions across models.
*	archive_option	int DEFAULT 1	Indicator that determines if filter should query part files that are archived, unarchived or both.
	part_files_dim_filter_xml	text
Indexes
	PK_ers_filter	ON filter_id
Foreign Keys
	FK_ers_filter_ers_filter_type	( filter_type_id ) ref ers_filter_type (filter_type_id)
	FK_ers_filter_ers_filter	( qcc_file_id ) ref qcc_file (qcc_file_id)
	Fk_ers_filter_ers_user	( owner_user_id ) ref ers_user (user_id)

Table ers_filter_condition

Represents the WHERE clause of filters in ERS.

Indexes	Field Name	Data Type	Description
*	filter_condition_id	int AUTOINCREMENT	The primary key of the table.
*	filter_id	int	The filter to which this condition is linked.
*	logical_operator	varchar( 10 )	The logical operator (AND, OR).
*	filter_field	nvarchar( 4000 )	The field on which to filter.
	left_paren	varchar( 10 )	Text field representing a set of left parentheses.
*	compare_operator	varchar( 100 )	The operator (greater than, equal to, etc).
*	criteria	nvarchar( 4000 )	The criteria value being compared.
	right_paren	varchar( 10 )	Text field representing a set of right parentheses.
	extra_info	varchar( 100 )	This extra info field allows us to add specific information about the type of filter or join condition. It is currently used for characteristic filters that use the "Where Found In (Latest Part File)" operator.
Indexes
	PK_ers_filter_condition	ON filter_condition_id
Foreign Keys
	FK_ers_filter_condition_ers_filter	( filter_id ) ref ers_filter (filter_id)

Table ers_filter_file

Maps dimension filters to QCQ files to prevent dimension filters from querying every file in a particular directory.

Indexes	Field Name	Data Type	Description
*	filter_file_id	int	The primary key of the table.
*	filter_id	int
*	file_path	nvarchar( 500 )
Indexes
	PK_ers_filter_file	ON filter_file_id

Table ers_filter_join

This table represents the table joins in a filter.

Indexes	Field Name	Data Type	Description
*	join_id	int AUTOINCREMENT	The primary key of the table.
*	filter_id	int	The filter id to which this join is linked.
*	join_desc	varchar( 100 )	The name of the join.
*	condition_related_flag	bit DEFAULT 0
*	table_left	varchar( 100 )	The table on the left side of the join.
	table_left_alias	varchar( 100 )	The generated table alias for the left table.
*	join_style	varchar( 50 )	The join style (inner, left outer).
*	table_right	varchar( 100 )	The table on the right side of the join.
	table_right_alias	varchar( 100 )	The generated table alias for the right table.
*	table_left_keys	varchar( 200 )	The list of keys for the left table.
*	table_right_keys	varchar( 200 )	The list of keys for the right table.
Indexes
	PK_ers_filter_joins	ON join_id
Foreign Keys
	FK_ers_filter_join_ers_filter	( filter_id ) ref ers_filter (filter_id)

Table ers_filter_type

Indexes	Field Name	Data Type
*	filter_type_id	int AUTOINCREMENT
	filter_type_name	nchar( 20 )
Indexes
	PK_ers_filter_type	ON filter_type_id

Table ers_full_view

This table represents full views in ERS.

Indexes	Field Name	Data Type	Description
*	view_id	int AUTOINCREMENT	The primary key of the table.
*	view_desc	nvarchar( 100 )	The name of the full view.
	image_desc	nvarchar( 50 )	The name of the image that was uploaded as the background image.
	bg_image	image	The image itself in binary form.
	size_mode	int	The size mode of the view (stretch, tile, etc). Stretch is the most common.
*	refresh_rate	int	The refresh rate in seconds.
*	owner_user_id	int	The owner of the full view.
*	sticky_flag	bit DEFAULT 1	Flag indicating whether the hotspot should continue blinking until acknowledgement.
*	debug_flag	bit DEFAULT 0	Flag indicating whether or not this view is in debug/troubleshooting mode. If so, it will automatically be set to false after the next refresh of the view.
	install_id	int	This id determines which installation will actually perform the processing of the full view.
	ers_service_id	int
	ers_service_group_id	int
	run_status	nvarchar( 15 )
	next_run_check_date_utc	datetime
	next_run_utc	datetime
	enabled_flag	bit
	calendar_id	int	The foreign key reference to the calendar the full view should observe. Full views do not process on known holidays if chosen.
	period_id	int	A foreign key reference to the production period table that the full view will observe. Full views will not run outside of production period hours if selected.
Indexes
	PK_ers_full_view	ON view_id
	Idx_ers_full_view_ers_service_id	ON ers_service_id
	Idx_ers_full_view_ers_service_group_id	ON ers_service_group_id
	Idx_ers_full_view_calendar_id	ON calendar_id
	Idx_ers_full_view_period_id	ON period_id
Foreign Keys
	fk_ers_full_view	( install_id ) ref installation (install_id)
	FK_ers_full_view_ers_user	( owner_user_id ) ref ers_user (user_id)
	fk_ers_full_view_ers_service	( ers_service_id ) ref ers_service (ers_service_id)
	fk_ers_full_view_ers_service_group	( ers_service_group_id ) ref ers_service_group (ers_service_group_id)
	fk_ers_full_view_ers_calendar	( calendar_id ) ref ers_calendar (calendar_id)
	fk_ers_full_view_production_period	( period_id ) ref ers_production_period (period_id)

Table ers_full_view_hotspot

This table represents hotspots in a full view.

Indexes	Field Name	Data Type	Description
*	hotspot_id	int AUTOINCREMENT	The primary key of the table.
*	view_id	int	The full view to which the hotspot is linked.
	hotspot_desc	nvarchar( 100 )	The name of the hotspot. Usually auto-generated.
	group_id	int	The data group that is used to calculate values.
	global_filter_id	int	The global filter (optional) that is used to filter data.
*	pos_x	float	The X position of the hotspot on the full view. This is a percentage.
*	pos_y	float	The Y position of the hotspot on the full view. This is a percentage.
	link_view_id	int	If the hotspot is a link to another full view, the id of the other view.
*	sticky_flag	bit DEFAULT 1	DEPRECATED
	last_run_date	datetime	The date time the hotspot last refreshed.
	last_run_data_flag	bit	A flag indicating whether or not there was data the last time it was refreshed.
	record_governor	int	A record govenor allowing you to pull only the latest x records when calculating the hotspot value.
Indexes
	PK_ers_full_view_hotspot	ON hotspot_id
Foreign Keys
	FK_ers_full_view_hotspot_ers_full_view	( view_id ) ref ers_full_view (view_id)

Table ers_full_view_hotspot_fields

This table is a generic data defined table of fields for hotspots.

Indexes	Field Name	Data Type	Description
*	field_id	int AUTOINCREMENT	The primary key of the table.
*	hotspot_id	int	The hotspot to which this field is linked.
*	field_name	nvarchar( 100 )	The name of the field.
*	field_value	nvarchar( 1000 )	The value of the field.
	field_blob	image	The binary value of the field such as an image (if the field stores binary data).
Indexes
	PK_ers_full_view_hotspot_fields	ON field_id
Foreign Keys
	FK_ers_full_view_hotspot_fields_ers_full_view_hotspot	( hotspot_id ) ref ers_full_view_hotspot (hotspot_id)

Table ers_full_view_hotspot_trend

Represents a trend that has occurred on a hotspot. All trends/rule breaks are logged so we do not flash the lights for the same issue more than once.

Indexes	Field Name	Data Type	Description
*	trend_id	int AUTOINCREMENT	The primary key of the table.
*	hotspot_id	int	The hotspot to which the trend is linked.
*	effective_date	datetime	The date of the trend/violation.
*	rule_type	int	The type of rule that was broken.
*	trend_state	int	The current state of the trend (warning or exception).
*	trend_desc	nvarchar( 500 )	The description of the trend.
*	trend_info	nvarchar( 4000 )	Extra info about the trend used to render the full view.
*	ack_flag	bit DEFAULT 0	Flag indicating whether or not the trend has been acknowledged.
	unique_identifier	nvarchar( 200 )	Special string uniquely identifying the trend.
	ack_date	datetime	Datetime the trend was acknowledged.
	ack_user_id	int	The user who acknowledged the trend.
	end_date	datetime	The date time the trend actually stopped occurring.
Indexes
	PK_ers_full_view_hotspot_trend	ON trend_id
Foreign Keys
	FK_ers_full_view_hotspot_trend_ers_full_view_hotspot	( hotspot_id ) ref ers_full_view_hotspot (hotspot_id)

Table ers_full_view_rule

This table stores the rules that are checked in a hotspot.

Indexes	Field Name	Data Type	Description
*	rule_id	int AUTOINCREMENT	The primary key of the table.
*	hotspot_id	int	The hotspot to which the rule is linked.
*	rule_type	int	The type of rule (Low part count, low Cpk, etc.)
	enabled_flag	bit DEFAULT 0	A flag indicating whether or not the rule is enabled.
Indexes
	PK_ers_full_view_rule	ON rule_id
Foreign Keys
	FK_ers_full_view_rule_ers_full_view_hotspot	( hotspot_id ) ref ers_full_view_hotspot (hotspot_id)

Table ers_full_view_rule_fields

A generic data defined table of fields for rules. For instance, if the rule is low Cpk, there will be two fields for the rule; a warning value and an exception value.

Indexes	Field Name	Data Type	Description
*	field_id	int AUTOINCREMENT	The primary key of the table.
*	rule_id	int	The rule to which the field is linked.
*	field_name	nvarchar( 100 )	The name of the field.
	field_value	nvarchar( 1000 )	The value of the field.
	field_blob	image	The binary value of the field such as an image (if field is binary).
Indexes
	PK_ers_full_view_rule_fields	ON field_id
Foreign Keys
	FK_ers_full_view_rule_fields_ers_full_view_rule	( rule_id ) ref ers_full_view_rule (rule_id)

Table ers_global_variable

This table contains global user variables used in ERS application. The variables could be paths or other values. The variables' values are set on the admin level and can be used throughout the application allowing changing values in a single place.

Indexes	Field Name	Data Type
*	variable_id	int AUTOINCREMENT
	desc	nvarchar( 255 )
*	name	nvarchar( 100 )
*	type	nvarchar( 20 )
	value	ntext
Indexes
	Pk_ers_global_variable_variable_id	ON variable_id

Table ers_group

Indexes	Field Name	Data Type	Description
*	group_id	int AUTOINCREMENT	The primary key of the table.
	group_name	nvarchar( 50 )	The name of the group.
	created_by	int	The owner of the data group.
	creation_date	datetime	The creation date of the group.
	last_modified_by	int	DEPRECATED
	last_modification_date	datetime	Date/time of the last modification.
	dim_filter_id	int	The dim filter id if the dimensions are driven by dim filter.
*	combine_dims_flag	bit DEFAULT 0	Flag indicating whether to combine like dimensions across models (when files and dimensions are used in group).
*	use_latest_qcc_files	bit DEFAULT 0	DEPRECATED
*	dim_option	int DEFAULT 0	The dimension handling option (stack, subgroup across dimensions) for groups that use files and dimensions.
	model_option	int	The model option (use latest, query across models, etc) for groups that use files and dimensions.
	part_files_dim_filter_xml	text
	override_part_files	bit
	category_id	int
	named_list_id	int	A foreign key to the ers_part_file_named_list.
Indexes
	PK_ers_group	ON group_id
	Idx_ers_group_category_id	ON category_id
	Idx_ers_group_named_list_id	ON named_list_id
Foreign Keys
	FK_ers_group_ers_user1	( created_by ) ref ers_user (user_id)
	FK_ers_group_ers_user2	( last_modified_by ) ref ers_user (user_id)
	fk_ers_group_category	( category_id ) ref category (category_id)
	fk_ers_group	( named_list_id ) ref ers_part_file_named_list (list_id)

Table ers_mapped_group

A many to many resolution table between report/export jobs and data groups. Each report, can have multiple groups and each group can belong to more than one report job.

Indexes	Field Name	Data Type	Description
*	config_id	int	The id of the report/export job.
*	group_id	int	The id of the datagroup.
*	group_position	int	The ordinal position of the group in the report/export.
Indexes
	PK_ers_mapped_group	ON config_id, group_id
Foreign Keys
	FK_ers_mapped_group_ers_configuration	( config_id ) ref ers_configuration (config_id)
	FK_ers_mapped_group_ers_group	( group_id ) ref ers_group (group_id)

Table ers_part_file_named_list

This is a list of part files that can be used in dimension filter, data view, or data analysis screen

Indexes	Field Name	Data Type
*	list_id	int AUTOINCREMENT
	owner_user_id	int
	settings_xml	nvarchar(max)
Indexes
	Pk_ers_part_file_named_list_list_id	ON list_id
	Idx_ers_part_file_named_list_owner_user_id	ON owner_user_id
Foreign Keys
	fk_ers_part_file_named_list_ers_user	( owner_user_id ) ref ers_user (user_id)

Table ers_production_period

This table represents production periods that can be defined. The database can house multiple production periods (for different factories) and deliverables will adhere to the production periods and only operate within them. For instance, a user can create a production period that spans Monday - Friday (9am-5pm). For any deliverables that observe this production period, they will not run outside these hours.

Indexes	Field Name	Data Type	Description
*	period_id	int AUTOINCREMENT	The autocounting primary key of the table.
*	period_desc	nvarchar( 100 )	The name of the production period.
*	period_json	nvarchar(max)	A JSON object that contains the days and hours that define the production period.
Indexes
	Pk_ers_production_period_period_id	ON period_id

Table ers_report_fields

This table houses the current report settings for manual reports.

Indexes	Field Name	Data Type	Description
*	field_id	int AUTOINCREMENT	The primary key of the table.
*	report_type	nvarchar( 50 )	The type of report (histogram, etc).
*	field_name	nvarchar( 100 )	The name of the field.
*	field_value	nvarchar( 500 )	The setting's value.
Indexes
	PK_ers_report_fields	ON field_id

Table ers_run_history

Maintains a history of all scheduled runs. This includes runs of reports, exports, full views and dashboards. This will also be expanded to include outside reporting such as QC-CALC and Office Buddy reports.

Indexes	Field Name	Data Type	Description
*	history_id	int AUTOINCREMENT	The primary key of the table.
	config_id	int	The id of the resource (report, export, dashboard or full view).
*	config_desc	nvarchar( 50 )	The name of the resource.
*	config_type	int	The config type (export or report). Only used in ERS.
*	run_date	datetime	The date/time of the run.
	automatic	bit	Always true. No longer used.
*	owner_user_id	int DEFAULT 0	The owner of the resource.
	resource_type	int	The type of resource (dashboard, export, etc).
	resource_blob	image	A binary representation of the resource (PDF for report, PNG for dashboard).
	start_date	date	Reports and Exports use run date as the time they ran. For Dashboards and Full Views, they refresh on a regular basis. To prevent a new record in this table for each refresh of a Dashboard or Full View, we simply set the start_date each time a Dashboard or Full View starts and update the run_date each time it refreshes. This makes it possible to see how long either has been running.
	resource_note	varchar( 100 )	Allows us to add information about the run. This is not yet used but is reserved for future expansion.
	install_id	int
	ers_service_id	int	This is the ID of the service that processed the deliverable. Since there can be multiple services mapped to the same installation, use this id if present (not null). If null, then the install_id specifies the ERS app than than the deliverable.
Indexes
	PK_ers_run_history	ON history_id
	Idx_ers_run_history_install_id	ON install_id
	Idx_ers_run_history_ers_service_id	ON ers_service_id
Foreign Keys
	fk_ers_run_history	( install_id ) ref installation (install_id)
	fk_ers_run_history_ers_service	( ers_service_id ) ref ers_service (ers_service_id)

Table ers_schedule

Not used.

Indexes	Field Name	Data Type
*	recur_id	int AUTOINCREMENT
	config_desc	nvarchar( 100 )
	enabled_flag	bit
	next_rundate	datetime
	owner_user_id	int
	recur_date_to_run	datetime
	recur_days_of_week	int
	recur_interval	int
	recur_time_to_run	datetime
	recur_type	int
Foreign Keys
	FK_ers_schedule_ers_user	( owner_user_id ) ref ers_user (user_id)

Table ers_selected_dimension

Holds selected dimensions in a data group (if group is using files and dimensions).

Indexes	Field Name	Data Type	Description
*	group_id	int	The data group id.
*	qcc_file_id	int	The qcc file id of the linked file.
*	dim_position	int	The ordinal (nth) dimension position (not id). This allows us to look across models.
*	dim_order	int	DEPRECATED.
*	used	smallint	Flag indicating whether or not to include this dimension in the data group. When a file is added, all dimensions are loaded to this table, but only "used" dimensions are included in reports.
*	special	smallint	DEPRECATED.
Indexes
	PK_ers_selected_dimension	ON group_id, qcc_file_id, dim_position
Foreign Keys
	FK_ers_selected_dimension_ers_selected_dimension	( group_id, qcc_file_id ) ref ers_selected_qcc_file (group_id, qcc_file_id)

Table ers_selected_qcc_file

Contains qcc files for the data group (if group is using files and dimensions).

Indexes	Field Name	Data Type	Description
*	group_id	int	The data group id.
*	qcc_file_id	int	The qcc file id of the linked file.
*	qcc_file_order	int	The ordinal position of the file in the list.
Indexes
	PK_ers_selected_qcc_file	ON group_id, qcc_file_id
Foreign Keys
	FK_ers_selected_qcc_file_ers_group	( group_id ) ref ers_group (group_id)
	FK_ers_selected_qcc_file_qcc_file	( qcc_file_id ) ref qcc_file (qcc_file_id)

Table ers_service

This table was added because we can't change the Installation table. It will break the older copies because of the way how the code was written.

Indexes	Field Name	Data Type	Description
*	ers_service_id	int AUTOINCREMENT
*	install_id	int
	friendly_name	nvarchar( 100 )
*	is_shared	bit
	alive_freq_in_sec	int DEFAULT 60
	alive_check_date_utc	date
	ers_service_group_id	int	A foreign key to the ers_service_group table. One ERS Service can belong only to a single ERS Service group.
	process_rate_in_sec	int	Indicates how often the service shoudl check if it has anything to process.
	service_name	nvarchar( 100 )	The name of the service in Windows as created from within ERS. This is the unique service name.
	lic_info	nvarchar( 4000 )	Internal usage of license information for the particular service.
Indexes
	Pk_ers_service_ers_service_id	ON ers_service_id
	Idx_ers_service_install_id	ON install_id
	Idx_ers_service_ers_service_group_id	ON ers_service_group_id
Foreign Keys
	fk_ers_service_installation	( install_id ) ref installation (install_id)
	fk_ers_service	( ers_service_group_id ) ref ers_service_group (ers_service_group_id)

Table ers_service_group

This table holds the ers service groups. This is done for the data processing purposes: dashboards, reports, exports, or fullviews can be run by one or multiple ers services. An ERS Service can belong to one service group.

Indexes	Field Name	Data Type	Description
*	ers_service_group_id	int AUTOINCREMENT	The primary key for this table.
	name	varchar( 100 )	The group name
Indexes
	Pk_ers_service_group_ers_service_group_id	ON ers_service_group_id

Table ers_snapshot

This holds snapshots of 2D/3D drawings taken in ERS.

Indexes	Field Name	Data Type	Description
*	snapshot_id	int	The auto-incrementing primary key of the table.
	snapshot_desc	varchar( 100 )	The friendly description given to the snapshot.
	owner_user_id	int	Since snapshots are resources in the system just like reports, exports, full views, and dashboards, they are owned by the user who created them. This maps to the ers_user table.
	drawing_resource_id	int	This foreign key maps back to the original drawing from which the snapshot was taken.
Indexes
	Pk_ers_snapshot_snapshot_id	ON snapshot_id
	Idx_ers_snapshot_owner_user_id	ON owner_user_id
	Idx_ers_snapshot_drawing_resource_id	ON drawing_resource_id
Foreign Keys
	fk_ers_snapshot_ers_user	( owner_user_id ) ref ers_user (user_id)
	fk_ers_snapshot_resource	( drawing_resource_id ) ref resource (resource_id)

Table ers_snapshot_dim_map

This maps particular dimensions from part files into the snapshot with coordinates.

Indexes	Field Name	Data Type	Description
*	qcc_file_id	int	A foreign key to the part file associated with the characteristic.
*	unique_dim_number	int	The unique_dim_number of the dimension within the part file. This refers to the unique dim number rather than the dim_id so we do not have to update it when models change since unique dim numbers survive across models.
*	snapshot_id	int	A foreign key reference to the ers_snapshot table declaring onto which snapshot the characteristic should be placed.
	coordinates	varchar( 50 )	The X and Y coordinates (comma separated). This was declared as a single varchar rather than 2 fields so we would have the option of storing X, Y, and Z in the future.
Indexes
	_0	ON qcc_file_id, unique_dim_number, snapshot_id
	Idx_ers_snapshot_dim_map_qcc_file_id	ON qcc_file_id
	Idx_ers_snapshot_dim_map_snapshot_id	ON snapshot_id
Foreign Keys
	fk_ers_snapshot_dim_map	( qcc_file_id ) ref qcc_file (qcc_file_id)
	fk_ers_snapshot_dim_map_snapshot	( snapshot_id ) ref ers_snapshot (snapshot_id)

Table ers_stats_data

DEPRECATED

Indexes	Field Name	Data Type
*	calc_id	int AUTOINCREMENT
	effective_date	datetime
	config_id	int
	group_id	int
	qcc_file_id	int
	dim_id	int
	filter_id	int
	calculation	nvarchar( 20 )
	value	float
Indexes
	PK_stats_data	ON calc_id

Table ers_task

This table includes tasks that appear in QC-Mobile's task list widget on the home screen.

Indexes	Field Name	Data Type	Description
*	task_id	int AUTOINCREMENT	The autonumbering primary key of the table.
*	task_type	int	A numeric indicator of the type of task to be performed. Current values are: 0 - Acknowledge Only 1 - Add Assignable Cause 2 - Add Corrective Action 3 - Add a Note 4 - Approve a Batch of parts
	task_user_id	int	The user id who created the task (not the recipient). This is null when tasks are assigned by the system.
	task_info	nvarchar(max)	A JSON field that describes everything needed for the user to execute the task. For instance, in the case of adding an assignable cause this would include information about the characteristic and record and reason for the assignable cause.
*	task_status	int	The current status of the task. Current values include: 0 - New/Not yet performed 1 - Complete 2 - Canceled
*	effective_date	datetime	The date and time the task was created.
	due_date	datetime	An optional due date for the task.
	complete_date	datetime	The date and time the task was completed (once completed)
	complete_user_id	int	The id of the user who completed the task.
*	required_flag	bit DEFAULT 0	Whether or not it is required that the user perform the task.
Indexes
	Pk_ers_task_task_id	ON task_id
	Idx_ers_task_task_user_id	ON task_user_id
	Idx_ers_task_complete_user_id	ON complete_user_id
Foreign Keys
	fk_ers_task_ers_user	( task_user_id ) ref ers_user (user_id)
	fk_ers_task_ers_user1	( complete_user_id ) ref ers_user (user_id)

Table ers_task_recipient

A list of task recipients. Recipients can include user groups, individual users, or both.

Indexes	Field Name	Data Type	Description
*	task_recip_id	int AUTOINCREMENT	The autonumbering primary key of the table.
*	task_id	int	The id of the task to which the recipients are attached.
	recip_group_id	int	A user group id if the recipient is a user group.
	recip_user_id	int	A user id if the recipient is a user.
Indexes
	Pk_ers_task_recipient_task_recip_id	ON task_recip_id
	Idx_ers_task_recipient_task_id	ON task_id
	Idx_ers_task_recipient_recip_group_id	ON recip_group_id
	Idx_ers_task_recipient_recip_user_id	ON recip_user_id
Foreign Keys
	fk_ers_task_recipient_ers_task	( task_id ) ref ers_task (task_id)
	fk_ers_task_recipient	( recip_group_id ) ref ers_user_group (user_group_id)
	fk_ers_task_recipient_ers_user	( recip_user_id ) ref ers_user (user_id)

Table ers_trigger

This table stores the information about ERS triggers. The triggers concept is about performing actions such as sending emails, generating reports, etc, when a number of parts changes.

Indexes	Field Name	Data Type	Description
*	trigger_id	int AUTOINCREMENT	The primary key.
	trigger_desc	nvarchar( 100 )	The short description of the trigger.
	trigger_conditions	nvarchar( 4000 )	Serialized conditions object.
	run_status	nvarchar( 15 )	Contains processing steps.
	refresh_rate	int	The number of seconds of how often the trigger must be checked. DEPRECATED as of May 2024 and replaced by recurrence.
	part_file_list_id	int	Trigger file selection: a foreign key to the part_file_named_list table.
	owner_user_id	int	A foreign key to the ers_user table.
	number_of_records	int	The number of records for reported set
	next_run_utc	datetime	The next run date to run the trigger in the universal time format.
	next_run_check_date_utc	datetime	The date when the trigger will be checked.
	monitor_all_files	bit	Trigger file selection: indicates that all part files should be used in the trigger.
	install_id	int	A foreign key to the installation table indicating the processor for the trigger.
	ers_service_id	int	The ERS service that processes this trigger.
	ers_service_group_id	int	A foreign key to the services group that processes this trigger.
	enabled_flag	bit	A flag that indicates wheater the trigger is enabled to autorun.
	dim_filter_id	int	Trigger file selection: a foreign key to the dimension filter.
	debug_flag	bit	A flag indicating that the trigger should run in debug mode.
	complete_records_flag	bit	A flag indicating that the trigger should only process completed records when running multi-source.
	calendar_id	int	The foreign key reference to the calendar the trigger should observe
	period_id	int	The foreign key reference to the production period to which the trigger will adhere. If specified, triggers will not run outside the hours of the production period.
*	trigger_style	int	This indicates the style and behavior of the trigger. As of the definition there are two styles; part-based and date-based. More may be developed in the future.
	trigger_lookback	int	This field is used for date-based triggers and allows a lookback override when looking for the expected number of parts. The field is denoted in seconds to lookback. This overrides the usual lookback which would use the bookmark date.
	recurrence	nvarchar( 1000 )	This replaces the refresh_rate which is deprecated as of May 2024. This is a JSON object that defines a more comprehensive scheduling allowing the user to schedule triggers to run daily, weekly, monthly, multiple times per day, or the traditional interval style of minutely or hourly.
	monitor_style	nvarchar( 4000 )	This is a JSON object that defines the monitoring for date-based triggers. Since workstations, part files, or trace field values can be monitored, this defines what is actually being monitored.
*	check_back_flag	bit DEFAULT 0	Indicates whether or not the trigger is currently in check back mode. In cases with date-based triggers, the trigger can automatically check back to see if the trigger has been satisfied if at least one escalation has been set. Check back is only launched if less than the expected number of parts has arrived when the trigger first runs.
	check_back_last_date	datetime	Indicates the last date the trigger checked back if in check back mode. Triggers can fire often based on the normal deliverable timer so this ensures we only check back once per minute.
*	escalate_flag_1	bit DEFAULT 0	A flag indicating that a date-based trigger should check back if less than the expected parts were found and then escalate after a certain number of minutes.
*	escalate_flag_2	bit DEFAULT 0	A flag indicating that a date-based trigger should check back if less than the expected parts were found and then escalate to a second level after a certain number of minutes.
	escalate_min_1	int	The number of minutes to wait before the first escalation event.
	escalate_min_2	int	The number of minutes to wait before the second escalation event.
	history_id	int	The history id of the trigger's run in the run history table. This is only saved during check back mode. When in check back mode, the initial run will create the history record. Subsequent check backs and escalations will then use the history id to update the log of the events that occurred until the trigger is either satisfied or gives up.
	orig_run_date	datetime	Stores the original run date in the event of checkback. For instance, if the trigger intended to run at 9:00am with a lookback of 60 minutes looking for 5 new parts measured, then we need to maintain the original date through the escalation process. If the trigger is escalating at 9:30am, it's important to make sure the trigger is looking back to 8am and not simply subtracting 60 minutes from the current time (thus looking back to 8:30a).
Indexes
	Pk_ers_trigger_trigger_id	ON trigger_id
	Idx_ers_trigger_part_file_list_id	ON part_file_list_id
	Idx_ers_trigger_owner_user_id	ON owner_user_id
	Idx_ers_trigger_install_id	ON install_id
	Idx_ers_trigger_ers_service_id	ON ers_service_id
	Idx_ers_trigger_ers_service_group_id	ON ers_service_group_id
	Idx_ers_trigger_dim_filter_id	ON dim_filter_id
	Idx_ers_trigger_calendar_id	ON calendar_id
	Idx_ers_trigger_period_id	ON period_id
Foreign Keys
	fk_ers_trigger	( part_file_list_id ) ref ers_part_file_named_list (list_id)
	fk_ers_trigger_ers_user	( owner_user_id ) ref ers_user (user_id)
	fk_ers_trigger_installation	( install_id ) ref installation (install_id)
	fk_ers_trigger_ers_service	( ers_service_id ) ref ers_service (ers_service_id)
	fk_ers_trigger_group	( ers_service_group_id ) ref ers_service_group (ers_service_group_id)
	fk_ers_trigger_ers_filter	( dim_filter_id ) ref ers_filter (filter_id)
	fk_ers_trigger_ers_calendar	( calendar_id ) ref ers_calendar (calendar_id)
	fk_ers_trigger_production_period	( period_id ) ref ers_production_period (period_id)

Table ers_trigger_action

This table contains action details for triggers.

Indexes	Field Name	Data Type	Description
*	trigger_action_id	int AUTOINCREMENT	The primary key.
*	trigger_id	int	The foreign key to the trigger table.
*	position	int	This number indicates the order in which ERS will execute the action.
*	action_type	int	The number representing the action type.
	action_params	nvarchar(max)	This field contains the servialized object with actions' details.
	action_desc	nvarchar( 100 )	A user' s friendly description of an action.
Indexes
	Pk_ers_trigger_action_trigger_action_id	ON trigger_action_id
	Idx_ers_trigger_action_trigger_id	ON trigger_id
Foreign Keys
	fk_ers_trigger_action	( trigger_id ) ref ers_trigger (trigger_id)

Table ers_trigger_date_bookmark

This table contains the bookmarks for date-based triggers which can monitor part files, workstations, or trace field values.

Indexes	Field Name	Data Type	Description
*	bookmark_id	int AUTOINCREMENT	The autocounting primary key of the table.
*	trigger_id	int	A foreign key reference to the ers_trigger table.
*	table	varchar( 100 )	The table of the monitored item. Since date-based triggers can monitor different types of items, this has been kept generic. The combination of table and table_pk fields will identify most items such as table="installation" and table_pk=5. Current values can be installation, part_factor, and qcc_file
	table_pk	int	The id of the table for the date-based bookmark. Applies to part file and workstation monitoring.
	trace_value	nvarchar( 100 )	Indicates the value of a text trace field being monitored. Value is stored (rather then any IDs) so triggers can work across models.
	trace_value_numeric	float	Indicates the numeric value of a numeric trace field being monitored. Value_Numeric is stored (rather then any IDs) so triggers can work across models.
*	bookmark_date	datetime	The date of the last time the expected number of records was found for the monitored item of the trigger.
Indexes
	Pk_ers_trigger_date_bookmark_bookmark_id	ON bookmark_id
	Idx_ers_trigger_date_bookmark_trigger_id	ON trigger_id
Foreign Keys
	fk_ers_trigger_date_bookmark	( trigger_id ) ref ers_trigger (trigger_id)

Table ers_trigger_file_bookmark

This table hold pointers to track processed records used in t riggers.

Indexes	Field Name	Data Type	Description
*	trigger_id	int	This table hold pointers to track processed records used in t riggers
*	qcc_file_id	int	The id of the part file.
*	num_of_records	int	The largest processed unique record number.
Indexes
	pk_ers_trigger_file	ON trigger_id, qcc_file_id
	Idx_ers_trigger_file_bookmark_trigger_id	ON trigger_id
	Idx_ers_trigger_file_bookmark_qcc_file_id	ON qcc_file_id
Foreign Keys
	fk_ers_trigger_file_bookmark_ers_trigger	( trigger_id ) ref ers_trigger (trigger_id)
	fk_ers_trigger_file_bookmark_qcc_file	( qcc_file_id ) ref qcc_file (qcc_file_id)

Table ers_trigger_qcc_file

This table contains the part files processed by triggers.

Indexes	Field Name	Data Type	Description
*	trigger_id	int	The foreign key to the ers_triggers table.
*	qcc_file_id	int	The foreign key to the qcc_file table.
Indexes
	ers_trigger_qcc_file_primary_key	ON trigger_id, qcc_file_id
	Idx_ers_trigger_qcc_file_trigger_id	ON trigger_id
	Idx_ers_trigger_qcc_file_qcc_file_id	ON qcc_file_id
Foreign Keys
	fk_ers_trigger_ers_trigger	( trigger_id ) ref ers_trigger (trigger_id)
	fk_ers_trigger_qcc_file	( qcc_file_id ) ref qcc_file (qcc_file_id)

Table ers_user

Contains all user information in the system. This includes both linkage for audit/Part 11 purposes as well as ownership and permission within ERS and QC-Mobile.

Indexes	Field Name	Data Type	Description
*	user_id	int AUTOINCREMENT	The primary key of the table.
*	user_name	nvarchar( 100 )	The user name specified.
	user_login	nchar( 1000 )	The password.
*	enabled_flag	bit DEFAULT 1	Flag indicating whether or not this user is enabled. Encrypted.
	password_expire	datetime	A date/time indicating when the password will expire.
*	password_change_flag	bit DEFAULT 0	A flag indicating that the password must be changed on next login.
	signature_pic	image	A binary image of the user's signature.
	password_history	nchar( 1000 )	A list of the last x passwords (encrypted).
*	sso_flag	bit DEFAULT 0	Flag indicating whether or not we are using single-sign on.
	qcm_login	nvarchar( 2000 )	Encrypted licensing string for QC-Mobile. For Prolink purposes only. Setting to null when not null will void the QC-Mobile license.
	email_address	nvarchar( 255 )
	password_life_in_days	int DEFAULT null	How many days must elapse (since last change) before the password must be changed. QC-CALC RealTime
	account_deactivate_max_attempts	int DEFAULT 0	How many consecutive failed attempts are allowed before deactiving the user (account_deactivate_login_fails_ind must be True).
	account_deactivate_login_fails_ind	bit DEFAULT false	Whether or not to deactive a user if they exceed their max login attempts.
	auto_assign_to_group_name	nvarchar( 100 ) DEFAULT null	The group name to auto assign a user to when they log in. Part 11.
	auto_assign_to_group_name_ind	bit DEFAULT false	Whether or not to auto assign a user to a Part 11 group.
Indexes
	PK_ers_user	ON user_id

Table ers_user_group

This table represents user groups in the system. These are used for file permission and resource sharing.

Indexes	Field Name	Data Type	Description
*	user_group_id	int AUTOINCREMENT	The primary key of the table.
*	user_group_desc	nvarchar( 50 )	The name of the user group.
	edl_desc	nvarchar( 200 )	The EDL description for the group. Users within the group have dynamic access to all QCC files with this EDL desc.
*	permission_level	int DEFAULT 0	Future expansion.
Indexes
	PK_ers_user_group	ON user_group_id

Table ers_user_group_file_map

This table is used for specific QCC files to which the user has permission or does not have permission to access beyond the dynamic EDL description.

Indexes	Field Name	Data Type	Description
*	user_group_id	int	The linked user group id.
*	qcc_file_id	int	The qcc file in question.
*	allow_flag	bit DEFAULT 1	A flag determining whether or not the user has permission to view this file. If true, user can see the file regardless as to whether or not it has the groups EDL desc. If false, opposite is true and file permission is denied regardless of the groups EDL desc.
*	permission_level	int DEFAULT 0	Future expansion.
Indexes
	PK_ers_user_group_file_map	ON user_group_id, qcc_file_id
Foreign Keys
	FK_ers_user_group_file_map_ers_user_group	( user_group_id ) ref ers_user_group (user_group_id)
	FK_ers_user_group_file_map_qcc_file	( qcc_file_id ) ref qcc_file (qcc_file_id)

Table ers_user_group_map

Many to many resolution table that maps users to groups. Each user can be a member of more than one group and each group has more than one user.

Indexes	Field Name	Data Type	Description
*	user_group_id	int	The linked user group id.
*	user_id	int	The linked user id.
Indexes
	PK_ers_user_group_map	ON user_group_id, user_id
Foreign Keys
	FK_ers_user_group_map_ers_user	( user_id ) ref ers_user (user_id)
	FK_ers_user_group_map_ers_user_group	( user_group_id ) ref ers_user_group (user_group_id)

Table ers_user_group_setting

Generic data defined table containing user group settings.

Indexes	Field Name	Data Type	Description
*	group_setting_id	int AUTOINCREMENT	The primary key of the table.
*	user_group_id	int	The linked user group.
*	setting_name	nvarchar( 50 )	The setting name.
	setting_value	nvarchar(max)	The setting's value.
Indexes
	PK_ers_group_setting	ON group_setting_id
Foreign Keys
	FK_ers_group_setting_ers_user_group	( user_group_id ) ref ers_user_group (user_group_id)

Table ers_user_group_view

Table that contains resources that are viewed/shared at the group level.

Indexes	Field Name	Data Type	Description
*	group_view_id	int AUTOINCREMENT	The primary key of the table.
*	user_group_id	int	The linked user group.
*	object_type	int	The resource type (report, filter, etc)
*	table_pk	int	The id of the linked resource.
Indexes
	PK_ers_group_view	ON group_view_id
Foreign Keys
	FK_ers_group_view_ers_user_group	( user_group_id ) ref ers_user_group (user_group_id)

Table ers_user_setting

A generic data defined table that contains user specific settings for ERS and QC-Mobile.

Indexes	Field Name	Data Type	Description
*	setting_id	int AUTOINCREMENT	The primary key of the table.
*	user_id	int	The user linked to the setting.
*	setting_name	varchar( 50 )	The name of the setting.
	setting_value	text	The value of the setting.
Indexes
	PK_user_setting	ON setting_id
Foreign Keys
	FK_user_setting_user	( user_id ) ref ers_user (user_id)

Table ers_view

This table contains all viewed resources for a particular user. Users can either copy other users' resources or view them. When a view is set up, they are added to this table.

Indexes	Field Name	Data Type	Description
*	view_id	int AUTOINCREMENT	The primary key of the table.
*	user_id	int	The user who is viewing the resource.
*	object_type	int	The type of resource (report, export, filter, dashboard, etc).
*	table_pk	int	The id of the resource.
Indexes
	PK_ers_view	ON view_id
Foreign Keys
	FK_ers_view_ers_user	( user_id ) ref ers_user (user_id)

Table exception_log

This logs exceptions in general. At the moment, it is mostly just logging exceptions from services running deliverables for ERS but this will be expanded in the future.

Indexes	Field Name	Data Type	Description
*	exc_id	int AUTOINCREMENT	The auto numbering primary key of the table.
	effective_date	date	The date/time of the exception.
	category	varchar( 50 )	The type of exception. "deliverables" specifies that it is a deliverable type exception.
	object_type	int	An integer specifying the type of object. In the case of deliverables, it is the enumeration for the type of deliverable (full view, dashboard, etc).
	object_id	int	The generic primary key of the object in question. In the case of deliverables, the fullview_id, dashboard_id, etc.
	desc	nvarchar( 1000 )	The description of the exception.
	stack_trace	nvarchar(max)	The stack trace of the exception if it's a software exception/error. If it's a validation issue, this will be left blank.
	extra_info	nvarchar(max)	An extra field that can be used to provide more information about the exception beyond the regular description. As an example, in the case of dashboards, this field will often contain the row and column of the widget that had an issue.
	install_id	int	The id of the installation of software.
	ers_service_id	int	The id of the ERS service if the exception is related to a service running a deliverable. Since the same installation can create multiple services, this will give us specificity on which service actually failed. If ERS and the install_id is not null and this id is null, it means that ERS was running as an application.
*	severity	tinyint DEFAULT 0	An indicator telling us the severity of the exception. The values are as follows: -1 - Diagnostic run - The system was checking for errors before actually attempting to run and found an issue. 0 - Informational - Information that is relevant but is not causing any issues in the running. 1 - Warning - Exception that is not causing an error, but could lead to problems. 2 - Exception - An actual error occurred.
*	ack_flag	bit DEFAULT 0	A flag indicating whether or not the exception has been acknowledged by a user.
Indexes
	Pk_exception_log_exc_id	ON exc_id

Table factor

Contains the definitions of factors at the model level. Does not contain factor values themselves.

Indexes	Field Name	Data Type	Description
*	factor_id	int AUTOINCREMENT	The primary key of the table.
*	qcc_file_model_id	int	The id of the linked model.
*	factor_number	int	The ordinal position of the factor.
*	factor_desc	nvarchar( 100 )	The name of the factor.
*	factor_type	varchar( 50 )	The factor type (numeric, text, date).
	precision	int DEFAULT Null	The number of places to the right of the decimal point for Numeric Factors.
*	historical_plot_flag	bit DEFAULT 1	True when column is visible in SPC and Buddy grid view
Indexes
	PK_factor	ON factor_id	Primary key of the table.
	IX_type_desc_model	ON factor_type, factor_desc, qcc_file_model_id	Index on factor type, description, and model. This allow searching based on "text" factor named "Operator" and optionally specifying the model.
	IX_type_number_model	ON factor_type, factor_number, qcc_file_model_id	Index on factor type, description, and model. This allow searching based on "text" factor number 5 and optionally specifying the model.
	FK_qcc_file_model	ON qcc_file_model_id	Foreign key on the model id used for joining to qcc_file_model
Foreign Keys
	FK_factor_part_type	( qcc_file_model_id ) ref qcc_file_model (qcc_file_model_id)

Table installation

This table holds the information for each workstation that uses this database. Information includes the workstation name, product code, database version, and last login.

Indexes	Field Name	Data Type	Description
*	install_id	int AUTOINCREMENT	The primary key of the table.
*	workstation	varchar( 50 )	The name of the PC connecting to this database.
	install_path	nvarchar( 8000 )	The path of the installation of the software.
*	product_type_code	varchar( 50 )	The type code of the product (ex. ERS, EDL, RT, SPC, etc).
*	product_version	varchar( 10 )	The version of the product installed. 3.4, etc.
	database_version	varchar( 10 )	The latest version of the database this product knows about.
	last_login	datetime	The latest date/time the application logged into the database.
	service_visible_flag	bit	Used to determine whether or not the service (ERS) should be visible to everyday users. When a user rolls a report, dashboard, etc into production s/he will choose a service that will run it. This flag allows administrators to turn off individuals that are not production worthy.
Indexes
	Pk_workstation_0	ON install_id

Table installation_ext

This table holds the information for each workstation that is updated. It is used to save the current application revision, a Json file that holds scheduled update DateTime, new revision number and update path, and the last application update DateTime.

Indexes	Field Name	Data Type	Description
*	install_id	int AUTOINCREMENT	The primary key of the table.
	revision	int	Used to save the current application revision number
	update_info	nvarchar( 1000 )	Used to save JSON file containing set update DateTime, new revision number and update path.
	last_update_date	date	Used to save the last application update DateTime
	workstation_alias	nvarchar( 100 )	A friendly name for the workstation. Workstations in corporate environments tend to be more like asset tags so this allows users to have recognizable names.
	last_user_name	nvarchar( 100 )	The name of the last user that was logged into the workstation. This is used for helping to identify the workstation when the name is not obvious. It is deliberately not linked to the ers_user table since it's just a regular Windows user name.
Indexes
	Pk_workstation_1	ON install_id
	PK_installation_ext	ON install_id
Foreign Keys
	fk_installation_ext	( install_id ) ref installation (install_id)

Table measurement

Table that contains the literal measurement values of all qcc files.

Indexes	Field Name	Data Type	Description
*	part_id	int	The linked part id.
*	dim_id	int	The linked dimension id.
*	value	float	The value.
*	deleted_flag	bit DEFAULT 0	The point deleted flag.
	note_id	int	A link to the notes if a note was added to the measurement.
	original_user_id	int	The original user who entered the value.
	current_user_id	int	The current user who has changed the value (if changed). On insert, original and current users are same. On update, current user changes and original stays the same.
	global_gage_id	int	A direct link to the global gage that measured the characteristic.
	measurement_date	date	The date the actual measurement took place. Only used with QC-Gage and manual gaging.
	extra_info	nvarchar( 50 )	Any extra information the user wishes to store at the measurement level.
	global_gage_id_2	int	This is the second global gage id for the measurement. This is rare and will typically be null, but may be used in situations where there are two opposing probes that are determining the size of a part. In order to capture both, we have added the ID here.
Indexes
	PK_measurement	ON part_id, dim_id	The primary key of the table.
	FK_note	ON note_id	Index on the foreign key to note table for join purposes.
	IX_dim_part_exclude	ON dim_id, part_id, deleted_flag	Index on dimension, part and then deleted/exclude flag. The primary key already indexes part id and then dimension id so this does the opposite allowing querying by either part or dim or both.
	Idx_measurement_global_gage_id	ON global_gage_id
	Idx_measurement_global_gage_id_2	ON global_gage_id_2
Foreign Keys
	FK_measurement_dimension1	( dim_id ) ref dimension (dim_id)
	FK_measurement_current_ers_user	( current_user_id ) ref ers_user (user_id)
	FK_measurement_ers_user	( original_user_id ) ref ers_user (user_id)
	FK_measurement_note	( note_id ) ref note (note_id)
	FK_measurement_part1	( part_id ) ref part (part_id)
	fk_measurement	( global_gage_id ) ref qc_gage_global_gage (global_gage_id)
	fk_measurement_global_gage_2	( global_gage_id_2 ) ref qc_gage_global_gage (global_gage_id)

Table measurement_action_history

Contains a history of changes to the assignment of corrective actions to measurements.

Indexes	Field Name	Data Type	Description
*	part_id	int	The part id of the linked measurement.
*	dim_id	int	The dimension id of the linked measurement.
*	action_number	int	The action number that changed.
*	effective_date	datetime	The date of the change.
	old_action_id	int	The old corrective action that was assigned to this position.
	new_action_id	int	The new corrective action that is assigned to this position.
	user_id	int	The user who made the change.
	reason_id	int	The reason for the change.
	edl_load_date	datetime	The date EDL loaded the change.
Indexes
	PK_measurement_action_history\	ON part_id, dim_id, action_number, effective_date
Foreign Keys
	FK_measurement_action_history_corrective_action	( old_action_id ) ref corrective_action (action_id)
	FK_measurement_action_history_corrective_action1	( new_action_id ) ref corrective_action (action_id)
	FK_measurement_action_history_dimension	( dim_id ) ref dimension (dim_id)
	FK_measurement_action_history_ers_user	( user_id ) ref ers_user (user_id)
	FK_measurement_action_history_part	( part_id ) ref part (part_id)
	FK_measurement_action_history_reason	( reason_id ) ref reason (reason_id)

Table measurement_action_map

Many to many resolution table between measurement and corrective actions. Each measurement can have more than one action and each action can be used by more than one measurement.

Indexes	Field Name	Data Type	Description
*	part_id	int	The linked part id for the measurement.
*	dim_id	int	The linked dimension id for the measurement.
*	action_id	int	The linked corrective action.
*	action_number	int	The ordinal position of the action (1st, 2nd).
Indexes
	PK_measurement_action_map	ON part_id, dim_id, action_id, action_number
Foreign Keys
	FK_measurement_action_map_corrective_action	( action_id ) ref corrective_action (action_id)
	FK_measurement_action_map_measurement	( part_id, dim_id ) ref measurement (part_id, dim_id)

Table measurement_cause_history

Contains a history of changes to the assignment of assignable causes to measurements.

Indexes	Field Name	Data Type	Description
*	part_id	int	The part id of the linked measurement.
*	dim_id	int	The dimension id of the linked measurement.
*	cause_number	int	The ordinal position of the assignable cause being changed.
*	effective_date	datetime	The date of the change.
	old_cause_id	int	The old assignable cause in that position.
	new_cause_id	int	The new assignable cause in that position.
	user_id	int	The user who made the change.
	reason_id	int	The reason for the change.
	edl_load_date	datetime	The date EDL uploaded the change.
Indexes
	PK_measurement_cause_history	ON part_id, dim_id, cause_number, effective_date
Foreign Keys
	FK_measurement_cause_history_assignable_cause	( old_cause_id ) ref assignable_cause (cause_id)
	FK_measurement_cause_history_assignable_cause1	( new_cause_id ) ref assignable_cause (cause_id)
	FK_measurement_cause_history_dimension	( dim_id ) ref dimension (dim_id)
	FK_measurement_cause_history_ers_user	( user_id ) ref ers_user (user_id)
	FK_measurement_cause_history_part	( part_id ) ref part (part_id)
	FK_measurement_cause_history_measurement_cause_history	( reason_id ) ref reason (reason_id)

Table measurement_cause_map

Many to many resolution table that maps measurements to assignable causes. Measurements can have more than one assignable cause and assignable causes can be used by multiple measurements.

Indexes	Field Name	Data Type	Description
*	part_id	int	The part id of the measurement.
*	dim_id	int	The dim id of the measurement.
*	cause_id	int	The cause id of the mapped assignable cause.
*	cause_number	int	The ordinal position of the cause (1st, 2nd, etc).
Indexes
	PK_measurement_cause_map	ON part_id, dim_id, cause_id, cause_number
Foreign Keys
	FK_measurement_cause_map_assignable_cause	( cause_id ) ref assignable_cause (cause_id)
	FK_measurement_cause_map_measurement	( part_id, dim_id ) ref measurement (part_id, dim_id)

Table measurement_history

Contains a historical record of changes to the measurements.

Indexes	Field Name	Data Type	Description
*	history_id	int AUTOINCREMENT	The autonumbering primary key of the table.
*	part_id	int	The part id of the measurement.
*	dim_id	int	The dimension id of the measurement.
*	effective_date	datetime	The effective date of the change.
*	field_changed	varchar( 50 )	The field that changed (note, value, deleted flag)
	old_value	nvarchar( 100 )	The old value of the field.
	new_value	nvarchar( 100 )	The new value of the field.
	user_id	int	The user who made the change.
	reason_id	int	The reason for the change.
	edl_load_date	datetime	The date the change was loaded by EDL.
	source_ind	int	Indicates the source of the history record. If set to 1, the change was made through a multisource update to the record. If set to 0, the change was made interactively by a user.
Indexes
	PK_measurement_history	ON history_id
	IX_measurement_history_part_id	ON part_id
	IX_measurement_history_dim_id	ON dim_id
Foreign Keys
	FK_measurement_history_dimension	( dim_id ) ref dimension (dim_id)
	FK_measurement_history_ers_user	( user_id ) ref ers_user (user_id)
	FK_measurement_history_measurement_history	( part_id ) ref part (part_id)

Table note

Contains user notes for QC-CALC. Mapped directly to measurements.

Indexes	Field Name	Data Type	Description
*	note_id	int AUTOINCREMENT	The primary key of the table.
	note_desc	nvarchar( 4000 )	The note itself.
Indexes
	PK_measurement_note	ON note_id

Table part

Contains all parts/records for a QCC file.

Indexes	Field Name	Data Type	Description
*	part_id	int AUTOINCREMENT	The primary key of the table.
*	qcc_file_id	int	File that contains this part. Added 1.7.1
	unique_record_number	int	The unique record number regardless of circular status.
	record_number	int	The current record number which repeats in case of circular. As of QC-CALC4.0, this will be identical to unique_record_number and will likely be deprecated as no longer necessary.
*	sub_group_id	int	DEPRECATED.
	measure_date	datetime	The measure date of the part.
*	deleted_flag	bit DEFAULT 0	Flag indicating whether or not part should be excluded from statistics.
	edl_load_date	datetime	The date the part was loaded by EDL.
	signoff_date	datetime	The original creation date for the part. Set once.
Indexes
	PK_part	ON part_id	Primary key of the table.
	IX_file_exclude	ON qcc_file_id, deleted_flag	Index allowing search on file id and deleted/excluded flag. Includes all other fields to reduce bookmark lookups and increase performance.
	IX_file_measure_date	ON qcc_file_id, measure_date	Index allowing search on file id and measure date of part. Includes all other fields to increase performance.
	IX_file_unique_rec_num	ON qcc_file_id, unique_record_number	Index on file_id and unique_record_number allowing searches on the last written record for the file, etc.
	IX_file	ON qcc_file_id	Index on qcc_file_id
Foreign Keys
	Fk_part_qcc_file	( qcc_file_id ) ref qcc_file (qcc_file_id)

Table part_factor

Contains actual factor values.

Indexes	Field Name	Data Type	Description
*	part_id	int	The part id linking back to the part.
*	factor_id	int	The factor id linking back to the factor definition.
	value	nvarchar( 100 )	The value if the factor is a text factor.
	value_numeric	float	The value is the factor is a numeric factor.
	value_datetime	datetime	The value if the factor is a date factor.
	original_user_id	int
	current_user_id	int
Indexes
	PK_part_factor	ON part_id, factor_id	The primary key on the table.
	IX_part_factor_value	ON part_id, factor_id, value	Index on part, factor, and value fields for joining and querying by value (text trace fields).
	IX_part_factor_value_numeric	ON part_id, factor_id, value_numeric	Index on part, factor, and value_numeric fields for joining and querying by value_numeric (numeric trace fields).
Foreign Keys
	FK_part_factor_factor	( factor_id ) ref factor (factor_id)
	FK_part_factor_part	( part_id ) ref part (part_id)

Table part_factor_history

Contains a history of the changes to factor values.

Indexes	Field Name	Data Type	Description
*	history_id	int AUTOINCREMENT	The auto-numbering primary key for the table.
*	part_id	int	The part id of the factor value.
*	factor_id	int	The factor id of the factor value.
*	effective_date	datetime	The date of the change.
*	field_changed	varchar( 50 )	The field that change (value).
	old_value	nvarchar( 100 )	The old value of the field.
*	new_value	nvarchar( 100 )	The new value of the field.
	user_id	int	The user who made the change.
	reason_id	int	The reason for the change.
	edl_load_date	datetime	The date EDL loaded the change.
	source_ind	int	Indicates the source of the history record. If set to 1, the change was made through a multisource update to the record. If set to 0, the change was made interactively by a user.
Indexes
	PK_factor_history	ON history_id
Foreign Keys
	FK_part_factor_history_ers_user	( user_id ) ref ers_user (user_id)
	FK_factor_history_factor	( factor_id ) ref factor (factor_id)
	FK_factor_history_part	( part_id ) ref part (part_id)
	FK_factor_history_reason	( reason_id ) ref reason (reason_id)

Table part_history

Contains a historical record of changes to part records.

Indexes	Field Name	Data Type	Description
*	part_id	int	The part id of the linked part.
*	effective_date	datetime	The date of the change.
*	field_changed	varchar( 50 )	The field that changed (measure date, deleted_flag)
*	old_value	nvarchar( 100 )	The old value of the field.
*	new_value	nvarchar( 100 )	The new value of the field.
	user_id	int	The user who made the change.
	reason_id	int	The reason for the change.
	edl_load_date	datetime	The date it was loaded by EDL.
	source_ind	int	Indicates the source of the history record. If set to 1, the change was made through a multisource update to the record. If set to 0, the change was made interactively by a user.
*	history_id	int AUTOINCREMENT	The autocounting primary key of the table.
Indexes
	Pk_part_history_history_id	ON history_id
Foreign Keys
	FK_part_history_ers_user	( user_id ) ref ers_user (user_id)
	FK_part_history_part	( part_id ) ref part (part_id)

Table part_source

Maintains the sources (install_id) and measure date for each part.

Indexes	Field Name	Data Type	Description
*	part_source_id	int AUTOINCREMENT	The autonumbering primary key of the table.
	part_id	int	Foreign key mapped back to part table.
*	measure_date	datetime	The measure date that came from the source.
*	install_id	int	The install id of the copy of QC-CALC RT that inserted the record.
Indexes
	Pk_part_source_part_source_id	ON part_source_id
	Idx_part_source_part_id	ON part_id
	Idx_part_source_install_id	ON install_id
Foreign Keys
	fk_part_source_part	( part_id ) ref part (part_id)
	fk_part_source_installation	( install_id ) ref installation (install_id)

Table qc_calc_spc_group

Indexes	Field Name	Data Type
*	spc_group_id	int AUTOINCREMENT
*	name	nvarchar( 100 )
	content	ntext
Indexes
	PK_spc_group	ON spc_group_id

Table qc_gage_connection

This repreesnts the gage connections in QC-Gage. The connection may be a one-to-one as in the case with file based gages or it may be a one-to-many as is often the cases with RS-232 gages with multiplexers where one connection on a COM port actually serves multiple gages. This table is the connection portion only.

Indexes	Field Name	Data Type	Description
*	connection_id	int AUTOINCREMENT	The primary key of the table.
*	connection_type	int DEFAULT 0	The type id of the connection. This is an enumeration where 0 = keyboard, 1 = Serial port/RS-232, 2 = file based, etc.
*	workstation	nvarchar( 100 )	The name of the workstation where the connection is being added. This is the new location for workstation replaces the qc_gage_global_gage.workstation field which is now deprecated.
Indexes
	Pk_qc_gage_connection_connection_id	ON connection_id

Table qc_gage_connection_param

This houses all parameters for the connection. For instance, file-based connections would have a path parameter where as RS-232 connections would have fields for baud rate, parity, etc.

Indexes	Field Name	Data Type	Description
*	param_id	int AUTOINCREMENT	The primary key of the table.
*	connection_id	int	A foreign key reference to the connection forming a one-to-many relationship where each connection can have more than one parameter.
*	name	nvarchar( 100 )	The name of the parameter being saved. For instance for file based connection "path" might be the name.
	value	ntext	The value of the parameter. For example, in the case of file-based parameters, the value may be the path itself to find the file to be collected.
Indexes
	Pk_qc_gage_connection_param_param_id	ON param_id
	Idx_qc_gage_connection_param_connection_id	ON connection_id
Foreign Keys
	fk_qc_gage_connection_param	( connection_id ) ref qc_gage_connection (connection_id)

Table qc_gage_gages

Stores gage settings for global gages in QC-Gage. Since the settings are vastly different between gages, this generic table allows them to be self-describing.

Indexes	Field Name	Data Type	Description
*	gage_id	int AUTOINCREMENT	The primary key of the table.
*	computer_name	nvarchar( 100 )	The name of the computer that stored the gage.
*	category	varchar( 100 )	The UID for each gage. All name, value and data_type values for each gage will be stored using the same UID in this column.
*	name	nvarchar( 100 )	The name of the setting.
	value	nvarchar(max)	The value of the setting.
*	data_type	nvarchar( 20 )	The type of the data field. String, Int and Single are stored.
*	global_gage_id	int	A foreign key link to the global gage table.
Indexes
	Pk_qc_gage_gages_gage_id	ON gage_id
	Idx_qc_gage_gages_global_gage_id	ON global_gage_id
Foreign Keys
	fk_qc_gage_gages	( global_gage_id ) ref qc_gage_global_gage (global_gage_id)

Table qc_gage_global_gage

This is the main table that houses global gages and is linked to the measurement table allowing the tracking of measurements at the individual hand tool/gage level.

Indexes	Field Name	Data Type	Description
*	global_gage_id	int AUTOINCREMENT	The primary key of the table.
*	workstation	nvarchar( 100 )	The name of the workstation on which the gage is installed.
*	name	nvarchar( 100 )	The friendly name of the global gage.
*	guid	nvarchar( 50 )	A globally unique id that links to the spec plan.
	brand	nvarchar( 50 )	The brand of the gage if known.
	model	nvarchar( 50 )	The model of the gage if known.
	serial_number	nvarchar( 50 )	The serial number of the gage if known.
	bar_code	nvarchar( 100 )	A bar code identifier for the gage.
	gage_status	int	An indicator showing the current status of the gage. Status is typically going to be either active or inactive allowing gages on the same workstation with the same GUID to be swapped and still run inside spec plans. As long as there is only one active gage with the same GUID on the same workstation, everything will work correctly.
	connection_id	int	A foreign key reference to the connection id since each connection can have more than one global gage attached. A common example of this is a COM port with multiplexer where the same COM port can have multiple gages on different channels.
	parent_global_gage_id	int	This refers back to the parent of the current global gage (if there is one). Parents are global gages that are literally plugged in and configured for the current workstation and this ID will be null for those records. Children are global gage records that are simply links back to the parent for the purposes of running a spec plan that was not written on the same PC. Since the spec plan will have GUIDs for its own workstation's parent global gages, we add child records here so we can link the spec plan's GUID expectation with the local parent gage that is physically attached.
Indexes
	Pk_qc_gage_global_gage_global_gage_id	ON global_gage_id
	Idx_qc_gage_global_gage_connection_id	ON connection_id
Foreign Keys
	fk_qc_gage_global_gage	( connection_id ) ref qc_gage_connection (connection_id)

Table qc_gage_global_gage_calibration_history

This table houses the calibration history for each global gage. The idea is to have time periods where the gage is calibrated on a particular date and then due on another date. Between those two dates, the gage is considered good. If there is a gap between the due date of one record and the cal date of another record for the same gage, the gage was said to be out of calibration during that period.

Indexes	Field Name	Data Type	Description
*	history_id	int AUTOINCREMENT	The primary key of the table.
*	global_gage_id	int	Foreign key reference to the global gage table.
*	calibration_date	date	The date the gage was calibrated.
*	calibration_due_date	date	The date the next calibration is due.
Indexes
	Pk_qc_gage_global_gage_calibration_history_history_id	ON history_id
	Idx_qc_gage_global_gage_calibration_history_global_gage_id	ON global_gage_id
Foreign Keys
	fk_qc_gage_global_gage_calibration_history	( global_gage_id ) ref qc_gage_global_gage (global_gage_id)

Table qc_gage_linked_trace_field

Linked trace field values that are associated with Master trace fields

Indexes	Field Name	Data Type	Description
*	master_trace_field_id	int	Master trace field ID
*	linked_trace_field_type	int	trace field type (text or numeric)
*	linked_trace_field_number	int	trace field number
	value	nvarchar( 100 )	text value
	value_numeric	float	numeric value
Indexes
	PK_qc_gage_linked_trace_field	ON master_trace_field_id, linked_trace_field_type, linked_trace_field_number
Foreign Keys
	fk_qc_gage_linked_trace_field	( master_trace_field_id ) ref qc_gage_master_trace_field (master_trace_field_id)

Table qc_gage_master_trace_field

Tracks Master trace field values

Indexes	Field Name	Data Type	Description
*	master_trace_field_id	int AUTOINCREMENT	Primary Key to a Master trace field value record
*	spec_plan_id	int	ID of spec plan that this Master trace field is found in.
*	master_trace_field_type	int	trace field type (text or numeric)
*	master_trace_field_number	int	trace field number on spec plan
	value	nvarchar( 100 )	text value
	value_numeric	float	numeric value
*	last_used_date	datetime	Last time Master trace field was used.
Indexes
	Pk_qc_gage_master_trace_field_master_trace_field_id	ON master_trace_field_id
	Idx_qc_gage_master_trace_field_spec_plan_id	ON spec_plan_id
Foreign Keys
	Fk_qc_gage_master_trace_field_qc_gage_spec_plan	( spec_plan_id ) ref qc_gage_spec_plan (spec_plan_id)

Table qc_gage_spec_plan

Stores the main spec plan description and path information.

Indexes	Field Name	Data Type	Description
*	spec_plan_id	int AUTOINCREMENT	The primary key of the table.
*	path	text	The path to the spec plan on the network.
	desc	nvarchar( 100 )	A description of the spec plan.
	content	ntext	Reserved for future use.
	content_type	nvarchar( 100 )	Reserved for future use.
Indexes
	Pk_qc_gage_spec_plan_spec_plan_id	ON spec_plan_id

Table qc_gage_spec_plan_setting

Stores temporary values that are saved when spec plans are run across multiple sessions.

Indexes	Field Name	Data Type	Description
*	setting_id	int AUTOINCREMENT	The primary key of the table.
*	spec_plan_id	int	A foreign key reference back to the qc_gage_spec_plan table. There are many settings to one spec plan.
*	category	nvarchar( 100 )	The general type or section of the spec plan values being saved. This allows us to group the settings by category.
*	name	varchar( 1000 )	The name of the setting.
	value	ntext	The value of the setting as entered by the user.
*	data_type	nvarchar( 100 )	The type of the data field. For instance, text vs. numeric.
Indexes
	Pk_qc_gage_spec_plan_setting_setting_id	ON setting_id
	Idx_qc_gage_spec_plan_setting_spec_plan_id	ON spec_plan_id
Foreign Keys
	fk_qc_gage_spec_plan_setting	( spec_plan_id ) ref qc_gage_spec_plan (spec_plan_id)

Table qc_sort_plan

This table stores QC-Sort plans. Note that the qcc_file_id is linked inside the content text of the record. The application handles the case when the part file doesn't exist.

Indexes	Field Name	Data Type	Description
*	qc_sort_plan_id	int AUTOINCREMENT	The auto-generated id for the table.
*	name	varchar( 100 )	The name of the sort plan. When running QCQ it's the file name without the extension.
	content	text	The QC-Sort plan ini file stored like blob.
Indexes
	Pk_qc_sort_plan_qc_sort_plan_id	ON qc_sort_plan_id

Table qcc_file

This represents a part file in the database at the highest level.

Indexes	Field Name	Data Type	Description
*	qcc_file_id	int AUTOINCREMENT	The primary key of the table.
*	qcc_file_desc	nvarchar( 100 )	The name of the QCC file.
	creation_date	datetime	The internal creation date of the part file.
	edl_desc	nvarchar( 100 )	The EDL description that helps to ID the part file as from a particular location.
*	archive_ind	int DEFAULT 0	A tristate indicator indicating whether the file is unarchived (0), archived (1), or overridden to be unarchived (2).
	qcc_file_alias	nvarchar( 255 )
	last_edit_date	datetime	The date/time of the last edit to the QCC file.
	qcc_file_path	nvarchar( 8000 )	The path to the QCQ file. Used by global.dat.
	category_id	int	The foreign key to the category table.
*	file_type	int DEFAULT 0	This will hold the file type (normal, GRR, or tryout) as an integer.
Indexes
	PK_qcc_file	ON qcc_file_id	Primary Key of table.
	IX_cat_location_archive	ON category_id, edl_desc, archive_ind	Index allowing searching on category, location, and archive indicator or just category alone.
	IX_desc_archive_date	ON qcc_file_desc, archive_ind, creation_date	Index on the description, archive indicator and date allowing typical searching for the file description and active state.
	IX_location_cat_archive	ON edl_desc, category_id, archive_ind	Index allowing searhing by location and category or just location alone.
Foreign Keys
	fk_qcc_file_category	( category_id ) ref category (category_id)

Table qcc_file_history

Contains historical changes at the QCC file level.

Indexes	Field Name	Data Type	Description
*	qcc_file_history_id	int AUTOINCREMENT	The primary key of the table.
*	qcc_file_id	int	The linked QCC file.
*	effective_date	datetime	The date of the change.
*	action_desc	nvarchar( 500 )	The change itself (typically part 11 actions).
	user_id	int	The user who made the change.
	reason_id	int	The reason code of the change.
	edl_load_date	datetime	The date it was loaded by EDL.
Indexes
	PK_qcc_file_history	ON qcc_file_history_id
Foreign Keys
	FK_qcc_file_history_ers_user	( user_id ) ref ers_user (user_id)
	FK_qcc_file_history_qcc_file	( qcc_file_id ) ref qcc_file (qcc_file_id)

Table qcc_file_model

Contains the models or snapshots of the QCC files. Models are created whenever the structure of the QCC file changes so we can preserve old settings for old values while supporting new values. Models are often created when the number of features or factor changes, tolerances change, or other structural changes happen to the QCC file.

Indexes	Field Name	Data Type	Description
*	qcc_file_model_id	int AUTOINCREMENT	The primary key of the table.
*	qcc_file_id	int	The id of the linked QCC file.
*	effective_date	datetime	The date the model was created (and presumably when the file changed).
	sub_group	int	The sub group size of the model.
Indexes
	PK_qcc_file_model	ON qcc_file_model_id	Primary key of the table.
	FK_qcc_file	ON qcc_file_id	Index on foreign key to qcc_file for joining purposes.
Foreign Keys
	FK_part_type_part_type_group1	( qcc_file_id ) ref qcc_file (qcc_file_id)

Table qcc_file_settings

Generic data defined table that contains most of the custom settings from QCC files.

Indexes	Field Name	Data Type	Description
*	setting_id	int AUTOINCREMENT	The primary key of the table.
*	qcc_file_id	int	The linked QCC file.
	name	varchar( 100 )	The name of the setting.
	value	nvarchar(max)	The value of the setting.
	category	varchar( 100 )	The category or type of setting if specified.
*	data_type	nvarchar( 20 )	The data type of the setting (integer, text, etc).
	identification	nvarchar( 100 )
Indexes
	PK_qcc_file_settings	ON setting_id	Primary key of the table.
	FK_qcc_file_cat_name	ON qcc_file_id, category, name	Index on foreign key as well as category and name.
Foreign Keys
	FK_qcc_file_settings_qcc_file_settings	( qcc_file_id ) ref qcc_file (qcc_file_id)

Table reason

Contains reason codes used for 21 CFR Part 11 purposes.

Indexes	Field Name	Data Type	Description
*	reason_id	int AUTOINCREMENT	The primary key of the table.
*	reason_desc	nvarchar( 500 )	The reason code description.
Indexes
	PK_reason	ON reason_id

Table resource

Indexes	Field Name	Data Type	Description
*	resource_id	int AUTOINCREMENT	The auto incrementing primary key of the table.
	file_name	nvarchar( 255 )	The name of the file with extension. This is just the name and does not include the path.
	compression_type	varchar( 10 )	This is declared for future use, but is not used at this time.
	path	nvarchar( 8000 )	This is the external path to the resource. If storage_type is set to link the resource from the original location (0), this is the path to the resource.
	blob	varbinary(max)	This is the binary array of the file that was uploaded in the case where storage_type is set to embed in the database (1).
*	storage_type	int DEFAULT 0	This declares how the resource is being stored. It can be a link to an outside location using the path field or embedded in the database using the blob field. Accepted values: LinkFromOriginalLocation = 0 EmbedInDatabase = 1 StoreInPicturesFolderInQCQPath = 3 UseGlobalPictureLocation = 4
Indexes
	PK_blob1	ON resource_id

Table resource_association

Indexes	Field Name	Data Type	Description
*	resource_association_id	int AUTOINCREMENT	The auto incrementing primary key. This was done to allow table and table_pk to repeat without forcing uniqueness. This allows the same part file or dimension to be mapped to multiple resources simultaneously.
	table	varchar( 50 )	The table/entity to which the resource is being associated. This combined with table_pk allows you to associate a resource with a particular record in the table. For instance, table could be set to "dimension" and a dim_id is inserted into the table_pk associating a particular resource (i.e. dim picture) with a particular dimension.
	table_pk	int	The id of the table/entity to which the resource is being associated. This combined with table allows you to associate a resource with a particular record. For instance, table could be set to "dimension" and a dim_id is inserted into the table_pk associating a particular resource (i.e. dim picture) with a particular dimension.
*	resource_id	int	A foreign key to the resource table. This maps the outside record (i.e. dimension with a dim_id of x) to the resource in question.
	role	varchar( 50 )	The role defines the purpose of the resource association. For instance, when mapping dimension to a resource, the role will typically be "DimPicture" but may also be "DimDocumentation" or some other resource type.
Indexes
	PK_resource	ON resource_association_id
Foreign Keys
	FK_resource_blob	( resource_id ) ref resource (resource_id)

Table rt_monitor

Contains a running log of copies of QC-CALC RT and the files they currently have open.

Indexes	Field Name	Data Type	Description
*	monitor_id	int AUTOINCREMENT	The primary key of the table.
	qc_calc_location	nvarchar( 8000 )
	serial_number	varchar( 10 )	The serial number of the copy of QC-CALC RT.
	model	varchar( 50 )	The machine model code (OGPM, etc)
	current_qcc_file_id	int	The currently open QCC file
	real_time_description	nvarchar( 200 )
	machine_name	nvarchar( 300 )	The name of the workstation.
	is_running	bit	Flag indicating if RT is currently running or shut down.
	install_id	int	A foreign key reference to the installation table allowing us to get more information about the currently monitored part files.
Indexes
	PK_rt_monitor	ON monitor_id
	Idx_rt_monitor_install_id	ON install_id
Foreign Keys
	FK_rt_monitor_qcc_file	( current_qcc_file_id ) ref qcc_file (qcc_file_id)
	fk_rt_monitor_installation	( install_id ) ref installation (install_id)

Table user

DEPRECATED in favor of unified ers_user table.

Indexes	Field Name	Data Type
*	user_id	int AUTOINCREMENT
*	user_desc	nvarchar( 100 )
Indexes
	PK_user	ON user_id