2.1 Diagram

Below is an example of a diagram of the database format, but this is not up to date. An up to date diagram can always be found at: https://www.prolinksoftware.com/download/database/prolinkdatabase.html

2.2 General Description

The database format involves several tables and splits both the actual and meta data into smaller chunks for easier storage and processing. Since QC-CALC is very flexible and allows the part file database to seamlessly change over time, the database was designed to do the same.

Exact table definitions are below, but basically parts are stored in the qcc_file table at their highest level. The qcc_file table is synonymous with the part file itself and is meant to represent a particular type of part that is measured over time (i.e. a particular engine block). As things change with the part (i.e. tolerances change or another characteristic is added), the definition of the part is re-written to preserve the historical nature of the data. This new definition is defined as a qcc_file_model and is stored by effective date. For instance, the engine block we are measuring had 5 characteristics as of 1/1/2007 and has 6 characteristics as of 6/1/2007. These would be two different models both grouped under the same engine block in the qcc_file table. Each time anything about the part definition changes, a new model is added within the qcc_file_model table, and the definitions for characteristics and trace fields are redefined. All measurements are then linked to the new chars and trace fields of the new part type.

To explain further, a qcc_file_model is a snapshot of the definition of a particular part to be measured at a particular point in time. The model contains a unique set of characteristics and trace fields. When parts are actually measured, the part, measurement, and part_trace field tables are filled with actual data and are mapped back to their corresponding models, characteristics, and trace fields.

The assignable_cause and corrective_action tables define the entire list of assignable causes and corrective actions that can occur. The measurement_cause_map and measurement_action_map table allow specific causes and actions to literally be mapped to a measurement in a many-to-many fashion. Therefore, more than one part can use the same assignable cause and parts can have more than one assignable cause. In QC-CALC 3.x, assignable causes and corrective actions are mapped to the part and not the individual measurement. In QC-CALC 4.x, assignable causes and corrective actions will be mapped to the individual measurement. Therefore, the database is currently set up to store causes and actions at the measurement level, but actually stores them at the part level. This is temporary.

2.3 Table Definitions

qcc_file
This table represents the part file directly and groups all history of the part file under one umbrella. This allows for easier querying later across all parts of the same type. A combination of the part file name and the creation date on the file itself are used to positively identify part files from each other. This allows you to have similar part files from multiple plants sharing the same database. The EDL description (described above in the Options section) allows you to add a friendly name to describe the part file other than the file name itself. This way, if you have two part files with the same name from two different plants, you can tell the two apart in the database (i.e. widget (Shanghai) and widget (Los Angeles)).

qcc_file_id - This is the auto-generated primary key the defines the qcc file.
qcc_file_desc - The name of the part file without the .qcc extension.
creation_date - This is the creation date from the control section of the part file. It helps to uniquely identify one part file from another if there are multiple PCs with the same part files.
edl_desc - This is a field identifying which copy of EDL actually added the data. This can be set at the plant level if desired.
archive_ind – This is a tristate that indicates whether or not the part file has been archived. The values are 0 (normal), 1 (archived) and 2 (override normal). Override normal can be set in ERS to force a file to be visible throughout the application even if it has not had activity for a long period.
last_edit_date – This is a date indicating the last time an edit occurred to the part file. It is an internal indicator for EDL.

qcc_file_model
This table represents a particular historical snapshot of the part as of a particular date. Each time a part is changed in the part file, a new part type record is generated along with the latest definition for the part. This allows us to maintain a historical record of the data as it looked on the particular day of the export. For example, in the part file, if the tolerances become smaller over time, parts that were previously in spec can become out of spec if measured against the new tolerances. Keeping this table and part type definitions historical keeps the history intact.

qcc_file_model_id - Auto-generated primary key
qcc_file_id_id - Foreign key reference back to the qcc_file table. effective_date - The date as of which this new definition is effective.
sub_group - Definition for the number of the subgroup for a particular model dimension

