1 Database Version 1.8.44 Move the mouse over tables & columns to read the comments. Dashboard Full View Prolink.Filtering Data Groups Report/Export User Related/Permission Main Data Section Deprecated ERS General General Snapshot ERS Triggers QC-Gage Fk FK_measurement_cause_map_assignable_cause measurement_cause_map ref assignable_cause ( cause_id ) cause_id Fk FK_measurement_cause_map_measurement measurement_cause_map ref measurement ( part_id, dim_id ) part_id,dim_id Fk FK_measurement_action_map_corrective_action measurement_action_map ref corrective_action ( action_id ) action_id Fk FK_measurement_action_map_measurement measurement_action_map ref measurement ( part_id, dim_id ) part_id,dim_id Fk FK_measurement_cause_history_assignable_cause measurement_cause_history ref assignable_cause ( old_cause_id -> cause_id ) old_cause_id Fk FK_measurement_cause_history_assignable_cause1 measurement_cause_history ref assignable_cause ( new_cause_id -> cause_id ) new_cause_id Fk FK_measurement_cause_history_dimension measurement_cause_history ref dimension ( dim_id ) dim_id Fk FK_measurement_cause_history_ers_user measurement_cause_history ref ers_user ( user_id ) user_id Fk FK_measurement_cause_history_part measurement_cause_history ref part ( part_id ) part_id Fk FK_measurement_cause_history_measurement_cause_history measurement_cause_history ref reason ( reason_id ) reason_id Fk FK_measurement_action_history_corrective_action measurement_action_history ref corrective_action ( old_action_id -> action_id ) old_action_id Fk FK_measurement_action_history_corrective_action1 measurement_action_history ref corrective_action ( new_action_id -> action_id ) new_action_id Fk FK_measurement_action_history_dimension measurement_action_history ref dimension ( dim_id ) dim_id Fk FK_measurement_action_history_ers_user measurement_action_history ref ers_user ( user_id ) user_id Fk FK_measurement_action_history_part measurement_action_history ref part ( part_id ) part_id Fk FK_measurement_action_history_reason measurement_action_history ref reason ( reason_id ) reason_id Fk FK_ers_chart_theme_ers_user ers_chart_theme ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk FK_user_setting_user ers_user_setting ref ers_user ( user_id ) user_id Fk FK_ers_user_group_map_ers_user ers_user_group_map ref ers_user ( user_id ) user_id Fk FK_ers_user_group_map_ers_user_group ers_user_group_map ref ers_user_group ( user_group_id ) user_group_id Fk FK_ers_user_group_file_map_ers_user_group ers_user_group_file_map ref ers_user_group ( user_group_id ) user_group_id Fk FK_ers_user_group_file_map_qcc_file ers_user_group_file_map ref qcc_file ( qcc_file_id ) qcc_file_id Fk FK_ers_group_view_ers_user_group ers_user_group_view ref ers_user_group ( user_group_id ) user_group_id Fk FK_ers_group_setting_ers_user_group ers_user_group_setting ref ers_user_group ( user_group_id ) user_group_id Fk FK_ers_selected_dimension_ers_selected_dimension ers_selected_dimension ref ers_selected_qcc_file ( group_id, qcc_file_id ) group_id,qcc_file_id Fk FK_ers_displayed_filters_ers_filter ers_displayed_filters ref ers_filter ( filter_id ) filter_id Fk FK_ers_displayed_filters_ers_group ers_displayed_filters ref ers_group ( group_id ) group_id Fk FK_ers_selected_qcc_file_ers_group ers_selected_qcc_file ref ers_group ( group_id ) group_id Fk FK_ers_selected_qcc_file_qcc_file ers_selected_qcc_file ref qcc_file ( qcc_file_id ) qcc_file_id Fk FK_ers_mapped_group_ers_configuration ers_mapped_group ref ers_configuration ( config_id ) config_id Fk FK_ers_mapped_group_ers_group ers_mapped_group ref ers_group ( group_id ) group_id Fk FK_ers_view_ers_user ers_view ref ers_user ( user_id ) user_id Fk FK_ers_dashboard_widget_ers_dashboard ers_dashboard_widget ref ers_dashboard ( dashboard_id ) dashboard_id Fk FK_ers_dashboard_widget_ers_filter ers_dashboard_widget ref ers_filter ( global_filter_id -> filter_id ) global_filter_id Fk FK_ers_dashboard_widget_ers_group ers_dashboard_widget ref ers_group ( group_id ) group_id Fk FK_ers_filter_join_ers_filter ers_filter_join ref ers_filter ( filter_id ) filter_id Fk FK_ers_filter_condition_ers_filter ers_filter_condition ref ers_filter ( filter_id ) filter_id Fk fk_ers_snapshot_dim_map ers_snapshot_dim_map ref qcc_file ( qcc_file_id ) qcc_file_id Fk fk_ers_snapshot_dim_map_snapshot ers_snapshot_dim_map ref ers_snapshot ( snapshot_id ) snapshot_id Fk fk_ers_snapshot_ers_user ers_snapshot ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk FK_ers_configuration_fields_ers_configuration ers_configuration_fields ref ers_configuration ( config_id ) config_id Fk FK_ers_schedule_ers_user ers_schedule ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk FK_qcc_file_settings_qcc_file_settings qcc_file_settings ref qcc_file ( qcc_file_id ) qcc_file_id Fk FK_dim_relation_map_dim_relation dim_relation_map ref dim_relation ( dim_relation_id ) dim_relation_id Fk FK_qcc_file_history_ers_user qcc_file_history ref ers_user ( user_id ) user_id Fk FK_qcc_file_history_qcc_file qcc_file_history ref qcc_file ( qcc_file_id ) qcc_file_id Fk fk_aql_percent_aql_table aql_percent ref aql_table ( aql_table_id ) aql_table_id Fk fk_aql_lot_aql_table aql_lot ref aql_table ( aql_table_id ) aql_table_id Fk fk_aql_level_aql_percent aql_level ref aql_percent ( aql_percent_id ) aql_percent_id Fk fk_aql_level_aql_lot aql_level ref aql_lot ( aql_lot_id ) aql_lot_id Fk FK_part_type_part_type_group1 qcc_file_model ref qcc_file ( qcc_file_id ) qcc_file_id Fk FK_dim_relation_dim_relation_type dim_relation ref dim_relation_type ( relation_type_id ) relation_type_id Fk FK_dim_relation_dimension dim_relation ref dimension ( dim_id ) dim_id Fk FK_ers_dashboard_widget_fields_ers_dashboard_widget ers_dashboard_widget_fields ref ers_dashboard_widget ( widget_id ) widget_id Fk FK_ers_filter_ers_filter_type ers_filter ref ers_filter_type ( filter_type_id ) filter_type_id Fk fk_ers_part_file_named_list_ers_user ers_part_file_named_list ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk fk_qcc_file_category qcc_file ref category ( category_id ) category_id Fk FK_factor_part_type factor ref qcc_file_model ( qcc_file_model_id ) qcc_file_model_id Fk FK_part_factor_factor part_factor ref factor ( factor_id ) factor_id Fk FK_part_factor_part part_factor ref part ( part_id ) part_id Fk FK_ers_custom_setting_ers_user ers_custom_setting ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk fk_ers_trigger_action ers_trigger_action ref ers_trigger ( trigger_id ) trigger_id Fk fk_ers_trigger_file_bookmark_ers_trigger ers_trigger_file_bookmark ref ers_trigger ( trigger_id ) trigger_id Fk fk_ers_trigger_file_bookmark_qcc_file ers_trigger_file_bookmark ref qcc_file ( qcc_file_id ) qcc_file_id Fk fk_ers_run_history_ers_service ers_run_history ref ers_service ( ers_service_id ) ers_service_id Fk FK_ers_group_ers_user1 ers_group ref ers_user ( created_by -> user_id ) created_by Fk FK_ers_group_ers_user2 ers_group ref ers_user ( last_modified_by -> user_id ) last_modified_by Fk FK_part_factor_history_ers_user part_factor_history ref ers_user ( user_id ) user_id Fk FK_factor_history_factor part_factor_history ref factor ( factor_id ) factor_id Fk FK_factor_history_part part_factor_history ref part ( part_id ) part_id Fk FK_factor_history_reason part_factor_history ref reason ( reason_id ) reason_id Fk FK_measurement_history_dimension measurement_history ref dimension ( dim_id ) dim_id Fk FK_measurement_history_ers_user measurement_history ref ers_user ( user_id ) user_id Fk FK_measurement_history_measurement_history measurement_history ref part ( part_id ) part_id Fk fk_ers_task_recipient_ers_task ers_task_recipient ref ers_task ( task_id ) task_id Fk fk_ers_task_recipient ers_task_recipient ref ers_user_group ( recip_group_id -> user_group_id ) recip_group_id Fk fk_ers_task_recipient_ers_user ers_task_recipient ref ers_user ( recip_user_id -> user_id ) recip_user_id Fk FK_audit_history_ers_user audit_history ref ers_user ( user_id ) user_id Fk FK_dimension_part_type dimension ref qcc_file_model ( qcc_file_model_id ) qcc_file_model_id Fk fk_dimension_aql_percent dimension ref aql_percent ( aql_percent_id ) aql_percent_id Fk Fk_part_qcc_file part ref qcc_file ( qcc_file_id ) qcc_file_id Fk fk_part_source_part part_source ref part ( part_id ) part_id Fk fk_part_source_installation part_source ref installation ( install_id ) install_id Fk FK_rt_monitor_qcc_file rt_monitor ref qcc_file ( current_qcc_file_id -> qcc_file_id ) current_qcc_file_id Fk fk_rt_monitor_installation rt_monitor ref installation ( install_id ) install_id Fk fk_installation_ext installation_ext ref installation ( install_id ) install_id Fk FK_resource_blob resource_association ref resource ( resource_id ) resource_id Fk FK_audit_ers_user audit ref ers_user ( user_id ) user_id Fk fk_ers_configuration ers_configuration ref installation ( install_id ) install_id Fk FK_ers_configuration_ers_user ers_configuration ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk fk_ers_configuration_ers_service ers_configuration ref ers_service ( ers_service_id ) ers_service_id Fk fk_ers_configuration_ers_service_group ers_configuration ref ers_service_group ( ers_service_group_id ) ers_service_group_id Fk FK_ers_configuration_ers_calendar ers_configuration ref ers_calendar ( calendar_id ) calendar_id Fk fk_ers_configuration_production_period ers_configuration ref ers_production_period ( period_id ) period_id Fk fk_ers_full_view ers_full_view ref installation ( install_id ) install_id Fk FK_ers_full_view_ers_user ers_full_view ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk fk_ers_full_view_ers_service ers_full_view ref ers_service ( ers_service_id ) ers_service_id Fk FK_ers_dashboard_ers_filter ers_dashboard ref ers_filter ( title_global_filter_id -> filter_id ) title_global_filter_id Fk FK_ers_dashboard_ers_group ers_dashboard ref ers_group ( title_group_id -> group_id ) title_group_id Fk FK_ers_dashboard_ers_user ers_dashboard ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk fk_ers_dashboard ers_dashboard ref installation ( install_id ) install_id Fk fk_ers_dashboard_ers_service ers_dashboard ref ers_service ( ers_service_id ) ers_service_id Fk fk_ers_dashboard_ers_service_group ers_dashboard ref ers_service_group ( ers_service_group_id ) ers_service_group_id Fk fk_ers_dashboard_ers_calendar ers_dashboard ref ers_calendar ( calendar_id ) calendar_id Fk fk_ers_dashboard_production_period ers_dashboard ref ers_production_period ( period_id ) period_id Fk fk_qc_gage_spec_plan_setting qc_gage_spec_plan_setting ref qc_gage_spec_plan ( spec_plan_id ) spec_plan_id Fk fk_ers_trigger_ers_trigger ers_trigger_qcc_file ref ers_trigger ( trigger_id ) trigger_id Fk fk_ers_trigger_qcc_file ers_trigger_qcc_file ref qcc_file ( qcc_file_id ) qcc_file_id Fk fk_category_category category ref category ( category_id -> parent_id ) category_id Fk fk_ers_external_report ers_external_report ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk FK_ers_full_view_hotspot_trend_ers_full_view_hotspot ers_full_view_hotspot_trend ref ers_full_view_hotspot ( hotspot_id ) hotspot_id Fk FK_ers_full_view_hotspot_fields_ers_full_view_hotspot ers_full_view_hotspot_fields ref ers_full_view_hotspot ( hotspot_id ) hotspot_id Fk FK_ers_full_view_rule_fields_ers_full_view_rule ers_full_view_rule_fields ref ers_full_view_rule ( rule_id ) rule_id Fk FK_ers_full_view_rule_ers_full_view_hotspot ers_full_view_rule ref ers_full_view_hotspot ( hotspot_id ) hotspot_id Fk fk_ers_task_ers_user ers_task ref ers_user ( task_user_id -> user_id ) task_user_id Fk fk_ers_task_ers_user1 ers_task ref ers_user ( complete_user_id -> user_id ) complete_user_id Fk fk_qc_gage_global_gage qc_gage_global_gage ref qc_gage_connection ( connection_id ) connection_id Fk fk_qc_gage_gages qc_gage_gages ref qc_gage_global_gage ( global_gage_id ) global_gage_id Fk FK_measurement_dimension1 measurement ref dimension ( dim_id ) dim_id Fk FK_measurement_current_ers_user measurement ref ers_user ( current_user_id -> user_id ) current_user_id Fk FK_measurement_ers_user measurement ref ers_user ( original_user_id -> user_id ) original_user_id Fk FK_measurement_note measurement ref note ( note_id ) note_id Fk fk_qc_gage_connection_param qc_gage_connection_param ref qc_gage_connection ( connection_id ) connection_id Fk fk_ers_trigger_ers_user ers_trigger ref ers_user ( owner_user_id -> user_id ) owner_user_id Fk fk_ers_trigger_installation ers_trigger ref installation ( install_id ) install_id Fk fk_ers_trigger_ers_service ers_trigger ref ers_service ( ers_service_id ) ers_service_id Fk fk_ers_trigger_group ers_trigger ref ers_service_group ( ers_service_group_id ) ers_service_group_id Fk fk_ers_trigger_ers_calendar ers_trigger ref ers_calendar ( calendar_id ) calendar_id Fk fk_ers_trigger_production_period ers_trigger ref ers_production_period ( period_id ) period_id Fk FK_part_history_ers_user part_history ref ers_user ( user_id ) user_id Fk FK_part_history_part part_history ref part ( part_id ) part_id dim_relation_typeTable dbo.dim_relation_type Pk PK_dim_relation ( relation_type_id ) relation_type_idrelation_type_id * int The primary key. Referred by dim_relation ( relation_type_id ) relation_type_descrelation_type_desc * varchar(50) The dim relation type; scatter, whisker, true position,etc. t userTable dbo.user DEPRECATED in favor of unified ers_user table. Pk PK_user ( user_id ) user_iduser_id * int # user_descuser_desc * nvarchar(100) t measurement_cause_mapTable dbo.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. Pk PK_measurement_cause_map ( part_id, dim_id, cause_id, cause_number ) part_idpart_id * int The part id of the measurement. References measurement ( part_id, dim_id ) Pk PK_measurement_cause_map ( part_id, dim_id, cause_id, cause_number ) dim_iddim_id * int The dim id of the measurement. References measurement ( part_id, dim_id ) Pk PK_measurement_cause_map ( part_id, dim_id, cause_id, cause_number ) cause_idcause_id * int The cause id of the mapped assignable cause. References assignable_cause ( cause_id ) Pk PK_measurement_cause_map ( part_id, dim_id, cause_id, cause_number ) cause_numbercause_number * int The ordinal position of the cause (1st, 2nd, etc). # measurement_action_mapTable dbo.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. Pk PK_measurement_action_map ( part_id, dim_id, action_id, action_number ) part_idpart_id * int The linked part id for the measurement. References measurement ( part_id, dim_id ) Pk PK_measurement_action_map ( part_id, dim_id, action_id, action_number ) dim_iddim_id * int The linked dimension id for the measurement. References measurement ( part_id, dim_id ) Pk PK_measurement_action_map ( part_id, dim_id, action_id, action_number ) action_idaction_id * int The linked corrective action. References corrective_action ( action_id ) Pk PK_measurement_action_map ( part_id, dim_id, action_id, action_number ) action_numberaction_number * int The ordinal position of the action (1st, 2nd). # measurement_cause_historyTable dbo.measurement_cause_history Contains a history of changes to the assignment of assignable causes to measurements. Pk PK_measurement_cause_history ( part_id, dim_id, cause_number, effective_date ) part_idpart_id * int The part id of the linked measurement. References part ( part_id ) Pk PK_measurement_cause_history ( part_id, dim_id, cause_number, effective_date ) dim_iddim_id * int The dimension id of the linked measurement. References dimension ( dim_id ) Pk PK_measurement_cause_history ( part_id, dim_id, cause_number, effective_date ) cause_numbercause_number * int The ordinal position of the assignable cause being changed. # Pk PK_measurement_cause_history ( part_id, dim_id, cause_number, effective_date ) effective_dateeffective_date * datetime The date of the change. d old_cause_idold_cause_id int The old assignable cause in that position. References assignable_cause ( old_cause_id -> cause_id ) new_cause_idnew_cause_id int The new assignable cause in that position. References assignable_cause ( new_cause_id -> cause_id ) user_iduser_id int The user who made the change. References ers_user ( user_id ) reason_idreason_id int The reason for the change. References reason ( reason_id ) edl_load_dateedl_load_date datetime The date EDL uploaded the change. d measurement_action_historyTable dbo.measurement_action_history Contains a history of changes to the assignment of corrective actions to measurements. Pk PK_measurement_action_history\ ( part_id, dim_id, action_number, effective_date ) part_idpart_id * int The part id of the linked measurement. References part ( part_id ) Pk PK_measurement_action_history\ ( part_id, dim_id, action_number, effective_date ) dim_iddim_id * int The dimension id of the linked measurement. References dimension ( dim_id ) Pk PK_measurement_action_history\ ( part_id, dim_id, action_number, effective_date ) action_numberaction_number * int The action number that changed. # Pk PK_measurement_action_history\ ( part_id, dim_id, action_number, effective_date ) effective_dateeffective_date * datetime The date of the change. d old_action_idold_action_id int The old corrective action that was assigned to this position. References corrective_action ( old_action_id -> action_id ) new_action_idnew_action_id int The new corrective action that is assigned to this position. References corrective_action ( new_action_id -> action_id ) user_iduser_id int The user who made the change. References ers_user ( user_id ) reason_idreason_id int The reason for the change. References reason ( reason_id ) edl_load_dateedl_load_date datetime The date EDL loaded the change. d reasonTable dbo.reason Contains reason codes used for 21 CFR Part 11 purposes. Pk PK_reason ( reason_id ) reason_idreason_id * int The primary key of the table. Referred by measurement_action_history ( reason_id ) Referred by measurement_cause_history ( reason_id ) Referred by part_factor_history ( reason_id ) reason_descreason_desc * nvarchar(500) The reason code description. t ers_chart_themeTable dbo.ers_chart_theme Pk PK_ers_chart_theme ( theme_id ) theme_idtheme_id * int The primary key of the table. # theme_desctheme_desc * nvarchar(50) The name of the theme. t theme_xmltheme_xml * ntext The complete xml representation of the theme. t owner_user_idowner_user_id * int The user who owns the resource. References ers_user ( owner_user_id -> user_id ) ers_filter_fileTable dbo.ers_filter_file Maps dimension filters to QCQ files to prevent dimension filters from querying every file in a particular directory. Pk PK_ers_filter_file ( filter_file_id ) filter_file_idfilter_file_id * int The primary key of the table. # filter_idfilter_id * int # file_pathfile_path * nvarchar(500) t ers_user_settingTable dbo.ers_user_setting A generic data defined table that contains user specific settings for ERS and QC-Mobile. Pk PK_user_setting ( setting_id ) setting_idsetting_id * int The primary key of the table. # user_iduser_id * int The user linked to the setting. References ers_user ( user_id ) setting_namesetting_name * varchar(50) The name of the setting. t setting_valuesetting_value text The value of the setting. t ers_stats_dataTable dbo.ers_stats_data DEPRECATED Pk PK_stats_data ( calc_id ) calc_idcalc_id * int # effective_dateeffective_date datetime d config_idconfig_id int # group_idgroup_id int # qcc_file_idqcc_file_id int # dim_iddim_id int # filter_idfilter_id int # calculationcalculation nvarchar(20) t valuevalue float # ers_user_group_mapTable dbo.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. Pk PK_ers_user_group_map ( user_group_id, user_id ) user_group_iduser_group_id * int The linked user group id. References ers_user_group ( user_group_id ) Pk PK_ers_user_group_map ( user_group_id, user_id ) user_iduser_id * int The linked user id. References ers_user ( user_id ) ers_user_group_file_mapTable dbo.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. Pk PK_ers_user_group_file_map ( user_group_id, qcc_file_id ) user_group_iduser_group_id * int The linked user group id. References ers_user_group ( user_group_id ) Pk PK_ers_user_group_file_map ( user_group_id, qcc_file_id ) qcc_file_idqcc_file_id * int The qcc file in question. References qcc_file ( qcc_file_id ) allow_flagallow_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. b permission_levelpermission_level * int default 0 Future expansion. # ers_user_groupTable dbo.ers_user_group This table represents user groups in the system. These are used for file permission and resource sharing. Pk PK_ers_user_group ( user_group_id ) user_group_iduser_group_id * int The primary key of the table. Referred by ers_task_recipient ( recip_group_id -> user_group_id ) Referred by ers_user_group_file_map ( user_group_id ) Referred by ers_user_group_map ( user_group_id ) Referred by ers_user_group_setting ( user_group_id ) Referred by ers_user_group_view ( user_group_id ) user_group_descuser_group_desc * nvarchar(50) The name of the user group. t edl_descedl_desc nvarchar(200) The EDL description for the group. Users within the group have dynamic access to all QCC files with this EDL desc. t permission_levelpermission_level * int default 0 Future expansion. # ers_user_group_viewTable dbo.ers_user_group_view Table that contains resources that are viewed/shared at the group level. Pk PK_ers_group_view ( group_view_id ) group_view_idgroup_view_id * int The primary key of the table. # user_group_iduser_group_id * int The linked user group. References ers_user_group ( user_group_id ) object_typeobject_type * int The resource type (report, filter, etc) # table_pktable_pk * int The id of the linked resource. # ers_user_group_settingTable dbo.ers_user_group_setting Generic data defined table containing user group settings. Pk PK_ers_group_setting ( group_setting_id ) group_setting_idgroup_setting_id * int The primary key of the table. # user_group_iduser_group_id * int The linked user group. References ers_user_group ( user_group_id ) setting_namesetting_name * nvarchar(50) The setting name. t setting_valuesetting_value nvarchar(max) The setting's value. t ers_selected_dimensionTable dbo.ers_selected_dimension Holds selected dimensions in a data group (if group is using files and dimensions). Pk PK_ers_selected_dimension ( group_id, qcc_file_id, dim_position ) group_idgroup_id * int The data group id. References ers_selected_qcc_file ( group_id, qcc_file_id ) Pk PK_ers_selected_dimension ( group_id, qcc_file_id, dim_position ) qcc_file_idqcc_file_id * int The qcc file id of the linked file. References ers_selected_qcc_file ( group_id, qcc_file_id ) Pk PK_ers_selected_dimension ( group_id, qcc_file_id, dim_position ) dim_positiondim_position * int The ordinal (nth) dimension position (not id). This allows us to look across models. # dim_orderdim_order * int DEPRECATED. # usedused * 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. # specialspecial * smallint DEPRECATED. # ers_displayed_filtersTable dbo.ers_displayed_filters Represents the record filters present in a data group. Pk PK_ers_displayed_filters ( group_id, filter_id ) group_idgroup_id * int The data group to which this filter is linked. References ers_group ( group_id ) Pk PK_ers_displayed_filters ( group_id, filter_id ) filter_idfilter_id * int The filter that is linked. References ers_filter ( filter_id ) filter_positionfilter_position * int The position (order) of the filter in the group. # ers_selected_qcc_fileTable dbo.ers_selected_qcc_file Contains qcc files for the data group (if group is using files and dimensions). Pk PK_ers_selected_qcc_file ( group_id, qcc_file_id ) group_idgroup_id * int The data group id. References ers_group ( group_id ) Referred by ers_selected_dimension ( group_id, qcc_file_id ) Pk PK_ers_selected_qcc_file ( group_id, qcc_file_id ) qcc_file_idqcc_file_id * int The qcc file id of the linked file. References qcc_file ( qcc_file_id ) Referred by ers_selected_dimension ( group_id, qcc_file_id ) qcc_file_orderqcc_file_order * int The ordinal position of the file in the list. # ers_mapped_groupTable dbo.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. Pk PK_ers_mapped_group ( config_id, group_id ) config_idconfig_id * int The id of the report/export job. References ers_configuration ( config_id ) Pk PK_ers_mapped_group ( config_id, group_id ) group_idgroup_id * int The id of the datagroup. References ers_group ( group_id ) group_positiongroup_position * int The ordinal position of the group in the report/export. # ers_viewTable dbo.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. Pk PK_ers_view ( view_id ) view_idview_id * int The primary key of the table. # user_iduser_id * int The user who is viewing the resource. References ers_user ( user_id ) object_typeobject_type * int The type of resource (report, export, filter, dashboard, etc). # table_pktable_pk * int The id of the resource. # ers_dashboard_widgetTable dbo.ers_dashboard_widget This table represents a specific widget on the dashboard. Pk PK_dashboard_widget ( widget_id ) widget_idwidget_id * int The primary key of the table Referred by ers_dashboard_widget_fields ( widget_id ) dashboard_iddashboard_id * int The dashboard to which this widget is linked. References ers_dashboard ( dashboard_id ) widget_typewidget_type * nvarchar(50) The type of widget (text, radial gauge, andon light). t position_rowposition_row * int The one-based row position of the widget. # position_colposition_col * int The one-based column position of the widget. # group_idgroup_id int The data group to use to calculate the value of the widget. References ers_group ( group_id ) global_filter_idglobal_filter_id int The global filter to use to calculate the value of the widget. References ers_filter ( global_filter_id -> filter_id ) label_overridelabel_override nvarchar(100) The dynamic label just above the widget. t no_data_show_flagno_data_show_flag * bit default 1 A flag indicating whether you should show the widget if there is no data. b record_governorrecord_governor int A record governor allowing you limit the number of records returned to the latest x from any query. # ers_filter_joinTable dbo.ers_filter_join This table represents the table joins in a filter. Pk PK_ers_filter_joins ( join_id ) join_idjoin_id * int The primary key of the table. # filter_idfilter_id * int The filter id to which this join is linked. References ers_filter ( filter_id ) join_descjoin_desc * varchar(100) The name of the join. t condition_related_flagcondition_related_flag * bit default 0 b table_lefttable_left * varchar(100) The table on the left side of the join. t table_left_aliastable_left_alias varchar(100) The generated table alias for the left table. t join_stylejoin_style * varchar(50) The join style (inner, left outer). t table_righttable_right * varchar(100) The table on the right side of the join. t table_right_aliastable_right_alias varchar(100) The generated table alias for the right table. t table_left_keystable_left_keys * varchar(200) The list of keys for the left table. t table_right_keystable_right_keys * varchar(200) The list of keys for the right table. t corrective_actionTable dbo.corrective_action Pk PK_corrective_action ( action_id ) action_idaction_id * int The primary key of the corrective action table. Referred by measurement_action_history ( old_action_id -> action_id ) Referred by measurement_action_history ( new_action_id -> action_id ) Referred by measurement_action_map ( action_id ) action_refaction_ref nvarchar(100) The short description or reference for the corrective action. t action_descaction_desc * nvarchar(500) The full description of the corrective action. t ers_filter_conditionTable dbo.ers_filter_condition Represents the WHERE clause of filters in ERS. Pk PK_ers_filter_condition ( filter_condition_id ) filter_condition_idfilter_condition_id * int The primary key of the table. # filter_idfilter_id * int The filter to which this condition is linked. References ers_filter ( filter_id ) logical_operatorlogical_operator * varchar(10) The logical operator (AND, OR). t filter_fieldfilter_field * nvarchar(4000) The field on which to filter. t left_parenleft_paren varchar(10) Text field representing a set of left parentheses. t compare_operatorcompare_operator * varchar(100) The operator (greater than, equal to, etc). t criteriacriteria * nvarchar(4000) The criteria value being compared. t right_parenright_paren varchar(10) Text field representing a set of right parentheses. t extra_infoextra_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. t ers_snapshot_dim_mapTable dbo.ers_snapshot_dim_map This maps particular dimensions from part files into the snapshot with coordinates. Pk _0 ( qcc_file_id, unique_dim_number, snapshot_id ) Idx_ers_snapshot_dim_map_qcc_file_id ( qcc_file_id ) qcc_file_idqcc_file_id * int A foreign key to the part file associated with the characteristic. References qcc_file ( qcc_file_id ) Pk _0 ( qcc_file_id, unique_dim_number, snapshot_id ) unique_dim_numberunique_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. # Pk _0 ( qcc_file_id, unique_dim_number, snapshot_id ) Idx_ers_snapshot_dim_map_snapshot_id ( snapshot_id ) snapshot_idsnapshot_id * int A foreign key reference to the ers_snapshot table declaring onto which snapshot the characteristic should be placed. References ers_snapshot ( snapshot_id ) coordinatescoordinates 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. t ers_snapshotTable dbo.ers_snapshot This holds snapshots of 2D/3D drawings taken in ERS. Pk Pk_ers_snapshot_snapshot_id ( snapshot_id ) snapshot_idsnapshot_id * int The auto-incrementing primary key of the table. Referred by ers_snapshot_dim_map ( snapshot_id ) snapshot_descsnapshot_desc varchar(100) The friendly description given to the snapshot. t Idx_ers_snapshot_owner_user_id ( owner_user_id ) owner_user_idowner_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. References ers_user ( owner_user_id -> user_id ) Idx_ers_snapshot_drawing_resource_id ( drawing_resource_id ) drawing_resource_iddrawing_resource_id int This foreign key maps back to the original drawing from which the snapshot was taken. References resource ( drawing_resource_id -> resource_id ) ers_aggregated_exportTable dbo.ers_aggregated_export aggregated_export_idaggregated_export_id * int The primary key of the table. # effective_dateeffective_date * datetime The effective or "as of" date of the calculation being saved. d configuration_idconfiguration_id int The export config id linked to this statistical value. # group_idgroup_id int The data group id that was used in the calculation. # qcc_file_idqcc_file_id int The qcc file that was used in the calculation. # dim_iddim_id int The dimension id that was used in the calculation. # global_filter_idglobal_filter_id int The global filter that was used in the calculation. # loop_filter_idloop_filter_id int The cycling filter in the data group that was used. # calculationcalculation nvarchar(50) The name of the statistical calculation (i.e. Cpk, Sigma). t valuevalue float The value of the statistical calculation. # dim_labeldim_label nvarchar(200) t ers_configuration_fieldsTable dbo.ers_configuration_fields A generic data defined table of fields that are specific to each report. Pk PK_ers_configuration_fields ( field_id ) field_idfield_id * int The primary key of the table. # config_idconfig_id * int The report/export to which this setting is linked. References ers_configuration ( config_id ) field_namefield_name * nvarchar(100) The name of the field. t field_valuefield_value nvarchar(max) The value of the field. t field_blobfield_blob image The binary value of the field if the field stores binary data. ~ ers_analysis_historyTable dbo.ers_analysis_history Pk PK_ers_analysis_history ( analysis_id ) analysis_idanalysis_id * int The primary key of the table. # date_createddate_created * datetime The date the analysis was run. d user_iduser_id * int The user who ran the analysis. # descdesc nvarchar(100) The name of the analysis that was run. t xml_serialized_objectxml_serialized_object text The result of the analysis. t assignable_causeTable dbo.assignable_cause Pk PK_assignable_cause ( cause_id ) cause_idcause_id * int The primary key of the table. Referred by measurement_cause_history ( old_cause_id -> cause_id ) Referred by measurement_cause_history ( new_cause_id -> cause_id ) Referred by measurement_cause_map ( cause_id ) cause_refcause_ref nvarchar(100) The short description or reference of the assignable cause. t cause_desccause_desc * nvarchar(500) The full description of the assignable cause. t ers_scheduleTable dbo.ers_schedule Not used. recur_idrecur_id * int # config_descconfig_desc nvarchar(100) t enabled_flagenabled_flag bit b next_rundatenext_rundate datetime d owner_user_idowner_user_id int References ers_user ( owner_user_id -> user_id ) recur_date_to_runrecur_date_to_run datetime d recur_days_of_weekrecur_days_of_week int # recur_intervalrecur_interval int # recur_time_to_runrecur_time_to_run datetime d recur_typerecur_type int # qcc_file_settingsTable dbo.qcc_file_settings Generic data defined table that contains most of the custom settings from QCC files. Pk PK_qcc_file_settings ( setting_id ) Primary key of the table. setting_idsetting_id * int The primary key of the table. # FK_qcc_file_cat_name ( qcc_file_id, category, name ) Index on foreign key as well as category and name. qcc_file_idqcc_file_id * int The linked QCC file. References qcc_file ( qcc_file_id ) FK_qcc_file_cat_name ( qcc_file_id, category, name ) Index on foreign key as well as category and name. namename varchar(100) The name of the setting. t valuevalue nvarchar(max) The value of the setting. t FK_qcc_file_cat_name ( qcc_file_id, category, name ) Index on foreign key as well as category and name. categorycategory varchar(100) The category or type of setting if specified. t data_typedata_type * nvarchar(20) The data type of the setting (integer, text, etc). t identificationidentification nvarchar(100) t dim_relation_mapTable dbo.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. Pk PK_dim_relation_map ( relation_map_id ) The primary key of the table. relation_map_idrelation_map_id * int The primary key of the table. # FK_dim_relation ( dim_relation_id ) Index on the foreign key to the dim_relation table for joining purposes. dim_relation_iddim_relation_id * int FK back to the dim_relation table. This table defines the child dimension in the overall relationship. References dim_relation ( dim_relation_id ) map_dim_idmap_dim_id * int The child dimension. # map_dim_descmap_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. t extra_infoextra_info varchar(255) Any extra information that needs to be provided. t noteTable dbo.note Contains user notes for QC-CALC. Mapped directly to measurements. Pk PK_measurement_note ( note_id ) note_idnote_id * int The primary key of the table. Referred by measurement ( note_id ) note_descnote_desc nvarchar(4000) The note itself. t qcc_file_historyTable dbo.qcc_file_history Contains historical changes at the QCC file level. Pk PK_qcc_file_history ( qcc_file_history_id ) qcc_file_history_idqcc_file_history_id * int The primary key of the table. # qcc_file_idqcc_file_id * int The linked QCC file. References qcc_file ( qcc_file_id ) effective_dateeffective_date * datetime The date of the change. d action_descaction_desc * nvarchar(500) The change itself (typically part 11 actions). t user_iduser_id int The user who made the change. References ers_user ( user_id ) reason_idreason_id int The reason code of the change. # edl_load_dateedl_load_date datetime The date it was loaded by EDL. d aql_percentTable dbo.aql_percent This table defines the AQL percentages (columns) of a 2D AQL matrix table. Pk Pk_aql_percent_aql_percent_id ( aql_percent_id ) aql_percent_idaql_percent_id * int The auto-counting primary key of the table. Referred by aql_level ( aql_percent_id ) Referred by dimension ( aql_percent_id ) Idx_aql_percent_aql_table_id ( aql_table_id ) aql_table_idaql_table_id * int Foreign key reference back to the AQL table to which this percentage belongs. References aql_table ( aql_table_id ) aql_percentaql_percent * float The actual percentage of the AQL column in decimal form. # aql_lotTable dbo.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. Pk Pk_aql_lot_aql_lot_id ( aql_lot_id ) aql_lot_idaql_lot_id * int The auto-counting primary key of the table. Referred by aql_level ( aql_lot_id ) Idx_aql_lot_aql_table_id ( aql_table_id ) aql_table_idaql_table_id * int The foreign key reference back to the AQL table to which this lot belongs. References aql_table ( aql_table_id ) lot_fromlot_from * int The lower FROM value of the lot. For instance, if the lot is 5-10, the FROM value is 5. # lot_tolot_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. # aql_levelTable dbo.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. Pk aql_level_primary_key ( aql_percent_id, aql_lot_id ) Idx_aql_level_aql_percent_id ( aql_percent_id ) aql_percent_idaql_percent_id * int Foreign key reference back to aql_percent table. References aql_percent ( aql_percent_id ) Pk aql_level_primary_key ( aql_percent_id, aql_lot_id ) Idx_aql_level_aql_lot_id ( aql_lot_id ) aql_lot_idaql_lot_id * int Foreign key reference back to aql_lot table. References aql_lot ( aql_lot_id ) valuevalue * 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. # qcc_file_modelTable dbo.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. Pk PK_qcc_file_model ( qcc_file_model_id ) Primary key of the table. qcc_file_model_idqcc_file_model_id * int The primary key of the table. Referred by dimension ( qcc_file_model_id ) Referred by factor ( qcc_file_model_id ) FK_qcc_file ( qcc_file_id ) Index on foreign key to qcc_file for joining purposes. qcc_file_idqcc_file_id * int The id of the linked QCC file. References qcc_file ( qcc_file_id ) effective_dateeffective_date * datetime The date the model was created (and presumably when the file changed). d sub_groupsub_group int The sub group size of the model. # dim_relationTable dbo.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). Pk PK_dim_relation_1 ( dim_relation_id ) The primary key of the table. dim_relation_iddim_relation_id * int The primary key of the table. Referred by dim_relation_map ( dim_relation_id ) FK_dim ( dim_id ) Index on foreign key to dimension table. dim_iddim_id * int The main dimension that defines the relationship. The mapped dimensions are the child dimensions in the relationship. References dimension ( dim_id ) FK_dim_relation_type ( relation_type_id ) Index on foreign key to dim_relation_type table. relation_type_idrelation_type_id * int The type of relationship that is being defined. FK to dim_relation_type. References dim_relation_type ( relation_type_id ) dim_relation_descdim_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. t ers_service_groupTable dbo.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. Pk Pk_ers_service_group_ers_service_group_id ( ers_service_group_id ) ers_service_group_iders_service_group_id * int The primary key for this table. Referred by ers_configuration ( ers_service_group_id ) Referred by ers_dashboard ( ers_service_group_id ) Referred by ers_full_view ( ers_service_group_id ) Referred by ers_service ( ers_service_group_id ) Referred by ers_trigger ( ers_service_group_id ) namename varchar(100) The group name t ers_dashboard_widget_fieldsTable dbo.ers_dashboard_widget_fields This table is a generic data defined table of field values for dashboard widgets. Pk PK_ers_dashboard_widget_fields ( field_id ) field_idfield_id * int The primary key of the table. # widget_idwidget_id * int The widget to which this field is linked. References ers_dashboard_widget ( widget_id ) field_namefield_name * nvarchar(100) The name of the field. t field_valuefield_value * nvarchar(1000) The value of the field. t field_blobfield_blob image The binary value of the field such as an image (if the field is a binary type). ~ ers_filterTable dbo.ers_filter This table represents both dimension and record filters. Pk PK_ers_filter ( filter_id ) filter_idfilter_id * int The primary key of the table. Referred by ers_dashboard ( title_global_filter_id -> filter_id ) Referred by ers_dashboard_widget ( global_filter_id -> filter_id ) Referred by ers_displayed_filters ( filter_id ) Referred by ers_filter_condition ( filter_id ) Referred by ers_filter_join ( filter_id ) Referred by ers_trigger ( dim_filter_id -> filter_id ) filter_type_idfilter_type_id int DEPRECATED References ers_filter_type ( filter_type_id ) filter_namefilter_name * nchar(100) The name of the filter. c filter_sqlfilter_sql * nchar(2000) The SQL generated by the filter. c edl_flagedl_flag * bit A flag indicating whether or not an edl description should be used. b model_optionmodel_option * int An indicator indicating the model option (latest model or query across models). # filter_formatfilter_format nvarchar(100) A custom format field allowing an override to the filter name in reports, etc. t qcc_file_idqcc_file_id int The qcc file linked (if one is linked). Used for querying specific features and factors. References qcc_file ( qcc_file_id ) dim_flagdim_flag * bit default 0 b owner_user_idowner_user_id * int The owner of the filter. References ers_user ( owner_user_id -> user_id ) dim_filter_flagdim_filter_flag * bit default 0 Flag indicating whether this is a dimension filter or record filter. True for dim filter. b model_combine_flagmodel_combine_flag * bit default 1 Flag indicating whether or not we should combine like dimensions across models. b archive_optionarchive_option * int default 1 Indicator that determines if filter should query part files that are archived, unarchived or both. # part_files_dim_filter_xmlpart_files_dim_filter_xml text t ers_filter_typeTable dbo.ers_filter_type Pk PK_ers_filter_type ( filter_type_id ) filter_type_idfilter_type_id * int Referred by ers_filter ( filter_type_id ) filter_type_namefilter_type_name nchar(20) c ers_custom_factor_listTable dbo.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. Pk PK_ers_custom_factor_list ( list_id ) list_idlist_id * int The primary key of the table. # list_typelist_type * nvarchar(50) The type of list (text or numeric). t list_desclist_desc * nvarchar(50) The name of the list. t list_variationlist_variation * nvarchar(4000) The variation of the names. A comma separated list of values. t ers_userTable dbo.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. Pk PK_ers_user ( user_id ) user_iduser_id * int The primary key of the table. Referred by audit ( user_id ) Referred by audit_history ( user_id ) Referred by ers_chart_theme ( owner_user_id -> user_id ) Referred by ers_configuration ( owner_user_id -> user_id ) Referred by ers_custom_setting ( owner_user_id -> user_id ) Referred by ers_dashboard ( owner_user_id -> user_id ) Referred by ers_external_report ( owner_user_id -> user_id ) Referred by ers_filter ( owner_user_id -> user_id ) Referred by ers_full_view ( owner_user_id -> user_id ) Referred by ers_group ( created_by -> user_id ) Referred by ers_group ( last_modified_by -> user_id ) Referred by ers_part_file_named_list ( owner_user_id -> user_id ) Referred by ers_schedule ( owner_user_id -> user_id ) Referred by ers_snapshot ( owner_user_id -> user_id ) Referred by ers_task ( task_user_id -> user_id ) Referred by ers_task ( complete_user_id -> user_id ) Referred by ers_task_recipient ( recip_user_id -> user_id ) Referred by ers_trigger ( owner_user_id -> user_id ) Referred by ers_user_group_map ( user_id ) Referred by ers_user_setting ( user_id ) Referred by ers_view ( user_id ) Referred by measurement ( current_user_id -> user_id ) Referred by measurement ( original_user_id -> user_id ) Referred by measurement_action_history ( user_id ) Referred by measurement_cause_history ( user_id ) Referred by measurement_history ( user_id ) Referred by part_factor_history ( user_id ) Referred by part_history ( user_id ) Referred by qcc_file_history ( user_id ) user_nameuser_name * nvarchar(100) The user name specified. t user_loginuser_login nchar(1000) The password. c enabled_flagenabled_flag * bit default 1 Flag indicating whether or not this user is enabled. Encrypted. b password_expirepassword_expire datetime A date/time indicating when the password will expire. d password_change_flagpassword_change_flag * bit default 0 A flag indicating that the password must be changed on next login. b signature_picsignature_pic image A binary image of the user's signature. ~ password_historypassword_history nchar(1000) A list of the last x passwords (encrypted). c sso_flagsso_flag * bit default 0 Flag indicating whether or not we are using single-sign on. b qcm_loginqcm_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. t email_addressemail_address nvarchar(255) t ers_part_file_named_listTable dbo.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 Pk Pk_ers_part_file_named_list_list_id ( list_id ) list_idlist_id * int Referred by ers_group ( named_list_id -> list_id ) Referred by ers_trigger ( part_file_list_id -> list_id ) Idx_ers_part_file_named_list_owner_user_id ( owner_user_id ) owner_user_idowner_user_id int References ers_user ( owner_user_id -> user_id ) settings_xmlsettings_xml nvarchar(max) t qcc_fileTable dbo.qcc_file This represents a part file in the database at the highest level. Pk PK_qcc_file ( qcc_file_id ) Primary Key of table. qcc_file_idqcc_file_id * int The primary key of the table. Referred by ers_filter ( qcc_file_id ) Referred by ers_selected_qcc_file ( qcc_file_id ) Referred by ers_snapshot_dim_map ( qcc_file_id ) Referred by ers_trigger_file_bookmark ( qcc_file_id ) Referred by ers_trigger_qcc_file ( qcc_file_id ) Referred by ers_user_group_file_map ( qcc_file_id ) Referred by part ( qcc_file_id ) Referred by qcc_file_history ( qcc_file_id ) Referred by qcc_file_model ( qcc_file_id ) Referred by qcc_file_settings ( qcc_file_id ) Referred by rt_monitor ( current_qcc_file_id -> qcc_file_id ) IX_desc_archive_date ( 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. qcc_file_descqcc_file_desc * nvarchar(100) The name of the QCC file. t IX_desc_archive_date ( 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. creation_datecreation_date datetime The internal creation date of the part file. d IX_cat_location_archive ( category_id, edl_desc, archive_ind ) Index allowing searching on category, location, and archive indicator or just category alone.IX_location_cat_archive ( edl_desc, category_id, archive_ind ) Index allowing searhing by location and category or just location alone. edl_descedl_desc nvarchar(100) The EDL description that helps to ID the part file as from a particular location. t IX_cat_location_archive ( category_id, edl_desc, archive_ind ) Index allowing searching on category, location, and archive indicator or just category alone.IX_desc_archive_date ( 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 ( edl_desc, category_id, archive_ind ) Index allowing searhing by location and category or just location alone. archive_indarchive_ind * int default 0 A tristate indicator indicating whether the file is unarchived (0), archived (1), or overridden to be unarchived (2). # qcc_file_aliasqcc_file_alias nvarchar(255) t last_edit_datelast_edit_date datetime The date/time of the last edit to the QCC file. d qcc_file_pathqcc_file_path nvarchar(8000) The path to the QCQ file. Used by global.dat. t IX_cat_location_archive ( category_id, edl_desc, archive_ind ) Index allowing searching on category, location, and archive indicator or just category alone.IX_location_cat_archive ( edl_desc, category_id, archive_ind ) Index allowing searhing by location and category or just location alone. category_idcategory_id int The foreign key to the category table. References category ( category_id ) file_typefile_type * int default 0 This will hold the file type (normal, GRR, or tryout) as an integer. # factorTable dbo.factor Contains the definitions of factors at the model level. Does not contain factor values themselves. Pk PK_factor ( factor_id ) Primary key of the table. factor_idfactor_id * int The primary key of the table. Referred by part_factor ( factor_id ) Referred by part_factor_history ( factor_id ) IX_type_desc_model ( 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 ( 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 ( qcc_file_model_id ) Foreign key on the model id used for joining to qcc_file_model qcc_file_model_idqcc_file_model_id * int The id of the linked model. References qcc_file_model ( qcc_file_model_id ) IX_type_number_model ( 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. factor_numberfactor_number * int The ordinal position of the factor. # IX_type_desc_model ( 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. factor_descfactor_desc * nvarchar(100) The name of the factor. t IX_type_desc_model ( 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 ( 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. factor_typefactor_type * varchar(50) The factor type (numeric, text, date). t precisionprecision int default Null The number of places to the right of the decimal point for Numeric Factors. # historical_plot_flaghistorical_plot_flag * bit default 1 True when column is visible in SPC and Buddy grid view b aql_tableTable dbo.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. Pk Pk_aql_table_aql_table_id ( aql_table_id ) aql_table_idaql_table_id * int Autocounting primary key of the table. Referred by aql_lot ( aql_table_id ) Referred by aql_percent ( aql_table_id ) aql_table_descaql_table_desc * nvarchar(200) The name of the AQL table. t edit_flagedit_flag * bit default 1 Specifies whether or not users can edit the values of the table. b default_tabledefault_table * bit determines the default table b part_factorTable dbo.part_factor Contains actual factor values. Pk PK_part_factor ( part_id, factor_id ) The primary key on the table.IX_part_factor_value ( 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 ( part_id, factor_id, value_numeric ) Index on part, factor, and value_numeric fields for joining and querying by value_numeric (numeric trace fields). part_idpart_id * int The part id linking back to the part. References part ( part_id ) Pk PK_part_factor ( part_id, factor_id ) The primary key on the table.IX_part_factor_value ( 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 ( part_id, factor_id, value_numeric ) Index on part, factor, and value_numeric fields for joining and querying by value_numeric (numeric trace fields). factor_idfactor_id * int The factor id linking back to the factor definition. References factor ( factor_id ) IX_part_factor_value ( part_id, factor_id, value ) Index on part, factor, and value fields for joining and querying by value (text trace fields). valuevalue nvarchar(100) The value if the factor is a text factor. t IX_part_factor_value_numeric ( part_id, factor_id, value_numeric ) Index on part, factor, and value_numeric fields for joining and querying by value_numeric (numeric trace fields). value_numericvalue_numeric float The value is the factor is a numeric factor. # value_datetimevalue_datetime datetime The value if the factor is a date factor. d original_user_idoriginal_user_id int # current_user_idcurrent_user_id int # ers_custom_settingTable dbo.ers_custom_setting Pk PK_ers_configuration_setting ( field_id ) field_idfield_id * int # field_typefield_type * nvarchar(100) t field_namefield_name * nvarchar(100) t field_valuefield_value nvarchar(max) t owner_user_idowner_user_id * int References ers_user ( owner_user_id -> user_id ) ers_trigger_actionTable dbo.ers_trigger_action This table contains action details for triggers. Pk Pk_ers_trigger_action_trigger_action_id ( trigger_action_id ) trigger_action_idtrigger_action_id * int The primary key. # Idx_ers_trigger_action_trigger_id ( trigger_id ) trigger_idtrigger_id * int The foreign key to the trigger table. References ers_trigger ( trigger_id ) positionposition * int This number indicates the order in which ERS will execute the action. # action_typeaction_type * int The number representing the action type. # action_paramsaction_params nvarchar(max) This field contains the servialized object with actions' details. t action_descaction_desc nvarchar(100) A user' s friendly description of an action. t ers_trigger_file_bookmarkTable dbo.ers_trigger_file_bookmark This table hold pointers to track processed records used in t riggers. Pk pk_ers_trigger_file ( trigger_id, qcc_file_id ) Idx_ers_trigger_file_bookmark_trigger_id ( trigger_id ) trigger_idtrigger_id * int This table hold pointers to track processed records used in t riggers References ers_trigger ( trigger_id ) Pk pk_ers_trigger_file ( trigger_id, qcc_file_id ) Idx_ers_trigger_file_bookmark_qcc_file_id ( qcc_file_id ) qcc_file_idqcc_file_id * int The id of the part file. References qcc_file ( qcc_file_id ) num_of_recordsnum_of_records * int The largest processed unique record number. # ers_run_historyTable dbo.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. Pk PK_ers_run_history ( history_id ) history_idhistory_id * int The primary key of the table. # config_idconfig_id int The id of the resource (report, export, dashboard or full view). # config_descconfig_desc * nvarchar(50) The name of the resource. t config_typeconfig_type * int The config type (export or report). Only used in ERS. # run_daterun_date * datetime The date/time of the run. d automaticautomatic bit Always true. No longer used. b owner_user_idowner_user_id * int default 0 The owner of the resource. # resource_typeresource_type int The type of resource (dashboard, export, etc). # resource_blobresource_blob image A binary representation of the resource (PDF for report, PNG for dashboard). ~ start_datestart_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. d resource_noteresource_note varchar(100) Allows us to add information about the run. This is not yet used but is reserved for future expansion. t Idx_ers_run_history_install_id ( install_id ) install_idinstall_id int References installation ( install_id ) Idx_ers_run_history_ers_service_id ( ers_service_id ) ers_service_iders_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. References ers_service ( ers_service_id ) ers_report_fieldsTable dbo.ers_report_fields This table houses the current report settings for manual reports. Pk PK_ers_report_fields ( field_id ) field_idfield_id * int The primary key of the table. # report_typereport_type * nvarchar(50) The type of report (histogram, etc). t field_namefield_name * nvarchar(100) The name of the field. t field_valuefield_value * nvarchar(500) The setting's value. t ers_serviceTable dbo.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. Pk Pk_ers_service_ers_service_id ( ers_service_id ) ers_service_iders_service_id * int Referred by ers_configuration ( ers_service_id ) Referred by ers_dashboard ( ers_service_id ) Referred by ers_full_view ( ers_service_id ) Referred by ers_run_history ( ers_service_id ) Referred by ers_trigger ( ers_service_id ) Idx_ers_service_install_id ( install_id ) install_idinstall_id * int References installation ( install_id ) friendly_namefriendly_name nvarchar(100) t is_sharedis_shared * bit b alive_freq_in_secalive_freq_in_sec int default 60 # alive_check_date_utcalive_check_date_utc date d Idx_ers_service_ers_service_group_id ( ers_service_group_id ) ers_service_group_iders_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. References ers_service_group ( ers_service_group_id ) process_rate_in_secprocess_rate_in_sec int Indicates how often the service shoudl check if it has anything to process. # service_nameservice_name nvarchar(100) The name of the service in Windows as created from within ERS. This is the unique service name. t lic_infolic_info nvarchar(4000) Internal usage of license information for the particular service. t ers_groupTable dbo.ers_group Pk PK_ers_group ( group_id ) group_idgroup_id * int The primary key of the table. Referred by ers_dashboard ( title_group_id -> group_id ) Referred by ers_dashboard_widget ( group_id ) Referred by ers_displayed_filters ( group_id ) Referred by ers_mapped_group ( group_id ) Referred by ers_selected_qcc_file ( group_id ) group_namegroup_name nvarchar(50) The name of the group. t created_bycreated_by int The owner of the data group. References ers_user ( created_by -> user_id ) creation_datecreation_date datetime The creation date of the group. d last_modified_bylast_modified_by int DEPRECATED References ers_user ( last_modified_by -> user_id ) last_modification_datelast_modification_date datetime Date/time of the last modification. d dim_filter_iddim_filter_id int The dim filter id if the dimensions are driven by dim filter. # combine_dims_flagcombine_dims_flag * bit default 0 Flag indicating whether to combine like dimensions across models (when files and dimensions are used in group). b use_latest_qcc_filesuse_latest_qcc_files * bit default 0 DEPRECATED b dim_optiondim_option * int default 0 The dimension handling option (stack, subgroup across dimensions) for groups that use files and dimensions. # model_optionmodel_option int The model option (use latest, query across models, etc) for groups that use files and dimensions. # part_files_dim_filter_xmlpart_files_dim_filter_xml text t override_part_filesoverride_part_files bit b Idx_ers_group_category_id ( category_id ) category_idcategory_id int References category ( category_id ) Idx_ers_group_named_list_id ( named_list_id ) named_list_idnamed_list_id int A foreign key to the ers_part_file_named_list. References ers_part_file_named_list ( named_list_id -> list_id ) part_factor_historyTable dbo.part_factor_history Contains a history of the changes to factor values. Pk PK_factor_history ( history_id ) history_idhistory_id * int The auto-numbering primary key for the table. # part_idpart_id * int The part id of the factor value. References part ( part_id ) factor_idfactor_id * int The factor id of the factor value. References factor ( factor_id ) effective_dateeffective_date * datetime The date of the change. d field_changedfield_changed * varchar(50) The field that change (value). t old_valueold_value nvarchar(100) The old value of the field. t new_valuenew_value * nvarchar(100) The new value of the field. t user_iduser_id int The user who made the change. References ers_user ( user_id ) reason_idreason_id int The reason for the change. References reason ( reason_id ) edl_load_dateedl_load_date datetime The date EDL loaded the change. d source_indsource_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. # measurement_historyTable dbo.measurement_history Contains a historical record of changes to the measurements. Pk PK_measurement_history ( history_id ) history_idhistory_id * int The autonumbering primary key of the table. # IX_measurement_history_part_id ( part_id ) part_idpart_id * int The part id of the measurement. References part ( part_id ) IX_measurement_history_dim_id ( dim_id ) dim_iddim_id * int The dimension id of the measurement. References dimension ( dim_id ) effective_dateeffective_date * datetime The effective date of the change. d field_changedfield_changed * varchar(50) The field that changed (note, value, deleted flag) t old_valueold_value nvarchar(100) The old value of the field. t new_valuenew_value nvarchar(100) The new value of the field. t user_iduser_id int The user who made the change. References ers_user ( user_id ) reason_idreason_id int The reason for the change. # edl_load_dateedl_load_date datetime The date the change was loaded by EDL. d source_indsource_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. # ers_task_recipientTable dbo.ers_task_recipient A list of task recipients. Recipients can include user groups, individual users, or both. Pk Pk_ers_task_recipient_task_recip_id ( task_recip_id ) task_recip_idtask_recip_id * int The autonumbering primary key of the table. # Idx_ers_task_recipient_task_id ( task_id ) task_idtask_id * int The id of the task to which the recipients are attached. References ers_task ( task_id ) Idx_ers_task_recipient_recip_group_id ( recip_group_id ) recip_group_idrecip_group_id int A user group id if the recipient is a user group. References ers_user_group ( recip_group_id -> user_group_id ) Idx_ers_task_recipient_recip_user_id ( recip_user_id ) recip_user_idrecip_user_id int A user id if the recipient is a user. References ers_user ( recip_user_id -> user_id ) audit_historyTable dbo.audit_history Stores the 21 CFR Part 11 audit history for general events such as the signing of documents, password failures, etc. Pk PK_audit_history ( audit_id ) audit_idaudit_id * int The primary key of the table. # effective_dateeffective_date * datetime The date the event occurred. d audit_descaudit_desc * nvarchar(500) The description of what occurred. t user_iduser_id int The id of the user who caused the event. References ers_user ( user_id ) reason_idreason_id int The reason id given when the event occurred. # qcc_file_idqcc_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_typeentry_type nvarchar(15) The enumerated value of the type of event that occurred. DOCUMENT_SIGNED = 9, FAILED_LOGIN = 10 t dimensionTable dbo.dimension Houses all characteristics for the parts being measured. Pk PK_dimension ( dim_id ) Primary key of the table. dim_iddim_id * int The primary key of the table. Referred by dim_relation ( dim_id ) Referred by measurement ( dim_id ) Referred by measurement_action_history ( dim_id ) Referred by measurement_cause_history ( dim_id ) Referred by measurement_history ( dim_id ) FK_model_dim_num ( qcc_file_model_id, dim_number ) Index allowing search on model id and dimension number.Unq FK_model_id_unique_dim_num ( qcc_file_model_id, unique_dim_number ) Index allowing search on model id and unique dimension number. qcc_file_model_idqcc_file_model_id * int The link back to the QCC file model or snapshot of the QCC. References qcc_file_model ( qcc_file_model_id ) dim_descdim_desc * nvarchar(100) The feature label. t FK_model_dim_num ( qcc_file_model_id, dim_number ) Index allowing search on model id and dimension number. dim_numberdim_number * int The current ordinal position of the dimension. # tol_plustol_plus * float The plus tolerance - Add with nominal to get USL. # ctl_upperctl_upper * float The currently saved upper control limit. # nominalnominal * float The nominal or target. # ctl_lowerctl_lower * float The currently saved lower control limit. # tol_minustol_minus * float The minus tolerance - Add with nominal to get LSL. # tol_typetol_type * varchar(50) The tolerance type - SSL, SSU, BI or Non toleranced t dim_source_descdim_source_desc varchar(1000) The characteristic/dim source description. t extra_infoextra_info nvarchar(1000) A general extra info field on the dimension that can be used for any extra info desired. t balloon_numberballoon_number int The balloon number of the dimension. Added for future implementation. # dim_typedim_type * varchar(50) The type of dimension (machine, calculated, or manual). t dim_precisiondim_precision int The number of decimal places to the right of the decimal point. # Unq FK_model_id_unique_dim_num ( qcc_file_model_id, unique_dim_number ) Index allowing search on model id and unique dimension number. unique_dim_numberunique_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_flagdim_dead_flag * int default 0 # transform_infotransform_info varchar(500) This field holds the Johnson Transform information if the dimension is non-normal and has been transformed. t xbar_meanxbar_mean * float default 0 This is the saved mean for the dimension. # range_ctl_upperrange_ctl_upper * float default 0 This is the saved upper control limit of the range chart. # range_meanrange_mean * float default 0 The saved mean of the range chart. # range_ctl_lowerrange_ctl_lower * float default 0 The saved lower control limit of the range chart. # sigma_ctl_uppersigma_ctl_upper * float default 0 The saved upper control limit of the sigma chart. # sigma_meansigma_mean * float default 0 The saved mean for the sigma chart. # sigma_ctl_lowersigma_ctl_lower * float default 0 The saved lower control limit of the sigma chart. # moving_average_ctl_uppermoving_average_ctl_upper * float default 0 The saved upper control limit of the MR chart. # moving_average_meanmoving_average_mean * float default 0 The saved mean of the MR chart. # moving_average_ctl_lowermoving_average_ctl_lower * float default 0 The saved lower control limit of the MR chart. # xbar_plot_limit_upperxbar_plot_limit_upper * float default 0 # xbar_plot_limit_lowerxbar_plot_limit_lower * float default 0 # range_plot_limit_upperrange_plot_limit_upper * float default 0 # range_plot_limit_lowerrange_plot_limit_lower * float default 0 # last_written_recordlast_written_record * int default 0 # historical_plot_flaghistorical_plot_flag * bit default 0 b rt_plots_flagrt_plots_flag * bit default 0 b prevent_nominal_update_flagprevent_nominal_update_flag * bit default 0 b historical_y_flaghistorical_y_flag * bit default 0 b sigma_valuesigma_value * float default 0 The saved sigma value of the dimension. # calculation_scriptcalculation_script nvarchar(500) default '' If the dimension type is calculated, this field holds the equation. t rt_plots_graph_typert_plots_graph_type * varchar(20) default 'XBar' An indicator that stores the graph type; normal, scatter, whisker. t check_for_trend_flagcheck_for_trend_flag * bit default 0 A flag that indicates whether this dimension should be checked for trends when trend detection is enabled. b factor_used_flagfactor_used_flag * bit default 0 b Idx_dimension_aql_percent_id ( aql_percent_id ) aql_percent_idaql_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. References aql_percent ( aql_percent_id ) partTable dbo.part Contains all parts/records for a QCC file. Pk PK_part ( part_id ) Primary key of the table. part_idpart_id * int The primary key of the table. Referred by measurement ( part_id ) Referred by measurement_action_history ( part_id ) Referred by measurement_cause_history ( part_id ) Referred by measurement_history ( part_id ) Referred by part_factor ( part_id ) Referred by part_factor_history ( part_id ) Referred by part_history ( part_id ) Referred by part_source ( part_id ) IX_file_exclude ( 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 ( qcc_file_id, measure_date ) Index allowing search on file id and measure date of part. Includes all other fields to increase performance.Unq IX_file_unique_rec_num ( 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 ( qcc_file_id ) Index on qcc_file_id qcc_file_idqcc_file_id * int File that contains this part. Added 1.7.1 References qcc_file ( qcc_file_id ) Unq IX_file_unique_rec_num ( 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. unique_record_numberunique_record_number int The unique record number regardless of circular status. # record_numberrecord_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_idsub_group_id * int DEPRECATED. # IX_file_measure_date ( qcc_file_id, measure_date ) Index allowing search on file id and measure date of part. Includes all other fields to increase performance. measure_datemeasure_date datetime The measure date of the part. d IX_file_exclude ( 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. deleted_flagdeleted_flag * bit default 0 Flag indicating whether or not part should be excluded from statistics. b edl_load_dateedl_load_date datetime The date the part was loaded by EDL. d signoff_datesignoff_date datetime The original creation date for the part. Set once. d part_sourceTable dbo.part_source Maintains the sources (install_id) and measure date for each part. Pk Pk_part_source_part_source_id ( part_source_id ) part_source_idpart_source_id * int The autonumbering primary key of the table. # Idx_part_source_part_id ( part_id ) part_idpart_id int Foreign key mapped back to part table. References part ( part_id ) measure_datemeasure_date * datetime The measure date that came from the source. d Idx_part_source_install_id ( install_id ) install_idinstall_id * int The install id of the copy of QC-CALC RT that inserted the record. References installation ( install_id ) audit_typeTable dbo.audit_type Pk PK_audit_type ( audit_type_id ) audit_type_idaudit_type_id * int The id of the audit type. Referred by audit ( audit_type_id ) audit_type_descaudit_type_desc * nvarchar(100) The description of the audit type. t [workstation (old)]Table dbo.[workstation (old)] Pk PK_workstation ( workstation ) workstationworkstation * nvarchar(100) t database_versiondatabase_version * nvarchar(50) t last_run_datelast_run_date datetime d visible_flagvisible_flag * bit default 1 b rt_monitorTable dbo.rt_monitor Contains a running log of copies of QC-CALC RT and the files they currently have open. Pk PK_rt_monitor ( monitor_id ) monitor_idmonitor_id * int The primary key of the table. # qc_calc_locationqc_calc_location nvarchar(8000) t serial_numberserial_number varchar(10) The serial number of the copy of QC-CALC RT. t modelmodel varchar(50) The machine model code (OGPM, etc) t current_qcc_file_idcurrent_qcc_file_id int The currently open QCC file References qcc_file ( current_qcc_file_id -> qcc_file_id ) real_time_descriptionreal_time_description nvarchar(200) t machine_namemachine_name nvarchar(300) The name of the workstation. t is_runningis_running bit Flag indicating if RT is currently running or shut down. b Idx_rt_monitor_install_id ( install_id ) install_idinstall_id int A foreign key reference to the installation table allowing us to get more information about the currently monitored part files. References installation ( install_id ) installationTable dbo.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. Pk Pk_workstation_0 ( install_id ) install_idinstall_id * int The primary key of the table. Referred by ers_configuration ( install_id ) Referred by ers_dashboard ( install_id ) Referred by ers_full_view ( install_id ) Referred by ers_run_history ( install_id ) Referred by ers_service ( install_id ) Referred by ers_trigger ( install_id ) Referred by installation_ext ( install_id ) Referred by part_source ( install_id ) Referred by rt_monitor ( install_id ) workstationworkstation * varchar(50) The name of the PC connecting to this database. t install_pathinstall_path nvarchar(8000) The path of the installation of the software. t product_type_codeproduct_type_code * varchar(50) The type code of the product (ex. ERS, EDL, RT, SPC, etc). t product_versionproduct_version * varchar(10) The version of the product installed. 3.4, etc. t database_versiondatabase_version varchar(10) The latest version of the database this product knows about. t last_loginlast_login datetime The latest date/time the application logged into the database. d service_visible_flagservice_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. b installation_extTable dbo.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. Pk Pk_workstation_1 ( install_id ) PK_installation_ext ( install_id ) install_idinstall_id * int The primary key of the table. References installation ( install_id ) revisionrevision int Used to save the current application revision number # update_infoupdate_info nvarchar(1000) Used to save JSON file containing set update DateTime, new revision number and update path. t last_update_datelast_update_date date Used to save the last application update DateTime d qc_calc_spc_groupTable dbo.qc_calc_spc_group Pk PK_spc_group ( spc_group_id ) spc_group_idspc_group_id * int # namename * nvarchar(100) t contentcontent ntext t resourceTable dbo.resource Pk PK_blob1 ( resource_id ) resource_idresource_id * int The auto incrementing primary key of the table. Referred by ers_snapshot ( drawing_resource_id -> resource_id ) Referred by resource_association ( resource_id ) file_namefile_name nvarchar(255) The name of the file with extension. This is just the name and does not include the path. t compression_typecompression_type varchar(10) This is declared for future use, but is not used at this time. t pathpath 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. t blobblob 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_typestorage_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 # resource_associationTable dbo.resource_association Pk PK_resource ( resource_association_id ) resource_association_idresource_association_id * int 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. # tabletable 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. t table_pktable_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_idresource_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. References resource ( resource_id ) rolerole 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. t auditTable dbo.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. Pk PK_audit ( audit_id ) audit_idaudit_id * int The auto counting primary key of the table. # effective_dateeffective_date * datetime The date the event occurred. d Idx_audit_audit_type_id ( audit_type_id ) audit_type_idaudit_type_id * int The type of audit event that occurred. Foreign key to audit_type References audit_type ( audit_type_id ) audit_descaudit_desc * nvarchar(500) A brief description of the event that occurred. t user_iduser_id int The id of the user who caused or performed the action that caused the audit trail. References ers_user ( user_id ) object_typeobject_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. t table_pktable_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. # ers_calendarTable dbo.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. Pk Pk_ers_calendar_calendar_id ( calendar_id ) calendar_idcalendar_id * int The autocounting primary key of the table. Referred by ers_configuration ( calendar_id ) Referred by ers_dashboard ( calendar_id ) Referred by ers_full_view ( calendar_id ) Referred by ers_trigger ( calendar_id ) calendar_desccalendar_desc * nvarchar(100) The name of the calendar. t calendar_jsoncalendar_json * nvarchar(max) A JSON object that holds the actual years and dates for the calendar. t ers_configurationTable dbo.ers_configuration This table represents all scheduled reports and exports in the system. Pk PK_ers_configuration ( config_id ) config_idconfig_id * int The primary key of the table. Referred by ers_configuration_fields ( config_id ) Referred by ers_mapped_group ( config_id ) config_descconfig_desc nvarchar(50) The name of the report or export. t config_typeconfig_type int The type of config (report or export). # owner_user_idowner_user_id int The user who owns the resource. References ers_user ( owner_user_id -> user_id ) filter_idfilter_id int The global filter associated with the config. Can be NULL if none set. # recur_idrecur_id int The FK to the recurrence table. This no longer used. # enabled_flagenabled_flag bit default 0 A boolean flag as to whether or not this config is enabled on the scheduler. b next_rundatenext_rundate datetime The next date time this report/export will run assuming it is enabled. d last_rundatelast_rundate datetime The last date/time this config was run. d report_typereport_type int The type of report if this config is a report (histogram, xbar, etc). # report_template_namereport_template_name nvarchar(500) The template path if this config is a report. t report_print_typereport_print_type int The type of output (print preview, printer, or file). # report_export_typereport_export_type int The type of export if the report is saved as a file (PDF, JPG). # report_send_emailreport_send_email bit default 0 A flag indicating whether the report is emailed upon completion. b report_open_in_default_appreport_open_in_default_app bit default 0 A flag indicating whether or not to open the report. b report_export_file_namereport_export_file_name nvarchar(500) The path to the exported report file. t report_email_toreport_email_to nvarchar(4000) The TO field of the email. t report_email_bodyreport_email_body nvarchar(500) The BODY field of the email. t calc_usedcalc_used bit DEPRECATED b compare_num_lowercompare_num_lower float DEPRECATED # report_email_ccreport_email_cc nvarchar(4000) The CC field of the email. t compare_num_uppercompare_num_upper float DEPRECATED # report_email_subjectreport_email_subject nvarchar(500) The SUBJECT of the email. t single_part_report_sortsingle_part_report_sort * bit default 0 DEPRECATED b single_part_report_sort_typesingle_part_report_sort_type * bit default 0 DEPRECATED b single_part_report_sort_filter_indexsingle_part_report_sort_filter_index int default 0 DEPRECATED # single_part_report_sort_calculation_typesingle_part_report_sort_calculation_type int default 0 DEPRECATED # printer_nameprinter_name nvarchar(100) The name of the printer if the report is to be printed. t use_default_printeruse_default_printer * bit default 1 A flag indicating whether or not to use the default printer in Windows. b subgroup_sizesubgroup_size * int default 5 The subgroup size to use with the data in the report or export. # next_runnext_run datetime The next scheduled run of the report/export. d recur_date_to_runrecur_date_to_run datetime The date to run (for yearly scheduling). d recur_days_of_weekrecur_days_of_week nvarchar(100) The days of the week to run (for weekly scheduling). t recur_time_to_runrecur_time_to_run datetime The time of the day to run (for daily and weekly scheduling). d recur_typerecur_type int The type of recurrence (every minute, daily, weekly, etc.) # recur_intervalrecur_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_flagowner_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. b remove_outliersremove_outliers * bit default 1 A flag indicating whether or not to remove outliers from statistics. b report_notereport_note nvarchar(500) A general note to add to the report. t debug_flagdebug_flag * bit default 0 A flag indicating if we are in debug mode. b record_governorrecord_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. # idx_ers_configuration ( install_id ) install_idinstall_id int This id determines which installation will perform the processing of the report/export. References installation ( install_id ) report_email_indreport_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_constreport_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. t Idx_ers_configuration_ers_service_id ( ers_service_id ) ers_service_iders_service_id int References ers_service ( ers_service_id ) Idx_ers_configuration_ers_service_group_id ( ers_service_group_id ) ers_service_group_iders_service_group_id int References ers_service_group ( ers_service_group_id ) run_statusrun_status nvarchar(15) t next_run_check_date_utcnext_run_check_date_utc datetime d next_run_utcnext_run_utc datetime d disable_afterdisable_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. t Idx_ers_configuration_calendar_id ( calendar_id ) calendar_idcalendar_id int A foreign key reference to the holiday calendar to observe for the report or export. References ers_calendar ( calendar_id ) Idx_ers_configuration_period_id ( period_id ) period_idperiod_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. References ers_production_period ( period_id ) ers_full_viewTable dbo.ers_full_view This table represents full views in ERS. Pk PK_ers_full_view ( view_id ) view_idview_id * int The primary key of the table. Referred by ers_full_view_hotspot ( view_id ) view_descview_desc * nvarchar(100) The name of the full view. t image_descimage_desc nvarchar(50) The name of the image that was uploaded as the background image. t bg_imagebg_image image The image itself in binary form. ~ size_modesize_mode int The size mode of the view (stretch, tile, etc). Stretch is the most common. # refresh_raterefresh_rate * int The refresh rate in seconds. # owner_user_idowner_user_id * int The owner of the full view. References ers_user ( owner_user_id -> user_id ) sticky_flagsticky_flag * bit default 1 Flag indicating whether the hotspot should continue blinking until acknowledgement. b debug_flagdebug_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. b install_idinstall_id int This id determines which installation will actually perform the processing of the full view. References installation ( install_id ) Idx_ers_full_view_ers_service_id ( ers_service_id ) ers_service_iders_service_id int References ers_service ( ers_service_id ) Idx_ers_full_view_ers_service_group_id ( ers_service_group_id ) ers_service_group_iders_service_group_id int References ers_service_group ( ers_service_group_id ) run_statusrun_status nvarchar(15) t next_run_check_date_utcnext_run_check_date_utc datetime d next_run_utcnext_run_utc datetime d enabled_flagenabled_flag bit b Idx_ers_full_view_calendar_id ( calendar_id ) calendar_idcalendar_id int The foreign key reference to the calendar the full view should observe. Full views do not process on known holidays if chosen. References ers_calendar ( calendar_id ) Idx_ers_full_view_period_id ( period_id ) period_idperiod_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. References ers_production_period ( period_id ) ers_dashboardTable dbo.ers_dashboard This table represents the highest level dashboard that contains widgets. Pk PK_dashboard ( dashboard_id ) dashboard_iddashboard_id * int The primary key of the table. Referred by ers_dashboard_widget ( dashboard_id ) dashboard_descdashboard_desc * nvarchar(100) The name of the dashboard. t row_countrow_count * int The number of rows in the layout. # col_countcol_count * int The number of columns in the layout. # refresh_raterefresh_rate * int The number of seconds between refreshes of the dashboard. # owner_user_idowner_user_id * int The owner of the resource. References ers_user ( owner_user_id -> user_id ) export_image_flagexport_image_flag * bit default 0 A flag indicating whether or not the dashboard should be exported to an image file. b export_image_pathexport_image_path nvarchar(8000) The path of the export image. t export_image_settingexport_image_setting nvarchar(1000) The resolution of the image and FTP settings. t dashboard_titledashboard_title nvarchar(200) The title of the dashboard. Can be HTML and dynamic labeling. t title_group_idtitle_group_id int The data group associated with any dynamic fields in the title. References ers_group ( title_group_id -> group_id ) title_global_filter_idtitle_global_filter_id int The global filter associated with any dynamic fields in the title. References ers_filter ( title_global_filter_id -> filter_id ) title_fieldstitle_fields nvarchar(500) The fields within the title. t debug_flagdebug_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. b idx_ers_dashboard ( install_id ) install_idinstall_id int This id determines which installation will perform the processing of the dashboard. References installation ( install_id ) Idx_ers_dashboard_ers_service_id ( ers_service_id ) ers_service_iders_service_id int References ers_service ( ers_service_id ) Idx_ers_dashboard_ers_service_group_id ( ers_service_group_id ) ers_service_group_iders_service_group_id int References ers_service_group ( ers_service_group_id ) run_statusrun_status nvarchar(15) t next_run_check_date_utcnext_run_check_date_utc datetime d next_run_utcnext_run_utc datetime d enabled_flagenabled_flag bit b Idx_ers_dashboard_calendar_id ( calendar_id ) calendar_idcalendar_id int The foreign key reference to the holiday calendar this dashboard should observe. Dashboards will not process on holidays. References ers_calendar ( calendar_id ) Idx_ers_dashboard_period_id ( period_id ) period_idperiod_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. References ers_production_period ( period_id ) ers_production_periodTable dbo.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. Pk Pk_ers_production_period_period_id ( period_id ) period_idperiod_id * int The autocounting primary key of the table. Referred by ers_configuration ( period_id ) Referred by ers_dashboard ( period_id ) Referred by ers_full_view ( period_id ) Referred by ers_trigger ( period_id ) period_descperiod_desc * nvarchar(100) The name of the production period. t period_jsonperiod_json * nvarchar(max) A JSON object that contains the days and hours that define the production period. t qc_sort_planTable dbo.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. Pk Pk_qc_sort_plan_qc_sort_plan_id ( qc_sort_plan_id ) qc_sort_plan_idqc_sort_plan_id * int The auto-generated id for the table. # namename * varchar(100) The name of the sort plan. When running QCQ it's the file name without the extension. t contentcontent text The QC-Sort plan ini file stored like blob. t qc_gage_spec_planTable dbo.qc_gage_spec_plan Stores the main spec plan description and path information. Pk Pk_qc_gage_spec_plan_spec_plan_id ( spec_plan_id ) spec_plan_idspec_plan_id * int The primary key of the table. Referred by qc_gage_spec_plan_setting ( spec_plan_id ) pathpath * text The path to the spec plan on the network. t descdesc nvarchar(100) A description of the spec plan. t contentcontent ntext Reserved for future use. t content_typecontent_type nvarchar(100) Reserved for future use. t qc_gage_spec_plan_settingTable dbo.qc_gage_spec_plan_setting Stores temporary values that are saved when spec plans are run across multiple sessions. Pk Pk_qc_gage_spec_plan_setting_setting_id ( setting_id ) setting_idsetting_id * int The primary key of the table. # Idx_qc_gage_spec_plan_setting_spec_plan_id ( spec_plan_id ) spec_plan_idspec_plan_id * int A foreign key reference back to the qc_gage_spec_plan table. There are many settings to one spec plan. References qc_gage_spec_plan ( spec_plan_id ) categorycategory * nvarchar(100) The general type or section of the spec plan values being saved. This allows us to group the settings by category. t namename * varchar(1000) The name of the setting. t valuevalue ntext The value of the setting as entered by the user. t data_typedata_type * nvarchar(100) The type of the data field. For instance, text vs. numeric. t ers_trigger_qcc_fileTable dbo.ers_trigger_qcc_file This table contains the part files processed by triggers. Pk ers_trigger_qcc_file_primary_key ( trigger_id, qcc_file_id ) Idx_ers_trigger_qcc_file_trigger_id ( trigger_id ) trigger_idtrigger_id * int The foreign key to the ers_triggers table. References ers_trigger ( trigger_id ) Pk ers_trigger_qcc_file_primary_key ( trigger_id, qcc_file_id ) Idx_ers_trigger_qcc_file_qcc_file_id ( qcc_file_id ) qcc_file_idqcc_file_id * int The foreign key to the qcc_file table. References qcc_file ( qcc_file_id ) categoryTable dbo.category Proposed changes v. 1.6.18 - Categories Pk Pk_categories ( category_id ) category_idcategory_id * int This is the primary key for the category table. References category ( category_id -> parent_id ) Referred by ers_group ( category_id ) Referred by qcc_file ( category_id ) Unq Pk_category ( parent_id ) parent_idparent_id int The value of this field points to the parent category. Referred by category ( category_id -> parent_id ) is_dynamicis_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][desc] * nvarchar(100) This filed contains the name of the category. t constantTable dbo.constant constantconstant * nvarchar(50) General key/value pair constants used througout the system. The constant column is not unique and can repeat for short lists. t valuevalue ntext The value of the key value pair. t exception_logTable dbo.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. Pk Pk_exception_log_exc_id ( exc_id ) exc_idexc_id * int The auto numbering primary key of the table. # effective_dateeffective_date date The date/time of the exception. d categorycategory varchar(50) The type of exception. "deliverables" specifies that it is a deliverable type exception. t object_typeobject_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_idobject_id int The generic primary key of the object in question. In the case of deliverables, the fullview_id, dashboard_id, etc. # descdesc nvarchar(1000) The description of the exception. t stack_tracestack_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. t extra_infoextra_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. t install_idinstall_id int The id of the installation of software. # ers_service_iders_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. # severityseverity * 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_flagack_flag * bit default 0 A flag indicating whether or not the exception has been acknowledged by a user. b ers_global_variableTable dbo.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. Pk Pk_ers_global_variable_variable_id ( variable_id ) variable_idvariable_id * int # descdesc nvarchar(255) t namename * nvarchar(100) t typetype * nvarchar(20) t valuevalue ntext t ers_external_reportTable dbo.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). Pk Pk_ers_external_report_report_id ( report_id ) report_idreport_id * int The autonumbering primary key of the table. # resource_typeresource_type * int The type of resource as defined by the viewable resources constant. This defines the type of external report. # owner_user_idowner_user_id * int The owner of the report linking to the ers_user table. References ers_user ( owner_user_id -> user_id ) report_descreport_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. t report_file_namereport_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. t ers_full_view_hotspotTable dbo.ers_full_view_hotspot This table represents hotspots in a full view. Pk PK_ers_full_view_hotspot ( hotspot_id ) hotspot_idhotspot_id * int The primary key of the table. Referred by ers_full_view_hotspot_fields ( hotspot_id ) Referred by ers_full_view_hotspot_trend ( hotspot_id ) Referred by ers_full_view_rule ( hotspot_id ) view_idview_id * int The full view to which the hotspot is linked. References ers_full_view ( view_id ) hotspot_deschotspot_desc nvarchar(100) The name of the hotspot. Usually auto-generated. t group_idgroup_id int The data group that is used to calculate values. # global_filter_idglobal_filter_id int The global filter (optional) that is used to filter data. # pos_xpos_x * float The X position of the hotspot on the full view. This is a percentage. # pos_ypos_y * float The Y position of the hotspot on the full view. This is a percentage. # link_view_idlink_view_id int If the hotspot is a link to another full view, the id of the other view. # sticky_flagsticky_flag * bit default 1 DEPRECATED b last_run_datelast_run_date datetime The date time the hotspot last refreshed. d last_run_data_flaglast_run_data_flag bit A flag indicating whether or not there was data the last time it was refreshed. b record_governorrecord_governor int A record govenor allowing you to pull only the latest x records when calculating the hotspot value. # ers_full_view_hotspot_trendTable dbo.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. Pk PK_ers_full_view_hotspot_trend ( trend_id ) trend_idtrend_id * int The primary key of the table. # hotspot_idhotspot_id * int The hotspot to which the trend is linked. References ers_full_view_hotspot ( hotspot_id ) effective_dateeffective_date * datetime The date of the trend/violation. d rule_typerule_type * int The type of rule that was broken. # trend_statetrend_state * int The current state of the trend (warning or exception). # trend_desctrend_desc * nvarchar(500) The description of the trend. t trend_infotrend_info * nvarchar(4000) Extra info about the trend used to render the full view. t ack_flagack_flag * bit default 0 Flag indicating whether or not the trend has been acknowledged. b unique_identifierunique_identifier nvarchar(200) Special string uniquely identifying the trend. t ack_dateack_date datetime Datetime the trend was acknowledged. d ack_user_idack_user_id int The user who acknowledged the trend. # end_dateend_date datetime The date time the trend actually stopped occurring. d ers_full_view_hotspot_fieldsTable dbo.ers_full_view_hotspot_fields This table is a generic data defined table of fields for hotspots. Pk PK_ers_full_view_hotspot_fields ( field_id ) field_idfield_id * int The primary key of the table. # hotspot_idhotspot_id * int The hotspot to which this field is linked. References ers_full_view_hotspot ( hotspot_id ) field_namefield_name * nvarchar(100) The name of the field. t field_valuefield_value * nvarchar(1000) The value of the field. t field_blobfield_blob image The binary value of the field such as an image (if the field stores binary data). ~ ers_full_view_rule_fieldsTable dbo.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. Pk PK_ers_full_view_rule_fields ( field_id ) field_idfield_id * int The primary key of the table. # rule_idrule_id * int The rule to which the field is linked. References ers_full_view_rule ( rule_id ) field_namefield_name * nvarchar(100) The name of the field. t field_valuefield_value nvarchar(1000) The value of the field. t field_blobfield_blob image The binary value of the field such as an image (if field is binary). ~ ers_full_view_ruleTable dbo.ers_full_view_rule This table stores the rules that are checked in a hotspot. Pk PK_ers_full_view_rule ( rule_id ) rule_idrule_id * int The primary key of the table. Referred by ers_full_view_rule_fields ( rule_id ) hotspot_idhotspot_id * int The hotspot to which the rule is linked. References ers_full_view_hotspot ( hotspot_id ) rule_typerule_type * int The type of rule (Low part count, low Cpk, etc.) # enabled_flagenabled_flag bit default 0 A flag indicating whether or not the rule is enabled. b ers_taskTable dbo.ers_task This table includes tasks that appear in QC-Mobile's task list widget on the home screen. Pk Pk_ers_task_task_id ( task_id ) task_idtask_id * int The autonumbering primary key of the table. Referred by ers_task_recipient ( task_id ) task_typetask_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 # Idx_ers_task_task_user_id ( task_user_id ) task_user_idtask_user_id int The user id who created the task (not the recipient). This is null when tasks are assigned by the system. References ers_user ( task_user_id -> user_id ) task_infotask_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. t task_statustask_status * int The current status of the task. Current values include: 0 - New/Not yet performed 1 - Complete 2 - Canceled # effective_dateeffective_date * datetime The date and time the task was created. d due_datedue_date datetime An optional due date for the task. d complete_datecomplete_date datetime The date and time the task was completed (once completed) d Idx_ers_task_complete_user_id ( complete_user_id ) complete_user_idcomplete_user_id int The id of the user who completed the task. References ers_user ( complete_user_id -> user_id ) required_flagrequired_flag * bit default 0 Whether or not it is required that the user perform the task. b qc_gage_global_gageTable dbo.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. Pk Pk_qc_gage_global_gage_global_gage_id ( global_gage_id ) global_gage_idglobal_gage_id * int The primary key of the table. Referred by measurement ( global_gage_id ) Referred by measurement ( global_gage_id_2 -> global_gage_id ) Referred by qc_gage_gages ( global_gage_id ) Referred by qc_gage_global_gage_calibration_history ( global_gage_id ) workstationworkstation * nvarchar(100) The name of the workstation on which the gage is installed. t namename * nvarchar(100) The friendly name of the global gage. t guidguid * nvarchar(50) A globally unique id that links to the spec plan. t brandbrand nvarchar(50) The brand of the gage if known. t modelmodel nvarchar(50) The model of the gage if known. t serial_numberserial_number nvarchar(50) The serial number of the gage if known. t bar_codebar_code nvarchar(100) A bar code identifier for the gage. t gage_statusgage_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. # Idx_qc_gage_global_gage_connection_id ( connection_id ) connection_idconnection_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. References qc_gage_connection ( connection_id ) parent_global_gage_idparent_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. # qc_gage_gagesTable dbo.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. Pk Pk_qc_gage_gages_gage_id ( gage_id ) gage_idgage_id * int The primary key of the table. # computer_namecomputer_name * nvarchar(100) The name of the computer that stored the gage. t categorycategory * 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. t namename * nvarchar(100) The name of the setting. t valuevalue nvarchar(max) The value of the setting. t data_typedata_type * nvarchar(20) The type of the data field. String, Int and Single are stored. t Idx_qc_gage_gages_global_gage_id ( global_gage_id ) global_gage_idglobal_gage_id * int A foreign key link to the global gage table. References qc_gage_global_gage ( global_gage_id ) measurementTable dbo.measurement Table that contains the literal measurement values of all qcc files. Pk PK_measurement ( part_id, dim_id ) The primary key of the table.IX_dim_part_exclude ( 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. part_idpart_id * int The linked part id. References part ( part_id ) Referred by measurement_action_map ( part_id, dim_id ) Referred by measurement_cause_map ( part_id, dim_id ) Pk PK_measurement ( part_id, dim_id ) The primary key of the table.IX_dim_part_exclude ( 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. dim_iddim_id * int The linked dimension id. References dimension ( dim_id ) Referred by measurement_action_map ( part_id, dim_id ) Referred by measurement_cause_map ( part_id, dim_id ) valuevalue * float The value. # IX_dim_part_exclude ( 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. deleted_flagdeleted_flag * bit default 0 The point deleted flag. b FK_note ( note_id ) Index on the foreign key to note table for join purposes. note_idnote_id int A link to the notes if a note was added to the measurement. References note ( note_id ) original_user_idoriginal_user_id int The original user who entered the value. References ers_user ( original_user_id -> user_id ) current_user_idcurrent_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. References ers_user ( current_user_id -> user_id ) Idx_measurement_global_gage_id ( global_gage_id ) global_gage_idglobal_gage_id int A direct link to the global gage that measured the characteristic. References qc_gage_global_gage ( global_gage_id ) measurement_datemeasurement_date date The date the actual measurement took place. Only used with QC-Gage and manual gaging. d extra_infoextra_info nvarchar(50) Any extra information the user wishes to store at the measurement level. t Idx_measurement_global_gage_id_2 ( global_gage_id_2 ) global_gage_id_2global_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. References qc_gage_global_gage ( global_gage_id_2 -> global_gage_id ) qc_gage_connectionTable dbo.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. Pk Pk_qc_gage_connection_connection_id ( connection_id ) connection_idconnection_id * int The primary key of the table. Referred by qc_gage_connection_param ( connection_id ) Referred by qc_gage_global_gage ( connection_id ) connection_typeconnection_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. # workstationworkstation * 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. t qc_gage_connection_paramTable dbo.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. Pk Pk_qc_gage_connection_param_param_id ( param_id ) param_idparam_id * int The primary key of the table. # Idx_qc_gage_connection_param_connection_id ( connection_id ) connection_idconnection_id * int A foreign key reference to the connection forming a one-to-many relationship where each connection can have more than one parameter. References qc_gage_connection ( connection_id ) namename * nvarchar(100) The name of the parameter being saved. For instance for file based connection "path" might be the name. t valuevalue 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. t ers_triggerTable dbo.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. Pk Pk_ers_trigger_trigger_id ( trigger_id ) trigger_idtrigger_id * int The primary key. Referred by ers_trigger_action ( trigger_id ) Referred by ers_trigger_file_bookmark ( trigger_id ) Referred by ers_trigger_qcc_file ( trigger_id ) trigger_desctrigger_desc nvarchar(100) The short description of the trigger. t trigger_conditionstrigger_conditions nvarchar(4000) Serialized conditions object. t run_statusrun_status nvarchar(15) Contains processing steps. t refresh_raterefresh_rate int The number of seconds of how often the trigger must be checked. # Idx_ers_trigger_part_file_list_id ( part_file_list_id ) part_file_list_id part_file_list_id int Trigger file selection: a foreign key to the part_file_named_list table. References ers_part_file_named_list ( part_file_list_id -> list_id ) Idx_ers_trigger_owner_user_id ( owner_user_id ) owner_user_idowner_user_id int A foreign key to the ers_user table. References ers_user ( owner_user_id -> user_id ) number_of_recordsnumber_of_records int The number of records for reported set # next_run_utcnext_run_utc datetime The next run date to run the trigger in the universal time format. d next_run_check_date_utcnext_run_check_date_utc datetime The date when the trigger will be checked. d monitor_all_filesmonitor_all_files bit Trigger file selection: indicates that all part files should be used in the trigger. b Idx_ers_trigger_install_id ( install_id ) install_idinstall_id int A foreign key to the installation table indicating the processor for the trigger. References installation ( install_id ) Idx_ers_trigger_ers_service_id ( ers_service_id ) ers_service_iders_service_id int The ERS service that processes this trigger. References ers_service ( ers_service_id ) Idx_ers_trigger_ers_service_group_id ( ers_service_group_id ) ers_service_group_iders_service_group_id int A foreign key to the services group that processes this trigger. References ers_service_group ( ers_service_group_id ) enabled_flagenabled_flag bit A flag that indicates wheater the trigger is enabled to autorun. b Idx_ers_trigger_dim_filter_id ( dim_filter_id ) dim_filter_iddim_filter_id int Trigger file selection: a foreign key to the dimension filter. References ers_filter ( dim_filter_id -> filter_id ) debug_flagdebug_flag bit A flag indicating that the trigger should run in debug mode. b complete_records_flagcomplete_records_flag bit A flag indicating that the trigger should only process completed records when running multi-source. b Idx_ers_trigger_calendar_id ( calendar_id ) calendar_idcalendar_id int The foreign key reference to the calendar the trigger should observe References ers_calendar ( calendar_id ) Idx_ers_trigger_period_id ( period_id ) period_idperiod_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. References ers_production_period ( period_id ) qc_gage_global_gage_calibration_historyTable dbo.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. Pk Pk_qc_gage_global_gage_calibration_history_history_id ( history_id ) history_idhistory_id * int The primary key of the table. # Idx_qc_gage_global_gage_calibration_history_global_gage_id ( global_gage_id ) global_gage_idglobal_gage_id * int Foreign key reference to the global gage table. References qc_gage_global_gage ( global_gage_id ) calibration_datecalibration_date * date The date the gage was calibrated. d calibration_due_datecalibration_due_date * date The date the next calibration is due. d part_historyTable dbo.part_history Contains a historical record of changes to part records. part_idpart_id * int The part id of the linked part. References part ( part_id ) effective_dateeffective_date * datetime The date of the change. d field_changedfield_changed * varchar(50) The field that changed (measure date, deleted_flag) t old_valueold_value * nvarchar(100) The old value of the field. t new_valuenew_value * nvarchar(100) The new value of the field. t user_iduser_id int The user who made the change. References ers_user ( user_id ) reason_idreason_id int The reason for the change. # edl_load_dateedl_load_date datetime The date it was loaded by EDL. d source_indsource_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. # Pk Pk_part_history_history_id ( history_id ) history_idhistory_id * int The autocounting primary key of the table. # Database Version: 1.8.44


Table [workstation (old)]

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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 category

Proposed changes v. 1.6.18 - Categories

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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).

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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_analysis_history

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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).

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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).

IndexesField NameData TypeDescription
* 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).

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.
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.
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.

IndexesField NameData TypeDescription
* 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_file_bookmark

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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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 )
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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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
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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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_spec_plan

Stores the main spec plan description and path information.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* 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.

IndexesField NameData TypeDescription
* user_id int AUTOINCREMENT
* user_desc nvarchar( 100 )
Indexes
PK_user ON user_id

Powered by DbSchema