What is relations?

Let us use a simple example to explain what relations and relational database are.

Let's imagine we have a client database. There is one type of records (form) - "Client" in which we store client's name, phone number and the company address:

In some time we notice that we have a lot of clients from the same companies. That is, we have to enter the company's address for every client anew. In addition, if the company moves, we will have to change the company's address for all clients who work in it. We face three problems:

• Necessity to re-enter information that we have already specified
• Excess data (the same name is stored several times)
• Difficulties at changing records

To fix this problem, we have to move our simple database to the relational model. How can we do that?

1. Create a new type of records (form) - "Company" with two simple fields: Title and Address.
2. Create records of the "Company" type with information on companies in the database. Now we have a list of companies:
   
3. Open the "Client" form and add a simple relational field named "Company" onto it. A relational field does not store text or numbers within; it stores a link to another record or records of a specific type. In our case, we will specify a link to the record about a company in this field. Therefore, a folder with records about companies will be the source for this field.
4. For all clients, specify corresponding companies in this field.
5. Remove from the "Client" form unnecessary fields "Company Title" and "Company Address".
6. The relational database is ready.

Therefore, the client form will look as follows:

To choose a company for a client, there will be a convenient list of companies:

However, now we cannot see the company address on the form with information about a client. We can fix it using the relational lookup field. This field displays the value of the record field that was selected in the relational field. That is, in our case, we will be able to see the company address:

So, we have created what is called single one-way relation:

• Single - as only one company corresponds to one client.
• One-way - as we have a client-company relation, but we do not have a company-client relation. That is, defining that a client works in the company does not influence the company in any way. Having selected a company, we cannot view all clients who work in it (of course, you can always create a query or accomplish search to do this).

To create many-to-many relations, you should use a many-to-many relational field. It looks like a table and can store links to several records at once. For example, we can add such field to the "Client" form to display a list of his or her friends:

That is, we have created a many-to-many multiple relation with records of the same type. Many-to-many means that, if we add the record "Mary" to the "Friends" field for "John", the record "John" will be added for "Mary" as well:

A common example of a many-to-many relations is the library database. Every book can have several authors, and one author can have a number of books:

Planning relational database

Before you create a relational database, it is recommended to consider all relations between forms that you want to create. For example, the structure of the database for keeping records on sales for a small store may look as follows:

Here:

• The name of the form is specified in bold (Client, Order, Item, Product)
• Relational fields are specified in blue.
• Relations are marked with arrows. Two-way arrows are used for many-to-many relations.

In the database this will look in the following way: