What are the different types of records

Basics of the database

Home

Basics of the database


 

A database is a collection of information structured for efficient use. A telephone directory is a good example of a database. The same applies to a dictionary, calendar or cookbook.

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

Each record contains data fields. Individual pieces of information are stored in a data field. In the database of a telephone directory, for example, a data field contains the name of the person; a second, that person's address; and a third is that person's phone number. Each data record contains these three data fields and each data record can contain information in these data fields.

A field name usually identifies the information contained in the data field. A field name is usually something like a name, address, or phone number. Each data field has a field type that identifies the type of information that can be entered in the data field: numbers, dates, alphanumeric characters, and others. Because each data field contains a particular type of data, calculations and other operations can be performed on the information in the data fields. For example, you can add the numbers from two data fields. A date in one data field can be compared with the date in another data field. A person's first name, stored in one data field, can be shown in front of the last name, stored in another data field, and thus be the first line on an address label.

All data records together form one table. Each database can contain many tables. The following figure shows how these elements are linked together

4D can rearrange records and perform calculations on the information so that you can use this information effectively. For example, 4D can calculate the total value of a data field and display the total in a data record. It can calculate a total for each seller and display a graph comparing sales numbers.

4D can create from 1 to 32,767 tables per application. This allows you to create a structure that exactly fits your needs.

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

In this figure, each employee's record needs the same type of data. The database grows with the number of employees stored.

Often times, a database can better store and access the data when more than one table is used. A good rule of thumb is that different types of information should be stored in different tables.
A common example is a database that records employees and companies. The data records for employees and companies are stored in different tables. If a company's address changes, all you need to do is change that one record. You do not need to enter the new address for every employee who works for this company.
If you only have one table, you would have to enter the address for each individual record; if you have two tables, enter this information only once. If you enter a company name in an employee's record, 4D can search for the company's record and automatically display the correct address.

The following figure shows the structure of a database in which two tables are linked. The arrow between the company field and the data field with the company name shows the link.

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

Note: In the 4D documentation, table names appear in square brackets. This is how they are also displayed in the method editor.

4D is known as a relational database, i.e. several tables are used that are linked to one another in different ways. For example, you can create a record for the [Employees] table that searches the [Company] table and automatically displays and prints information about the company of each employee. The links between the tables make the information from each table available for the data set.
You can enter data directly into linked tables. In a database for invoices, for example, you can also change the information of the individual items in a form for an invoice, although this is stored in a different table. You can also write data in linked tables using the 4D programming language.

Sometimes you need a multi-table structure where the tables are not directly related to each other. It can be advantageous to have a database in which different types of information are stored, such as an address list and an expense table.
4D allows up to 32,767 tables in each database. A table can have up to 32,761 data fields. In the case of multiple tables, virtually any type of database structure is possible.

Typically, you create structures in which information is spread across multiple tables. For example, let's say you create a database in which you record employees and their companies. The structure shown below contains a table [Employees] for storing employee data and a table [Company] for storing company data:

While useful, the data stored in each table does not meet your needs for collecting the information. When you look at a record from the [Employees] table, you also want information about the company in which that employee works; and when you look at a record from the [Company] table, you want information about all the employees who work for that company.
You can join two tables that contain such information - in other words, a join can be made between the data in each table.
In 4D, table links give you access to data from another table. Tables that make information available through a link are called linked tables.

Linked tables offer a variety of options. You can:

  • Store data effectively
  • Update data in one place, the changes are applied wherever this data occurs
  • View linked information
  • Perform searches and sorts on one table based on data in another table
  • Create, modify, or delete records in linked tables

The following figure shows a link between the [Employees] table and the [Company] table in the structure editor:

The [Employees] table contains one record per employee. The table [Company] contains one data record per company. The link between the two tables enables you to access information from both tables, to enter, change or delete information. For example:

  • If the data record of an employee is open, you can view or change the associated company information, e.g. update the company address.
  • When adding a new employee, you can link that employee's record to the company's associated record if the company is already entered, or you can create the new company's record while creating the record for that employee. See the Link Properties section for more information.
  • For any company, you can view or change the information for all employees of that company - name, title, phone number, etc. You can also add an employee's record within the company's record.

You display information from linked tables with the linked data fields - these are the data fields that connect two tables to one another. The linking of tables is intended to tell 4D which record (s) in one table to update based on the updated record in the other table. Linked tables use data in two linked data fields to identify related records. In the previous example, the company name is stored in both tables, the [Employees] table and the [Company] table.
The company field in the [Employees] table and the company name field in the [Company] table link the two tables. The company name field in the [Company] table is the primary key field for [Company]. It uniquely identifies each company record. A primary key field should have the properties Indexed and Unique. If the primary key field is not indexed, 4D automatically assigns this attribute. The company field in the [Employees] table is a linked data field.

Each value in a linked data field corresponds to a value in the primary key field of another table. In this example, a value in the linked data field in [Employees] exactly matches a value in the primary key field in [Company]. The linked data field is also indexed, but its values ​​are not unique, since several employees can work in the same company.

As of 4D version 14, fields with primary keys must be explicitly defined in every table in the database. The values ​​of the primary key field are assigned automatically - either by assigning a sequential number assigned by 4D or by a method written by the user. This procedure ensures the uniqueness of the key field. For example, if the primary key field in the [Company] table is a data field with a serial number instead of the company name, it would be possible for users to enter several companies with the same name but different addresses. If a company name changes, the user can also change the database without affecting the link between the two tables.

If the user has permission to enter the value of the primary key field, you should have both properties Unique and Not changeable assigned (see field properties) to check the uniqueness of the initial entry and to prevent users from being able to change the entry once it has been entered. Do you want the property Not changeable If you do not use it, you must take other steps to ensure that users do not create "orphaned" records in related tables because they can change the values ​​of a primary key field.

Once the links have been set up, you can write and read values ​​in a table while you are working in the linked table. For example, if you enter a company name in an employee's record, 4D searches for this company in the [Company] table and displays the company address and telephone number in the employee's record. If you look at the data record for a company, 4D searches in the [Employees] table for all employees who work for this company and displays their data records in the company data record.

You can call up these links automatically, i.e. you don't have to program anything, or you can make links manually. In the second case you use methods to load and remove linked data sets and control the creation, modification or deletion of linked data sets. In complex structures in which more than two tables are linked and you want to control the loading or non-loading of linked data records, manual links are sometimes advantageous.

For automatic links, assign the appropriate properties when you define the link between the tables. See the Link Properties section for more information.

Product: 4D
Topic: Creating a database structure

4D design mode (4D v15.4)
4D design mode (4D v15)
4D design mode (4D v15.3)
4D design mode (4D v15.6)