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:
To fix this problem, we have to move our simple database to the relational model. How can we do that?
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:
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:
In the database this will look in the following way:
Copyright (C) 2001-2010 Binary Brilliant Inc. All Rights Reserved.