dimension
This table is a definition of all characteristics of a particular part type.

dim_id - Auto-generated primary key
qcc_file_model_id - Foreign key reference back to the model table
dim_desc - The characteristic label
dim_number - The number of the label in the Real-Time display
tol_plus - The plus tolerance value (NULL if single sided lower type)
ctl_upper - The upper control limit.
nominal - The nominal
ctl_lower - The lower control limit.
tol_minus - The minus tolerance value (NULL if single sided upper type)
tol_type - Defines the type of tolerance. Options are BI, SSU, SSL, NON for Bilateral, Single Sided Upper, Single Sided Lower, and Non-Toleranced respectively.

dim_type - defines the source of the data (from the machine, manually entered, or calculated)
dim_source_id - Foreign key reference to the dim_source table that declares the source of the characteristic (i.e. which machine is literally responsible for the characteristic - currently only available via Zeiss Calypso)
extra_info - The characteristic information that accompanies characteristics from QC-CALC. This is set in the Edit Nominals & Tolerances screen of QC-CALC SPC 3.0.

balloon_number - This is the balloon number from the CAD drawing if supplied. transform_info – This field holds the settings for the Johnson Transform if one has been performed on the characteristic. This way, we can use the settings to perform the transform consistently going forward without having to recalculate the algorithm.
dim_source
This table holds a reusable list of characteristic sources. This allows you to track the source of a particular characteristic. For instance, it may be a particular machine on your shop floor that produced the characteristic. Since the same machine produces many characteristics, the same machine can be mapped to multiple characteristics in the characteristic table.
dim_source_id – An auto-generated primary key.
dim_souce_desc – The name of the source.

dim_relation_type
This table defines characteristic relation types so ERS knows how to handle the relationship in charts, reports, etc.

relation_type_id - Auto-generated primary key
relation_type_desc – The name of the relation type.

dim_relation
This table sets up a particular characteristic as the master characteristic of a particular characteristic relationship. The characteristic’s id is linked in this table and the child characteristics in the relationship are mapped through the dim_relation_map table.

dim_relation_id - Auto-generated primary key
dim_id – The id of the characteristic that is the master in the relationship.
relation_type_id – A foreign key mapped telling us what type of relationship this is.

dim_relation_map
This table adds child characteristics to the relationship and identifies their roles in the relationship. For instance, in the case of True Positions, the True Position characteristic is the master characteristic in the relationship. This will be in the dim_relation table. The other 3 characteristics in the relationship (X, Y, and Diameter) would be mapped in this table.

relation_map_id - Auto-generated primary key
dim_relation_id – A foreign key reference to the relationship definition including the master characteristic and relationship type.
map_dim_id – The id of the characteristic that is one of the children in the relationship.
map_dim_desc – The description of the role of the child characteristic (i.e. X, Y, DiameterPin, DiameterHole)
extra_info – A characteristic information field for future expansion.

factor
This table houses the definitions of the trace fields for a given part type.

factor_id - Auto-generated primary key
qcc_file_model_id - Foreign key reference back to the model table
factor_number - The physical number of the trace field to keep them in order.
factor_desc - The description of the trace field.
factor_type - The type of trace field (text vs. numeric)

part
This table represents an actual part measured in QC-CALC. It is the top level for the part and includes the measurement date, a sub group identifier, and the record number from the part file database.

part_id - Auto-generated primary key
qcc_file_model_id - Foreign key reference back to the model table
unique_record_number - A unique record number from the part file. This is unique even if the part file database is circular.
record_number - The current record number in the part file database. This number is not guaranteed unique if the file is set up to be circular. For a guaranteed unique number, use unique_record_number.
sub_group_id - The auto-incrementing sub group number that allows part grouping at the subgroup level. This is a convenience field to allow aggregate rollups since the measurement data is stored at the lowest level rather than at the “point” level in QC-CALC.
measure_date - The date and time the part was measured.
deleted_flag - Indicates whether or not the entire part was excluded.
edl_load_date – Indicates when the record was added to the database. Used for internal tracking purposes and the EDL export events.

