Most likely a beginner question. I just realized that I link my tables without really thinking about what is the best key. I just realized I wouldn't know how to determine that. Look at the following picture; what should be my criteria to decide whether to join based on the teacher, the installation or the activity key?
I guess the one to many indicator could provide a hint..?
Gosh no! The one-to-many indicator is a cardinality indicator. FileMaker deduces the cardinality indicator based on your field definitions: is it a unique field; is it a global field; etc. If there are mistakes in the field definitionsā¦ then the cardinality indicators should be a hint that there are errors.
What fields should you use? It depends on the relationships between the tables. An entity relation diagram (ERD) can help here because the relationship between entities, typically base tables in FileMaker's relationship graph (RG), and cardinality are described both ways.
Simple example: Person owns Things; Person owns zero or more Things; Thing belongs to Person; Thing always belongs to one and only one Person.
Assume that Person and Thing each have a primary key, a key that uniquely identifies each record in its respective table. Thing can only have one owner, a Person, so it can have a foreign key that says what Person is its owner. Person can own multiple Thingsā¦ so it shouldn't have a foreign key that says what Things it owns.
NOTE: FileMaker can hold multiple keys in a field and they will all link to their respective records. That's an oddity in the database world as, generally, a field can only hold one information. I usually discourage the use of multi-key fields but that's just me. Others encourage it.
It should now be fairly obvious which fields to use to link the two tables: Thing::PersonForeignKey to Person::primaryKey. The answer won't always so simple. It depends on table design and the relationships that need to exist between tables. This can easily be a course in and of itself.
Keep in mind that while the above ERD looks an awful lot like an RG, ERDs are not RGs. ERDs can make circular definitions. RGs can't. RGs display table occurrences. Entities in ERDs more closely resembles base tables. ERDs usually deal only with conceptual details. RGs, on the other hand, need to deal with implementation details such as security and performance. And so onā¦
Hope I didn't discourage you and hope this helps.
2 Likes
Your relationship should be based on the simplest criteria required to describe each instance uniquely. That is usually achieved by having an ID field in each table which stores a unique value. The ID fields are then used to generate links between two tables. Your screen shot shows the most common situation, which is a one to many link between a primary key that is unique in that table, and a related key (often called a foreign key) which can occur more than once in the table.
As @bdbd mentioned, the relationship graph is not an entity relation diagram. In FileMaker relationships between tables are equivalent to "views" in SQL. They embed a set of criteria that filter the records in complex ways. For instance, you may want Teachers to see Activities that match an Installation; or that match a date range. To do that you'll need to create relationships that express those criteria.
Looking again at the 3 tables above; a teacher can teach many activities so I should see the one to many indicator in the other direction; does that suggest there is something wrong in my field definition?
Not sure I understand that logic. Although I can understand that only one record is needed to express the relationship, in the thing table, if it is the owner id that is used in the thing record; in the owner table if we use the thing Id to relate them, you need to find all records and filter on owner to list all records related to it.
But following that logic, activityID should be the match key with installation as well. Yet, why does using installationID as the match key feels more natural?
Installation to activity is correct: one installation can host several activities. On could argue the opposite is also true; when I was a massage therapist (teacher), my assignment specified the client, the room (installation) and the technique e.g. shiatsu (activity). The room's characteristics/equipment would determine the range of techniques that could be provided there.
Does that mean that a join table "Assignments" is needed to link all other 3 properly?
Yup!
Let's forget FileMaker Pro for a second and instead focus on general database concepts.
You could put the Thing foreign key in the Person table. It does answer the of "who owns what" question. How many Things could a Person own in this case? Once a Person owns a Thing, it can no longer own additional Things. The only way to work around this is to duplicate the Person record to be able to put a different value in the Thing foreign key. Now you have data duplication and a mess on your hands.
Now flip things around. A Thing can only ever have one owner if co-ownership is prohibited. It also must always have an owner according to the example model. That's why it makes sense to have the Person foreign key in the Thing table. It answers the "who owns what" question and multiple Things can each be owned by the same Person.
Relationships in Entity Relation Diagrams (ERD) and in FileMaker relationship graphs (RG) are bi-directional. That's true even if you organize table occurrences using the Anchor-Buoy method in the RG.
Layout context determines the relationship direction FileMaker uses and the available related tables during runtime. If the context is Person, then the Person primary key will match zero or more Person foreign key in Thing records. The relationship will therefore return zero or more Thing records, each representing a unique Thing. If the context is Thing, then the Person foreign key will match one and only one primary key in Person records. The relationship will therefore always return one Person record.
Figuring out which keys to create and use isn't a matter of feeling. It is a matter of logic andā¦ you are going to find me harsh hereā¦ you aren't following the logic correctly. Ask yourself these question:
- What is the relationship between Activity and Installation?
- Does an Installation offer or host an activity (or it is something else)?
- Does an Activity take place in or is initiated in an Installation (or something else)?
- Can an Activity take place in one or in multiple installations?
- Can an installation offer only one or multiple activities?
- Put another way, is there a one-to-one, one-to-many or many-to-many relationship between Installation and Activity?
I can only speculate to the above answers so I can't say which keys are best. Answer questions like these and you should be able to figure out the keys and intermediate tables (join tables, for example) needed solve pretty much any data modelling problem space.
Hope this helps.
1 Like