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.
1 to Many (1 to N or N to 1) Relationships
Suppose that you wish to create a relationhip between Customers and Phone Calls. Suppose that
you have separate records for all of your customers and you also create a new record for every call you receive.
Obviously (well, most of the time), each phone call record may only be related to a single customer. However, if you look from
the customer side, a customer may be related to an unlimited number of phone call records.
1 Customer is related to many Phone Calls. So, we call this as 1 to Many Relationship between Customers and Phone Calls.
Note that, a 1 to N relationship is also a N to 1 relationship. The choice of name depends on in which direction we are looking at.
In the example above, Many Phone Calls are related to 1 Customer.
So we may call this also as Many to 1 Relationship between Phone Calls and Customers.
We will call the first catalog of a 1 to N relationship as Parent Catalog (a.k.a master table).
We will call the second catalog of a 1 to N relationship as Child Catalog (a.k.a detail table).
See "Creating 1 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. 3 distinct
teachers "atmost", you may create 1 to N for Teacher > Student relationship and then 3 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 3 lookup fields where you will be able to select
up to 3 teachers in total.
See "Creating Many to Many Relationships" to learn how to create this type of relationship.