The most common type of relation used is the one between a Many table and a One table — called Many to One. However, you can also create Many to Many and One to One relations. All relations can be either manual or automatic.
Automatic and manual relations
Relations can be either automatic or manual.
In an automatic relation, whenever a record in a related table is made current, 4D selects the corresponding record or records. The record or records so specified can then be viewed, printed, modified, or used in searches and sorts. No programming is required.
Note: Automatic relations that point to a table being viewed in a list form (using DISPLAY SELECTION, MODIFY SELECTION, or a subform) will have their automation disabled. This is to preserve the selection being viewed by the user. If you need to use automatic relations that impact a table being viewed, you can perform your processing in a separate process in which the table is not viewed.
In a manual relation, you dictate whether 4D loads the corresponding record or records into memory. To exercise this control, you use methods. For complete information about creating the methods that control related tables, see the 4D Language Reference manual.
You would use a manual relation if you wanted to optimize the performance of specific applications that do not need all corresponding records loaded each time. For example, if your structure relates three or more tables together, you may want to control when related records are loaded into memory. You would also use a manual relation if you wanted to relate two tables with two separate relations. Only one automatic relation can exist between two tables. Any number of manual relations can exist between two tables.
Many to One relations
When you create a relation between two tables, the table containing the primary key in the relation is called the One table and the table containing the foreign key in the relation is called the Many table. The tables are called the One table and the Many table because one record in the One table relates to many records in the Many table and many records in the Many table relate to one record in the One table. This type of table relation is called a Many to One relation.
In the relation between employees and companies, the [Companies] table is the One table and the [Employees] table is the Many table. One company record relates to several employees (i.e., all the people who work for that company) and several employees relate to one company (i.e., the company for which they work). For instance, there may be one record for Acme in the [Companies] table but many records of people employed by Acme in the [Employees] table.
When any record in the [Employees] table is made current, 4D loads the corresponding single record from the [Companies] table. If any fields have been included from the [Companies] table, the values for these
fields are automatically displayed.
The figure below shows how the company name in an [Employees] table record specifies a record in the [Companies] table so that the [Employees] table record can display the company’s address and phone number:
Conversely, when a record in the [Companies] table is made current, 4D creates a selection of records in the [Employees] table and displays them on the form. Since the relation specifies several records in the other table, the names and titles of many employees can be displayed. Only records currently displayed in the form are loaded into memory.
The figure below shows how a company name in a record in the [Companies] table specifies several records in the [Employees] table so that the [Companies] table record can display a list of people employed by that company.
The distinction between the One table and the Many table is specific to a particular relation. A table may be the One table in one relation and the Many table in another. A table in a relation can only have one primary key, but it can have several foreign keys.
For example, suppose you decide to send a package of sample merchandise to everyone in your [Employees] table. You add a [Postal Rates] table that contains zip codes and the postal rate for each zip code. Using this structure enables you to print an address label for each employee that includes the amount of postage needed to mail the package. The figure below shows the [Postal Rates] table added to the database structure.
The Zip Code field in the [Postal Rates] table is its primary key, so the [Postal Rates] table is the One table. The Zip field in the [Companies] table is the foreign key field for this relation. Since the Zip field is a foreign key, it can have non-unique values. The Zip field will contain duplicate Zip codes for companies that are near each other. The [Companies] table is therefore the Many table in relation to the [Postal Rates] table.
Whether a table is a One table or a Many table, therefore, depends on its relation to the other table. The [Companies] table is the Many table in relation to the [Postal Rates] table and it is the One table in relation to the [Employees] table.
One to One relations
One to One relations are used only in special cases since tables that are related on a one-to-one basis could be combined into a single table.
Here are some reasons to use a one-to-one relation:
- You have large Text, Picture or BLOB fields in the database. These fields would slow down the database if they were loaded into memory when a record is made current. By placing text, pictures and BLOBs in another table, you can load the data only when needed.
- You have a very large number of fields and need to divide them into logical groups. Separate tables can make the database faster and easier to use.
- You want to limit access to certain fields. If you use separate tables, you can assign different access privileges to each table.
Many to Many relations
Sometimes you need to relate many records in one table to many records in another table. This is called a Many to Many relation.
An example of a Many to Many relation is a database that tracks class enrollment. Suppose that this database has two tables, [Students] and [Classes]. A student may enroll in many classes and a class may have many students. You want to see all the classes that a student has enrolled in and you want to see all the students enrolled in each class.
Other examples of Many to Many relations include the following:
- [Suppliers] and [Products]: Each supplier provides many products and each product may be provided by several suppliers.
- [Employees] and [Account]: Each employee works on many accounts and each account may be worked on by several employees.
- [Movies] and [Actors]: Each movie involves several actors and each actor may appear in several movies.
You can use 4D to create automatic Many to Many relations. The key is to create an intermediate table which is related to the other tables using Many to One relations. You can then create input and output forms that handle all the necessary record tracking and data display. This section describes how to use automatic relations to handle a Many to Many relation.
The figure below shows the enrollment database with three tables, [Students], [Classes], and [Joining]. This database structure is used throughout this section to explain how an automatic Many to Many relation works.
The [Students] table is a One table. It contains one record for each student, including their name, major, and GPA. The Student ID field identifies each student uniquely.
The [Classes] table is also a One table. It contains one record for each class, including the class name and the instructor. The CatalogTitle field
identifies each class uniquely.
An intermediate table, [Joining], is the Many table for both of the other tables. It contains records for many students and many classes. Forms for this table are used for entering data into both of the other tables, and for displaying information in each of the other tables.
The use of three tables ensures that the data is stored efficiently. A student’s complete record is stored only once. Each class has one record, stored only once. Records that relate
students to classes are stored once for each enrollment. All of the information, however, is available in any combination.
You use the intermediate table —þin this example the [Joining] table — to enter and display information from both of the other tables. Each record that you enter in the [Joining] table is related to both of the other tables (a student and a class). The records from the [Joining] table contain only the two pieces of information that establish the relation: the student ID and the catalog title. Here is an example of a new record being entered in the [Joining] table.
This record indicates Jeffrey T. Spaulding as enrolled in a Journalism class. This record actually combines information from the other two tables.
A similar record exists for each class in which the student is enrolled. Only the Student ID and Catalog Title fields are actually stored in the [Joining] table. Each record catalogs a particular student taking a particular class.
Note: When a record in the [Joining] table is loaded (as when creating such a record), it automatically creates a selection of records in the related tables. The selection consists of the corresponding student and class records. If you switch to either of the other tables, only a single record is displayed. To display all the records, choose Show All from the Queries menu.
The input form for this record is shown below. Notice that it contains fields from both the [Students] and [Classes] tables.
Data is entered only in the Student ID and Catalog Title fields. When a student ID is entered, 4D finds the student information in the related Students table and displays it in the Last Name, First Name, and Major fields. Likewise, when a Catalog Title is entered, 4D finds class information in the [Classes] table and displays it on the input form.
Displaying information in a subformYou can display information from these three tables using subforms. In the student’s record, you can display all the classes in which he or she is enrolled. In the class record, you can display all the students enrolled in a particular class.
To display classes in a student’s record, you use a subform. For information about creating subforms, see Creating and defining a subform.
The record shown above is in the [Students] table. It shows information about the student at the top of the record. The information about the two classes that he is enrolled in is drawn from the [Joining] table where the enrollment information is kept.
Notice that the subform is for the [Joining] table, not the [Classes] table. The [Joining] table contains the records that relate the student’s record to the class records. The subform contains the ClassName field from the [Classes] table. Because of the relation between the [Joining] and [Classes] tables, 4D can display the correct class name automatically.
Here is a record that shows the students who are enrolled in a class:
This is a record from the [Classes] table. It shows class information and lists the students enrolled in the class. The information about the students is also drawn from the [Joining] table since that table contains the records that relate the classes to the students enrolled in them.
In the above examples of subforms, you can enter records in any of the fields shown. For example, to enter a new student into a class record, you simply tab to the last student record shown in the subform and press Ctrl+Shift+/ (Windows) or Command+Shift+/ (Mac OS) to create a new record (you can change this shortcut in the Database Settings, see ). When you enter the appropriate catalog title, the remainder of the information is entered in the record.
Analyzing database relations
The relations that you establish in a database play an important role in the operation of the database by controlling the flow of information between the tables.
If a record with an automatic relation is loaded from disk using an input form, the corresponding record or records from the related table are selected. If a relation selects only one record in a related table, that record is loaded from disk. If a relation selects more than one record in a related table, a new current selection of records is created for that table and the first record in the current selection is loaded from disk. The record that is loaded from disk is called the current record for the table.
In the examples in this section, relations have been established between no more than three tables. In the real world, relations are often created between several tables and are activated one after the other, as in a chain. Each time a relation is activated, 4D creates a selection of records in the related table and loads a record from disk. The record that is loaded from disk becomes the current record for the table and — if the table has an automatic relation —4D creates a selection and loads a current record in the next related table in the chain, and so on.
If the table relations have not been set up properly, the circulation of information between tables can become disorderly or corrupt. The following cases alert you to relational structures of which you should be aware.
Circular relationsA circular relation is one in which table relations are set up so that the transfer of information will loop indefinitely. The figure below shows a circular relation in which the [Employees] table relates to the [Company] table, which relates to the [Insurer] table, which relates back to the [Employee] table.
When a record in the [Employee] table is loaded from disk, 4D loads the related company record from the [Company] table. This becomes the current record for the [Company] table, which in turn loads the related insurer record from the [Insurer] table.
If the table relations were allowed to continue, the records related to this insurer (all the people insured by the company) would be selected in the [Employee] table and the first record in that selection would be the current record. Note that this current record may be different from the current record that started this progression. In this situation, 4D has no way of knowing which record is really the current record.
When 4D encounters this kind of circular relation, table relations are stopped at the last table in the chain. In this case, the relation between the [Insurer] table and the [Employee] table is not carried out.
Multiple relations to the same tableA similar conflict between current records occurs if you have more than one link to the same table.
Since you cannot have more than one current record at a time, you cannot manage an automatic table
relation in which two or more tables are related to the same table.
The following illustration shows a database structure in which a table and its subtable both relate to the same table.
When a user is working with a record in the [Employees] table, the related record is loaded in the [Insurance] table and it is made the current record for that table.
However, there is also a relation between the
[Children] table and the [Insurance] table. This means that another related record is loaded in the [Insurance] table based on the current record (the first record) in the [Children] table. If the child’s insurance company is different from the parent’s, this relational structure will cause problems. In this case, 4D does not stop the relations from proceeding. Both the relations are carried out, but not at the same time.
If you want to use this kind of structure, you must use manual table
relations and control the relations using the commands described in the 4D Language Reference manual.
Another example of a relational structure that cannot be managed by automatic relations is a structure in which one table has more than one relation to another table. Each time a user modifies either of the related fields in one table, the current record in the other table may change. In this situation, you cannot tell which relation is being activated.
Relations from multiple records
Since there is only one current record in a table, relations are not established for all of the records in a selection. Let’s take the case of the the Invoices database shown in the structure below:
When a record in the [Invoices] table is being used, a selection of records is created in the [InvoiceLines] table that contains all of the lines for that invoice. But the corresponding record in the [Items] table is selected only for the first item in the [InvoiceLines] table. The selection in the [Items] table does not include information about all the items in the invoice, only the first item.
However, if you place [InvoiceLines] in a subform in the [Invoices] table, 4D calls each invoice line, one at a time, and activates the relationship for each one of them.