On the QuintaDB service, data is stored in tables. Tables are filled through forms^[1]. Using the table with orders for home appliances store as an example, we will examine the most common mistake in creating a database. Assume we have a task to create a table for managers to enter customer data and orders. For a simple database, one table may be sufficient, but for most databases, you will need multiple tables.

Consider the first case. Let's create an Orders form and try to work with one table only.

Here we list some of the most common problems that you may face:

• A large, cumbersome table, which is not convenient to work with. Especially if the table contains many columns;
• You have to duplicate the product information (name, price, etc.);
• You must enter the same customer data with new orders;
• Data duplication or redundancy takes up space and can lead to errors and inconsistencies;
• It is hard to calculate the sum of all orders. Especially if you need additional discount calculations, taxes, etc..

To save time and simplify the task for yourself and your managers, use the following instruction. Before starting the database design, we recommend thinking in advance about the purpose of the database. Decide how many tables you need and what information you need to put inside. Define a list of users who will work with the database. This will help to identify the tasks correctly. In our example, we will create a simple appliance store database for managers.

Step 1. Create the Product catalog form and add the following fields: Category, Name, Model, Product code, Status, Price.

Step 2. Fill in the table with product information or import data^[2] .

Step 3. Create a Customers form, which will contain fields for filling in customer data (Name, Address, Phone, etc.). At this step, it is not necessary to fill in the table with customers, but if you already have a ready-made database, then you can do it right away.

Step 4. To place a customer order, you need to create a third form - the Order form.

You need to link this form to the Product Catalog table using the Relationship field^[3]. This field will help to link data from several tables within your application (for more information about relationships between tables, read this article).

We recommend using the column which contains unique values for the relationship. The Product сode column from the Product Catalog table is a good fit for this, because the code is never repeated.

Select the Many-to-one relationship type and the Dropdown or Multi Select with autocompletion display type. Now, when you select a product code on the order form, the system automatically detects: the product name, model, status and price per unit. These columns (Name, Model, Price) can be displayed in the current table using Linked columns^[4] fields.

Step 5. Add a numeric field Quantity and the Total formula^[5] to the form.

Using the formula Price * Quantity, we will calculate the total amount of the order.

Step 6. Create the final form - Sales. Using the Relationship field - Customer, we'll link the Sales and Customers tables. Relationship type - Many-to-one.

Step 7. Add the Order form as a Subform field^[6]. To summarize the values of the Total column, add the Linked column Payable amount to the form. (Follow this link to learn more about how the amount is calculated by subform field).

Step 8. Fill in the form. Select the customer from the linked table or add a new one using the Relationship field. The client orders are entered in the Order subform.

As a result, we have a more compact table, without the need to manually duplicate the data each time. Also, if you update the values in one table, then all changes will be displayed in the related tables too. This is called a relational database.

In the next article, you will learn how to split permissions in the database using reports.

Useful links:

1. Creating a Database

2. How to import Excel and CSV files to online database

3. How to create relationships between tables

4. Linked column field

5. Formula field

6. Subform field