Tables can be related to each other - this is done with relationships. For example, a student may have a teacher. We say that the students table is related to the teachers table.
Xbase relationships work completely differently to relationships in other RDBMSs. Relationships are managed in Xbase, which means the specifics are handled by Xbase, not the user. The user just tells Xbase what they want instead of how to do it. This means you can create structures that are completely impossible in other systems.
Types of Relationships
There are three types of relationships:
- One-to-One relationships relate one thing to one other thing. For example, a computer has one mouse, and a mouse has one computer.
- One-to-Many relationships relate one thing to many other things. For example, a headteacher has many teachers, but a teacher only has one headteacher.
- Many-to-Many relationships relate many things to many other things. For example, a student has many teachers (for Maths, Science, English, etc), and a teacher has many students in their classes.
In addition, relationships can be inverse or one way. Inverse relationships are the most common, and work in both directions. One way relationships are uni-directional and come in two types: to-One and to-Many.
Setting Up Relationships
A relationship is made between two different fields of type "relationship". They cannot be made between fields of any other type. Use the table design view to create these fields.
Once relationship fields have been created, relationships can be edited by selecting the View > Relationships menu item.
Each table in the database has a corresponding box in the relationships view. Each box has all the fields of type "relationship" in that table.
To add a relationship, click and drag from one field to another field. Select the newly created line to change the source, destination, relationship type or to make it one-way. To delete a relationship, select it and press delete.
Using Relationships
Relationships can be used in either table data or form data views. In both cases, the usage is almost the same.
Open one of the related tables in table data or form data view. Select a record and press edit to add related records to it for that field. You are now editing the related subtable. This shows all records that are related to the record you just pressed edit on. For example, if you edited the "teacher" relationship field of a student in the Students table, you can now add a record from the Teachers table. This will set the student's teacher.
When editing a subtable, two new buttons become usable. The "Insert" button does not create a new record, but adds an existing record to the subtable. The "Uninsert" removes the selected record for the subtable but not from the database.
The add button both creates a brand new record and inserts it into the subtable. Pressing the delete button and then "Delete from Database" permanently removes the record from the database completely.
At the bottom of the view is the path bar. This shows the path of edit buttons you took to get to the subtable you're at now. You can click on items in the path bar to return to those subtables.
Examples
The sample database has relationships set up. You can access the sample database by selecting Help menu > Open Sample Database.