Database basics

A database is any collection of information organized so that it can be used efficiently. A telephone directory is a good example of a database. So is a dictionary, calendar, or cookbook.

The information in a database is organized in the form of records. Each record contains all of the information about one person or thing in the database. For example, each record in a telephone directory contains one person’s name, address, and telephone number.

Each record contains fields. A field is used to store a particular piece of information. For example, in the telephone directory database, one field contains the person’s name; a second field contains the person’s address and a third field contains the person’s telephone number. Every record contains each of these fields and every record can have information in these fields.

A field name usually identifies the information that goes into the field. A field name is usually something like Name, Address, or Phone Number. Each field has a field type that identifies the kind of information that can be entered in a field: numbers, dates, alphanumeric characters, and others. Because each field contains a specific type of data, you can perform calculations and other operations on the information in the fields. For example, numbers from two fields can be added. A date in one field can be compared to a date in another field. A person’s first name (stored in one field) can be displayed in front of the last name (stored in another field) to make the first line of an address label.

All the records together make up a table. Each database can contain many tables. The following figure shows how these concepts are related.

4D can reorganize records and perform calculations on the information so that the information is useful. For example, 4D can calculate the total values in a field and present the total in a report. It can calculate a total for each salesperson and display a graph that compares sales figures.

4D can create from 1 to 32 767 tables per database. This means that you can create a structure that is precisely adapted to your needs.

Some databases use only one table. You use a single table for a single category of information such as employees, companies, or inventory. You can have as many fields in a table as you need (up to 32,767).

In the figure above, every person’s record needs the same types of data. The database grows in accordance with the number of employees stored.

A database can often store and access data more efficiently by using more than one table. A good rule to remember is that different types of information should be stored in different tables.

A database that keeps track of both employees and companies is a good example. The records for the employees and the companies are stored in different tables. If the address of a company changes, you need only change that company’s record. You do not need to enter that new address for every employee who works for the company.

With a single table, you would have needed to enter the address in each individual record; with two tables, you need to enter that information only once. When a company name is entered in an employee’s record, 4D can search for the company’s record and automatically display the correct address.

The figure below shows the structure of a multiple-table database in which two tables are related. The arrow drawn between the [Employees]Company field and the [Company]Name field shows that relationship:

The data for each employee is stored in the [Employees] table. Data about each company is stored in the separate [Company] table. 

4D is called a relational database application because it can use multiple tables and relate them in various ways. For example, you can create a report for the [Employees] table that searches the [Company] table and automatically displays and prints information about each employee’s company. The relationship between the tables allows information from each table to be available to the report.

You can also enter data directly into related tables. For example, an invoicing database can write information to a [Line Items] table from within an Invoicing screen. You can also write data to related tables using 4D’s language.

Sometimes you need a multiple-table structure in which tables are not directly related. It may be convenient to have one database store different kinds of information such as a contact list and an expense table.

4D allows up to 32,767 tables in each database. A table can have up to 32,767 fields. Using multiple tables, virtually any kind of database structure is possible.

You will usually need to create structures in which several tables share information. For instance, suppose you create a database to keep track of employees and their companies. 

The database structure, shown below, contains a [Employees] table for storing employee information and a [Companies] table for storing company information.

Although useful, the information stored in each separate table does not fulfill your information tracking needs. When you are viewing a record from the [Employees] table, you need to be able to view information about the company for which that employee works and when you are viewing a record from the [Companies] table, you need to be able to view information about all the employees who work for that company.
To allow two tables to share information in this way, the tables can be related to each other — in other words, a relation can be established between the data in each table. In 4D, table relations allow data stored in one table to be accessed from another table. Tables that share information by means of a relation are called related tables.

Relating tables allows you to do the following:

• Store data efficiently,
• Update data in one place and have the change reflected everywhere the data is used,
• View related information,
• Perform queries and sorts in one table that are based on data in another table,
• Create, modify, or delete records in related tables.

The figure below shows a relation created between the [Employees] table and the [Companies] table in the Structure editor:

The [Employees] table contains one record per employee. The [Companies] table contains one record per company. The relation between the two tables allows you to access, enter, modify, or delete information from both tables. For example:

• When an employee’s record is on screen, you can view or modify the corresponding company information — the address, city, state, zip code, and company telephone number.
• When you add a new employee, you can link the employee’s record to the appropriate company record (if the company is already entered), or, if the person’s employer is not in the database, create the new company record while creating the employee record. For more information, see Relation properties.
• For each company, you can view or modify information for each employee in the company — name, title, telephone number, and so forth. You can also add a employee record from within the company record.

You are able to display information from related tables by means of the related fields — the fields that connect the two tables in a relation.
The basic purpose for relating tables is to instruct 4D which record or records to make current in one table based on which record is current in the other table. The related tables make use of data in two related fields to identify corresponding records.
In the previous example, the company name is stored in both the [Employees] table and the [Companies] table. The Company field in the [Employees] table and the Name field in the [Companies] table relate the two tables. The Name field in the [Companies] table is the primary key field for [Companies]. It uniquely identifies each company record. A primary key should have the Indexed and Unique attributes. If the primary key field does not have the Indexed attribute, 4D assigns this attribute automatically. The Company field in the [Employees] table is a foreign key field.

Each value in a foreign key field is equal to one value of the primary key field in another table. In this example, a value of the foreign key field in [Employees] matches exactly one value of the primary key field in [Companies]. The foreign key field is also indexed but its values are non-unique (e.g., several employees may work for the same company).

Starting in 4D v14, primary key fields must be explicitly defined in each table of the database. The values of the primary key field are usually assigned by the database automatically — either by assigning a sequence number that 4D generates or by a user-written method. Such a procedure guarantees the uniqueness of the key field. For example, if the primary key field in the [Companies] table is a sequence number rather than the company name, it would be possible for users to enter several companies with the same name but different addresses. Also, if a company name changes, the user could make the change to the database without disturbing the relation between the two tables.

If the user is permitted to enter the value of the primary key field, you should select both Unique and Can’t Modify as Field properties to check for the uniqueness of the initial entry and to prevent users from subsequently changing the entry to a non-unique value. If you elect not to use the Can’t Modify attribute, you will need to take other measures to prevent users from creating “orphaned” records in any related tables by making changes to the values of the primary key field. 

When relations are established, you can read and write values in one table while working in the related table. For example, when you enter a company name in an employee’s record, 4D searches for that company in the [Companies] table and displays the company address and phone number in that employee’s record. When you view a company’s record, 4D searches in the [Employees] table for all the employees who work at that company and displays their records in the company record.

These relations can be invoked automatically (i.e., with no programming on your part) or you can choose to use manual relations. In the latter case, you use methods to load and unload related records and control the creation, modification, or deletion of related records. Manual relations are sometimes preferable in complicated structures in which more than two tables are related to one another and you need to control the loading and unloading of related records. You can choose to use automatic relations by selecting the appropriate properties at the time the relation between the tables is specified (see Relation properties.