Handing Your Database Over to Users

When designing an application, it is crucial to keep its intended users in mind. An average user probably will not understand the complications that can arise from invalid or incorrect data.

There are several ways to help maintain your data's integrity while making things as simple as possible for the application's users. I will be addressing four of these methods.

1. Server validation
2. Front-end validation
3. Tooltips
4. Useful error messages

I will be using an example application with a backend written in Python and utilizing Flask and a front-end built with React. In this example, a user can submit up to two product usage reports from different tracking systems and have a product order created for them. The two tracking systems have disparate ways of referencing the same products: different names for the products and different categories. The products currently in the database are all matched correctly, but new products will inevitably be added. When that happens, a user must be able to add that product to the database correctly. It is our responsibility as developers to make that as easy as possible for the user.

1. Server Validations

Database Constraints and Validations

We are able to validate data within our model classes using validations and constraints. Validations will not allow instantiation if the data does not meet certain requirements. Constraints will not allow the data to be added to the database if it is invalid.

An example of a constraint is unique=True, which will not allow the addition of a row to a table if the same value already exists in the column. In this example, we need the names of each product to be different in order to avoid confusion when a product order is actually generated. The unique constraint is perfect for this situation.

# server/models.py

class Product(db.Model, SerializerMixin):
    __tablename__ = 'products'

    ...

    id = db.Column(db.Integer, primary_key=True)
    # Add the unique constraint.
    name = db.Column(db.String, unique=True)
    ...

When a POST request is sent to the server, an instance of the Product class is created. If the name is not unique, the instance will not be added to the database and an error will be returned to the client.

# server/app.py

class Products(Resource):

    def get(self):
        ...

    def post(self):
        ...

        # Create a Product instance.
        product = Product(
            name = name,
            category_id = category_id,
            phorest_name = phorest_name,
            vish_name = vish_name
        )

        # Try to add the instance to products table.
        try:

            db.session.add(product)
            db.session.commit()

            return make_response(product.to_dict(), 201)

        # Return an error if the name is not unique.
        except IntegrityError:

            return make_response({'error': '422 Unprocessable Entity'}, 422)

Response Error Messages

In some cases, errors need to be caught even before an instance is created.

In this example, we need to catch if a user submits the wrong report while creating an order. A user is only able to upload .xls and .xlsx files, but they could accidentaly upload files that have nothing to do with the products they are trying to order. We need to make sure that products are actually detected in the files before instantiating the order and return an error if they are not.

# server/app.py

class Orders(Resource):

    def get(self):
        ...

    def post(self):
        ...

        phorest_products = []

        try:
            # See if a file was submitted
            phorest_file = request.files['phorest_file']

            try:
                # Read the file.
                phorest_data = pd.read_excel(phorest_file, engine='xlrd') 

                ...

                # Identify which rows have data that should be added.
                for i in range(phorest_data.shape[0]):
                    if name == 'Wella': 
                        if phorest_data.iloc[i, 0] == 'PROFESSIONAL':
                            name = ''
                            pass

                        if phorest_data.iloc[i, 0] == 'RETAIL':
                            break

                        # Add the product data to phorest_products.
                        try:
                            phorest_products.append((phorest_data.iloc[i, 0], int(phorest_data.iloc[i, 1])))

                        except:
                            pass

                    else:
                        name = phorest_data.iloc[i, 0]

            # Return an error if a file was submitted but cannot be read.
            except:
                return make_response({'error': '422 Unprocessable Entity', 'file': 'phorest_file'}, 422)

            # Return an error if no products were found in the file.
            if phorest_file and not phorest_products:
                return make_response({'error': '422 Unprocessable Entity', 'file': 'phorest_file'}, 422)

        # Move on if no file was submitted           
        except:
            pass

        ...

This process can be repeated for the other file. An error specifying which of the two files is invalid will be returned to the client.

2. Client Side Validations

When possible, invalid data should be detected before a request is even made. Tools like Yup and Formik allow us to define a form schema that specifies what type of data is allowed and whether a field is required, among other things.

// client/src/components/CreateOrder.js

import React from "react";
import * as yup from "yup";

function CreateOrder() {
    ...

    const formSchema = yup.object().shape({
        // Place restrictions on data type and require certain data.
        location_id: yup.number()
            .required("Please choose a location"),
    });

    ...
}

export default CreateOrder;

3. Tooltips

So, we have done our best not to allow invalid data into our database. However, there is still the issue of data that is valid but incorrect.

In this example, a user could easily choose the wrong category for a product. This would pose a problem when an order is being created and the server compares the products in the database to the data in the report files. The user needs to be informed of how to know which product category they should choose.

Tooltips are an ideal way to provide instruction to your users without cluttering up the user interface.

// client/src/components/Add Product.js

import React from "react";

function AddProduct() {
    ...

    return (
        ...
            <label htmlFor="category_id">Category</label>
            <abbr>
                ?
                <span>
                    This can be found in Vish.
                    <br />
                    <strong>Products → Wella</strong>
                    <img src="category_screenshot.png" className="tooltip-img" alt="Category Screenshot" />
                </span>
            </abbr>
        ...
    );
}

export default AddProduct;

Of course, some users might ignore the help provided. It is always useful to give users a way to fix any mistakes, such as an option to edit the product category.

4. Useful Errors

Finally, error messages displayed to the client should be useful. They do not need to see that the error sent back from the server was 422 Unprocessable Entity. They need to see that Quantity must be greater than 0.

Conclusion

The thought of handing your database over to users can be an intimidating one. However, if you have implemented methods to disallow invalid data and given users to tools to submit the correct data, you can rest easy knowing that you have done your job.