measurement
This table houses the actual values that were measured. The values are stored based on the part and characteristic measured.

part_id - Foreign key reference back to the part
dim_id - Foreign key reference back to the characteristic
value - The actual value measured.
deleted_flag - Indicates whether or not the point was excluded.
note_id – Foreign key reference to a note for the measurement (notes are re-used).

note
This table houses all notes in a single place to avoid repeating the note in the measurement table. Therefore, if several points or entire subgroups of parts have the same note, the note will be added to this table once and then linked to all the appropriate measurements in the measurement table.

note_id – Primary key identifier
note_desc – The notes themselves.

measurement_history
This table houses the historical values for CFR21 Part 11 auditing reasons. Whenever a field is changed, the latest value is updated in the measurement table. The audit trail of the edit is stored in this table.

part_id - Foreign key reference back to the part
dim_id - Foreign key reference back to the characteristic
effective_date - the effective date of the edit
field_changed - the name of the field that changed.
old_value - The value before the change
new_value - The value after the change
user_id - The user who made the change. Foreign key reference to user table.
reason_id - The reason code for the change. Foreign key reference to reason table.
edl_load_date – Indicates when the record was added to the database. Used for internal tracking purposes and the EDL export events.

part_history
This table houses the historical values for CFR21 Part 11 auditing reasons. Whenever a field is changed, the latest value is updated in the part table. The audit trail of the edit is stored in this table.

part_id - Foreign key reference back to the part
effective_date - the effective date of the edit
field_changed - the name of the field that changed.
old_value - The value before the change
new_value - The value after the change
user_id - The user who made the change. Foreign key reference to user table.
reason_id - The reason code for the change. Foreign key reference to reason table.
edl_load_date – Indicates when the record was added to the database. Used for internal tracking purposes and the EDL export events.

user
This table houses the users of the system. Users are added to this table as they are used in QC-CALC - not when they are created.

user_id - Auto-generated primary key
user_desc - The description of the user as passed from QC-CALC

reason
This table houses the reason codes in the system. Reason codes are added as they are used in QC-CALC - not when they are created.

reason_id - Auto-generated primary key
reason_desc - The description of the reason as passed from QC-CALC.

part_factor
This table houses the actual value of the trace fields of a particular part. The values are stored according to the part and trace field being measured.

part_id - Foreign key reference back to the part
factor_id - Foreign key reference back to the trace field
value - The actual value of the trace field. This is stored as a 500 character field even though numeric values could also be stored here. For numeric values, convert the value to a numeric value. If the value is not set in QC-CALC, a NULL will be inserted.

assignable_cause
This table houses the complete list of assignable causes for all parts. This list is currently populated as the causes are used and not when they are literally created in the part file. This keeps the database more efficient.

cause_id - Auto-generated primary key
cause_ref - The short description or reference
cause_desc - The description of the cause. 500 character field.

corrective_action
This table houses the complete list of corrective actions for all parts. This list is currently populated as the actions are used and not when they are literally created in the part file. This keeps the database more efficient.

action_id - Auto-generated primary key
action_ref - The short description or reference
action_desc - The description of the action. 500 character field.

measurement_cause_map
This table allows the assignment of any number of assignable causes to a measurement. QC-CALC 3.0 saves assignable causes at the part level. This means that assignable causes will be mapped to each measurement in the part. In 4.0, assignable causes will be mapped at the measurement level.

part_id - Foreign key reference back to the measurement
dim_id - Foreign key reference back to the measurement
cause_id - Foreign key reference back to the assignable_cause
cause_number - The number of the cause since there can be multiple. Also matches to the action.

measurement_action_map
This table allows the assignment of any number of corrective actions to a measurement. QC-CALC 3.0 saves corrective actions at the part level. This means that corrective actions will be mapped to each measurement in the part. In 4.0, assignable causes will be mapped at the measurement level.

