Production Quality

Try me

Problem definition

You have recently been hired as an intern at a metal machining company named ROTCAF. Currently, the company uses paper forms and Excel sheets to register the quality controls that they perform in their finished products, and you have to design a database to store this critical information. Operators perform quality controls, so your database must store some basic information about operators, namely the unique operator identifier, first name, and last name, to ensure traceability. Each quality control tests a specific product, one unit of a production order. Your database must store the unique order identifier of each production order and the total quantity. For the associated product, the database must store the product’s unique identifier and the product name.

Each quality control is performed according to a quality standard. Your database must store the names of the quality standards. There are two types of quality standards: Numerical quality standards and true/false quality standards. Numerical quality standards specify the minimum, nominal, and maximum values of a specific feature of a product. For instance, a numerical quality standard to control the inner diameter of a tube in millimeters can have a minimum value of 29, a nominal value of 30, and a maximum value of 31. The database must store the type of the quality standard and the parameters of numerical quality standards (they can be empty or null when the quality standard is of type true/false).

Finally, the database must store the information of the quality controls: The date and time when the control is performed, the associated production order, the associated quality standard, the associated operator that performed the control, the value of the control, and the comments of the operator.

Design a data model to implement this challenging digital transformation problem.

Solution

The following diagram represents a possible solution to the problem.

In the solution, the quality controls made are stored in the qualitycontrols table, registering the date and time (control_datetime), the value of the control (either float_value or boolean_value depending on the type of control), and the quality standard associated to it (through the quality_standard_fk foreign key), as well as some important information for traceability, such as the operator (operator_fk foreign key), the operator comments, and the production order (production_order foreign key).

Try it yourself

The following code snippet implements the model above in MySQL.

-- Use this in Workbench
DROP DATABASE IF EXISTS quality_model;
CREATE DATABASE quality_model;

USE quality_model;

-- Use this in SQL fiddle
DROP TABLE IF EXISTS Products;
CREATE TABLE Products (
    product_id VARCHAR(10) PRIMARY KEY,
    product_name VARCHAR(100)
);

DROP TABLE IF EXISTS ProductionOrders;
CREATE TABLE ProductionOrders (
    order_id VARCHAR(15) PRIMARY KEY,
    quantity INT,
    product_fk VARCHAR(10),
    CONSTRAINT product_order_fk FOREIGN KEY (product_fk) REFERENCES Products (product_id)
);

DROP TABLE IF EXISTS Operator;
CREATE TABLE Operator (
    operator_id  VARCHAR(5) PRIMARY KEY,
    first_name VARCHAR(30),
    last_name VARCHAR(30)
);

DROP TABLE IF EXISTS QualityStandards;
CREATE TABLE QualityStandards(
    quality_standard_id INT PRIMARY KEY,
    product_fk VARCHAR(10),
    standard_name VARCHAR (100),
    standard_type ENUM('boolean', 'float'),
    min_value FLOAT,
    nominal_value FLOAT,
    max_value FLOAT,
    CONSTRAINT standard_product_fk FOREIGN KEY (product_fk) REFERENCES Products (product_id)
);

DROP TABLE IF EXISTS QualityControls;
CREATE TABLE QualityControls (
    quality_control_id INT PRIMARY KEY,
    quality_standard_fk INT,
    control_datetime DATETIME,
    order_fk  VARCHAR(15),
    float_value FLOAT,
    boolean_value TINYINT(1),
    operator_fk VARCHAR(5),
    comments VARCHAR(500),
    CONSTRAINT control_order_fk FOREIGN KEY (order_fk) REFERENCES ProductionOrders (order_id),
    CONSTRAINT operator_order_fk FOREIGN KEY (operator_fk) REFERENCES Operator (operator_id),
    CONSTRAINT control_standard_fk FOREIGN KEY (quality_standard_fk) REFERENCES QualityStandards (quality_standard_id)
);