Step 7) Relations

Prerequisites
What are Relations, and how are they implemented in 4th Dimension?
Learning Resources

Prerequisites

If you are new to the concept of a database, we suggest that you review the following pages:

What are Relations, and how are they implemented in 4th Dimension?

In a database, data is organized and stored in data structures known as Tables. A table contains information about one thing. For example, a contacts table would store information about contacts, and only about contacts. The data stored in a table is divided and structured into fields, which comprise the attributes of the thing for which the table is storing data. If considered in tabular format (rows and columns), the fields of a database are the equivalent of columns. A relation is a link between two tables both of which contain matching data in columns.

A properly constructed database table must have a primary key field; a field which stores a unique identifying value in each record. To implement a relation, the primary key field of table A is placed in Table B, providing a column of values in table B which have matching values in table A. The placement of the primary key field from Table A as a column in Table B, creates what is known as a Foreign Key field in Table B. In 4th Dimension, relations are implemented by drawing a relation arrow in the 4th Dimension Structure Editor from the Foreign Key field in Table B to the Primary Key field in Table A to link the two tables. Records are then logically linked based upon common values in the Primary Key field of table A and the Foreign Key field in table B.

In 4th Dimension you can easily implement One to Many relations, Many to Many relations, and One to One relations (rarely used). Relations in 4th Dimension are bi-directional: you can navigate to the matching row or rows of either table from the other table.

The relations you implement are of course dependent on the overall table design of your database. The proper organization of data into table structures is the process known as Normalization. Properly normalized tables make searching, sorting and reporting upon data easier. Properly normalized tables also eliminate the need for redundant data entry, making data entry faster and easier, and eliminating the maintenance tasks that redundant data entry can require. We encourage you to familiarize yourself not only with the implementation of relations in 4th Dimension but also with the principals and process of Normalization by using the resources listed below.

Learning Resources

Implementing the Foreign Key in a Relation
This tech tip covers the use of the Foreign Key and implementation of a One to Many relation in detail.

Primary Keys (Relations Part 1) (QuickTime movie)
Every table must have a field that contains a unique identifying value for each record; the Primary Key. This video tech tip explains the concept of the Primary Key, and its implementation in a Table.

Getting Started: Relations (QuickTime movie)
Illustrates the mechanics of implementing a relation in 4D.

4th Dimension QuickStart Guide (PDF)
Pages 44 – 45: A brief explanation of relational technology.
Pages 45 – 46: A brief explanation of the advantages of relational technology.

4th Dimension Design Reference (PDF)
Pages 154 – 186: An explanation of relations and their implementation in 4th Dimension. Some of the concepts covered are; the primary key; foreign keys; the One Table; the Many Table; setting relation properties; deletion control (referential integrity); automatic and manual relations; entering data in related tables; One to One relations; Many to Many relations; entering, displaying, and reporting on data in Many to Many relations; circular relations; multiple links to the same table; when and how 4D establishes links between records.

Relations
This web page documents the use of automatic table relations with the commands available in the 4th Dimension language.

Commands and Topics for Relations
This web page provides links to the commands available for controlling relations in the 4th Dimension language.

Designing Relational Databases (PDF)
A full hands-on course in database (data structure) design theory.

4D v 6.8 Getting Started
Pages 13 - 36
Table design theory and relations are covered. The topics covered include:
Relations, cardinality, redundancy and segmentation, what is a relation, 7 rules for properly determining the fields of a table.

An Introduction To Database Systems by C. .J. Date
Published by Addison Wesley
The bible of database design theory; 850 pages long, and at times a challenge, but worth the effort.

Database Systems Management And Design by Pratt & Adamski
Published by Boyd & Farser.
A good overview textbook on database systems management and design.