Relationships Between Tables in Base

Hello students, welcome to Kwickprep. A school stores students in one table and fee receipts in another. How does Base know which receipt belongs to which student? Today we will learn why data is split, the three kinds of relationships, how to create them in Base, and referential integrity.

Imagine one table holding students and their fee receipts together. Riya pays fees in the first term, so her name is typed. She pays in the second term, and her name is typed again. She pays a third time, and it is typed once more. Aman's details are repeated in the same way for every receipt.

One big table causes three problems. Repeating the same data again and again is called redundancy. It wastes storage space and invites typing mistakes, like Riya Shah in one row and Riya Sha in another. If Riya's name changes, we would have to correct every row. So we split the data into a student table and a fees table, and link them.

Here are our two tables. The student table has admission number and name, and its primary key is admission number. The fees table has receipt number, admission number and amount, and its primary key is receipt number. Admission number appears in both tables, and that is the link.

Two keys make the link work. A primary key is a field whose value is unique in its own table. A foreign key is a field in one table that refers to the primary key of another table. So admission number inside the fees table is a foreign key. The table with the primary key is called the parent, and the table with the foreign key is the child.

A relationship tells how rows of two tables match. In one-to-one, one row matches exactly one row, like each student having one locker. In one-to-many, one row matches many rows, like one student having many fee receipts. In many-to-many, many rows match many rows, like students and subjects, because a student takes many subjects and a subject has many students.

One-to-many is the relationship you will use most. One student row can match many fee rows. But each receipt belongs to just one student. In Base, the line shows 1 near the parent and n near the child, where n means many. In a one-to-one link, both ends of the line show 1.

Base cannot link many to many directly, so we add a middle table, often called a junction table. Here, the enrol table holds pairs of admission number and subject code. Riya's row pairs her with IT in one row. Another row pairs her with Maths. So one many-to-many becomes two one-to-many links.

Now let us create the link in Base. First, close every open table, because Base cannot change open tables. Choose Tools, then Relationships, and the Relation Design window opens. In the Add Tables box, add student and fees, then click Close. Drag admission number from student and drop it on admission number in fees. A line appears, showing 1 at student and n at fees. Save the relationships with Ctrl+S.

Check a few things before you drag. Both fields must have the same field type, such as Integer and Integer. The field in the parent table must be its primary key. The field names may differ, but using the same name is much clearer. To edit a relationship, double-click its line, and to remove it, select the line and press Delete.

Now an important idea, called referential integrity. It is a rule that keeps the links between tables correct. Every foreign key value in the child must exist as a primary key in the parent. So you cannot add a fee receipt for admission number nine nine nine nine if no such student exists. Base enforces this rule as soon as the relationship is created.

Double-click the relationship line to open the Relations dialog, which has update options and delete options. No action blocks the change if child rows exist, and it is the default. Cascade passes the change on, so deleting a student also deletes that student's fee rows. Set null empties the foreign key in the child rows. Set default puts the field's default value there instead.

Pause and predict. Student and fees are linked one-to-many, with no action selected. Riya, admission number one zero four five, has three fee receipts. You try to delete Riya from the student table. What does Base do? It refuses and shows an error, because the three receipts would point to a student who no longer exists.

So why does this rule matter? It prevents orphan records, which are child rows whose parent is missing. It keeps totals and reports correct, because every receipt belongs to a real student. And it catches mistyped admission numbers the moment they are entered.

Let us revise what we learned today. We split data into tables to reduce redundancy, then link them by a common field. Relationships can be one-to-one, one-to-many or many-to-many, and many-to-many needs a middle table. In Base, choose Tools, Relationships, and drag the primary key onto the foreign key. Referential integrity makes sure every foreign key value exists in the parent table. Try linking two tables of your own in Base.