The Relationship Between Databases - File Maker

Let’s explore the mechanics behind a relationship between two databases. An E-R or Entity-Relationship Diagram is a diagram that depicts all of the databases in your database system and their relationship to each other. Figure below shows an E-R diagram for our friends and family database system. The one-to-many relationship between Friends.fp5 and Children.fp5.

The one-to-many relationship between Friends.fp5 and Children.fp5

The one-to-many relationship between Friends.fp5 and Children.fp5

Friends.fp5 is represented here as a rectangle, as is Children.fp5. The forked line that connects the two represents the one-to-many relationship from Friends.fp5 to Children.fp5. Any given friend can have many children, but any child only has one parent in Friends.fp5. (This assumes that you don’t list both parents of a child as separate records in Friends.fp5, a situation that would call for a many-to-many relationship).

The one-to-many relationship between Friends.fp5 and Children.fp5, showing the fields that this relationship is based on

one-to-many relationship between Friends.fp5 and Children.fp5, showing the fields that this relationship is based on

Figure above is another way of looking at the relationships between these two databases. In it you can see the field that this one-to-many relationship is based on, which is the FriendID field. (You were introduced to the concept of an ID or serial number field earlier.) The FriendID field exists in Friends.fp5 to uniquely identify each friend in the database. In fact, each time you create a new record in Friends.fp5, the FileMaker database automatically enters the next sequential serial number into this field for the new record.

Primary Keys
The FriendID field is known as the primary key of Friends.fp5, which means that this is the field used to uniquely identify each record in a given database. In this case, it is the main field used to relate a friend record in Friends.fp5 to a set of records in the Children.fp5 database. (You could use the combination of someone’s first and last name as the primary key in a database, but if you did this, luck would have it that eventually you would acquire two friends named Rupert Pupkin and then this primary key would no longer be a unique identifier.)

Note that because FriendID is the unique identifier for a record, it is a big problem if somehow two or more of your Friends.fp5 records end up with identical FriendIDs. If this were the case, the records with the same FriendID could not be uniquely identified. Thus it is usually best to allow your database to automatically generate unique IDs. They never even have to show up on the screen (the numbers are usually meaningless to a user, anyway), but could simply exist in the background to maintain the integrity of your database relationships.

Foreign Keys
FriendID also exists in the Children.fp5 database. In this database, FriendID is called the foreign key because it matches the primary key in another database (Friends.fp5). Children.fp5 also has its own auto-entered primary key, ChildID, which you can use to establish relationships from Children.fp5 to another database that you might add to the database system in the future.

Basically, when you enter data into the portal to Children.fp5 on the entry screen for Friends.fp5 (the screen shown earlier for scenario 3) here’s what happens: As soon as you start typing a child’s name into a blank line in the portal, FileMaker creates a new record in Children.fp5, auto-enters the next sequential ChildID value (to make the new Children.fp5 record unique) and also automatically enters the FriendID of the friend record you are currently viewing into the FriendID field of the new child record you are creating in the portal.

In FileMaker, whenever you are entering related data into a portal like this, the foreign key of the related database is always entered for you automatically. It makes sense: In order for the record you are creating in the portal to maintain its relationship to the current friend record, the current friend record and the new related child record must have the same FriendID.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics