Understanding Relationships
Recommended video: Creating Relationships
A relationship is defined between two catalogs. It helps to easily access the related information saved in the
second (child) catalog while you are viewing a record details of the first (parent) catalog. Creating relationships (whenever
possible) greatly enhances the performance and ease of information management in your working environment.
SpeedBase supports creating both one to many (1-N or N-1) and many to many (N-N) type relationships between catalogs.
A Real World Example
Suppose that you save company information in the "companies" catalog.
Suppose that you also save contact information in a "people" catalog where each person works for one of those companies.
The simplest approach is to add all the company information to the people record which means,
you have to repeat the same company information on each person record. If a company information needs to be updated,
you must update each person related to that company. Forget one and you have now inconsistent copies of the company
information. After a while, you may even have no idea which record has the correct information.
Suppose that we have a way to link people to the companies for which they work. When you are able to easily
move to the list of employees from the company record, or, move easily to the parent company from a certain person record,
there is no more need to save repeated company information to the people records.
Relationships make it possible to jump from a record you are viewing to another type of record related to the former
with a single click. Starting from a company record, you may jump to products of that company, or to the contact details
of the manager of it, or to the list of the orders you received from it, or to the phone calls or support activities of it etc.
Types of Relationships
There are basically two types of relationships.
One to Many (1 to N or N to 1) Relationships
Suppose that you wish to create a relationship between Customers and Phone Calls. Suppose that also
you have separate records for each of your customers and you create a new record for every call you receive.
For this example, each phone call record can only be related to a single customer. Similarly, looking to the relationship from
opposite side, a selected customer can be related to an unlimited number of phone call records.
One Customer is related to many Phone Calls. So, we call this as One to Many Relationship between Customers and Phone Calls.
Note that, a "One to Many" relationship would be called a "Many to One" relationship if you just change the order of the catalogs.
The choice of name depends on in which direction we are looking at.
In the example above, Many Phone Calls are related to one Customer.
So we may call this also as Many to One Relationship between Phone Calls and Customers.
We will refer the first catalog of a One to Many relationship as Parent Catalog (a.k.a master table).
We will refer the second catalog of a One to Many relationship as Child Catalog (a.k.a detail table).
See "Creating One to Many Relationships" to learn how to create this type of relationship.
Many to Many (N to N) Relationships
Suppose that you wish to create a relationhip between Students and Teachers.
Suppose that you have distinct records for all students and all teachers. In most scenarios, every student
takes multiple courses from multiple teachers. Every teacher also teaches to many students.
So every student is related to many teacher as well as every teacher is related to many students.
In this case, we have a many to many relationship.
Caution!
You may think that you can simply forget about other types and create N to N in every case as it seems to cover the rest.
However, an N to N type relationship is not convenient as you cannot get the list of students with related teacher information
in a single table. That means you will not be able to export this type of data. This is due to fact that each row of student has
multiple teacher relations (theoretically unlimited) which cannot be displayed in a single "teacher" column.
Another disadvantage is that computed fields cannot process data from this type of fields.
You are recommended to prefere 1 to N (or N to 1) type relationships if there is no unavoidable requirement for creating a N to N relationship.
Tip: In some applications, it is much better to create multiple N to 1 relationships on one side and a single 1 to N relationship on the other side
rather than a N to N. Considering the example above, if a teacher has many students whereas we know that each student may have a limited number of e.g. four distinct
teachers "atmost", you may create 1 to N for Teacher > Student relationship and then four counts of N to 1 relationship between Student > Teacher.
This means, the teacher record will again display all related students and the student record will have four lookup fields where you will be able to select
up to four teachers in total.
See "Creating Many to Many Relationships" to learn how to create this type of relationship.