part_id - Foreign key reference back to the measurement
dim_id - Foreign key reference back to the measurement
action_id - Foreign key reference back to the corrective_action
action_number - The number of the action since there can be multiple. Also matches to the cause.

part_factor_history
This table holds historical trace field values for each part. As changes occur to the trace field values, a record of the change is automatically added to this table. If Part 11 mode is enabled in QC-CALC, the user_id and reason_id fields will be populated. Otherwise, these fields will be set to NULL.

part_id - Foreign key reference back to the part
factor_id - Foreign key reference back to the trace field
field_changed - The field that changed.
effective_date - The effective date/time of the change.
old_value - The value before the change.
new_value - The value after the change.
user_id - The user who made the change (Part 11 Mode only)
reason_id - The reason the change was made (Part 11 Mode only)
edl_load_date – Indicates when the record was added to the database. Used for internal tracking purposes and the EDL export events.

measurement_cause_history
This table holds the history changes to the measurement_cause_map table. As causes are added and removed, a record of the change is added to this table. This includes setting and removing assignable causes to/from the part and not just changes. In cases like this, the old or new value will be set to NULL.

part_id - Foreign key reference back to the part
dim_id - Foreign key reference back to the characteristic
cause_number - The number of the cause since there can be multiple.
effective_date - The effective date/time of the change.
old_cause_id - The cause_id before the change.
new_cause_id - The cause_id after the change.
user_id - The user who made the change (Part 11 Mode only)
reason_id - The reason the change was made (Part 11 Mode only)
edl_load_date – Indicates when the record was added to the database. Used for internal tracking purposes and the EDL export events.

measurement_action_history
This table holds the history changes to the measurement_action_map table. As actions are added and removed, a record of the change is added to this table. This includes setting and removing corrective actions to/from the part and not just changes. In cases like this, the old or new value will be set to NULL.

part_id - Foreign key reference back to the part
dim_id - Foreign key reference back to the characteristic
action_number - The number of the cause since there can be multiple.
effective_date - The effective date/time of the change.
old_action_id - The action_id before the change.
new_action_id - The action_id after the change.
user_id - The user who made the change (Part 11 Mode only)
reason_id - The reason the change was made (Part 11 Mode only)
edl_load_date – Indicates when the record was added to the database. Used for internal tracking purposes and the EDL export events.

constant
This table holds any constants that are needed in the system. Currently this includes the database version number.

constant – A label describing the constant.
value – The value of the constant.

audit_history
This table holds any 21 CFR Part 11 actions that have occurred. These include the creation of new records, the signing of reports. They are not changes to individual parts so they are placed in a more generic table.

audit_id – An auto-generated primary key.
effective_date – The date and time the action took place.
audit_desc – The description of the action that took place.
user_id – The user who performed the action.
reason_id – The reason for the action.

audit_type
This table is related to the audit table (not audit_history) and tracks the type of activities that happen in the database. For instance, when EDL loads data an audit record is inserted to the audit table with an audit type of EDL Load.

audit_type_id - An auto-generated primary key.
audit_type_desc – A description of the type.

audit
This table tracks activities that happen in the database. For instance, when EDL loads data an audit record is inserted to this table.

audit_id - An auto-generated primary key.
effective_date – The date of the audit event
audit_type_id – The type of event that occurred.
audit_desc – The description of the event (records added, edited, etc)
user_id – The user id performing the action.

2.4 Example Queries

These queries are meant as a guide for you to use to prove that the data is accurate. They are only samples.

2.4.1 Example 1: Retrieving Latest Part Definition For part File

2.4.2 Example 2: Retrieving Latest Part Characteristics For part File

2.4.3 Example 3: Retrieving Measured Values For Part

2.4.4 Example 4: Retrieving Measured Values Using Subgroup Average

2.4.5 Example 5: Retrieving Measured Values Across Models

2.4.6 Example 6: Retrieving Out Of Control Values