Need Help Understanding Match Fields

[GarrettC]

Although I am not new to FileMaker I am still in the weeds on some of the finer points of relationships. I have a fairly good book that has gotten me quite a ways in constructing my database but it is advanced and some knowledge is assumed and of coarse this is the hole where my question is lurking.

When you create a Primary key field in one table and then need it to be a foreign key in another table (match field?) do you need to create a field in the other table to make the relation between the 2 tables? I know this seems really obvious but the book blew right over this issue.

I have noticed that that is what many people have doen here when I have downloaded some of their examples. But I am onsure the best practice for this and good naming conventions.

If I use a simple example: Company table and employee table, I have an employee ID field in the employee table, I would need a cooresponding field in the other table right? How should I create this and name it?

Thanks for the advice.

PS I find FileMaker the oddest program to learn, it seems that the most complicated stuff is easier to pick up than the most commonplace.

[LaRetta]

Hi Garrett,

Assuming there is one company with multiple employees:

Company table:

Primary key: CompanyID

Employee table:

Primary key: EmployeeID

Foreign key: CompanyID

You wouldn't need the EmployeeID in the Company table. The relationship would then be based upon the CompanyID. It can become more complex than 1:n (one-to-many) where you have n:n, but this is the basic idea.

LaRetta

[Fenton]

>Company table and employee table, I have an employee ID field in the employee table, I would need a corresponding field in the other table right? How should I create this and name it?

Not the EmployeeID. You would have the CompanyID in both tables. Because an employee only has 1 company,* but an company has many employees.

*If employees can belong to several companies (unlikely), then you'd need a "join" table between the 2 existing tables. The join table would then have 1 record for each unique companyID & employeeID combination (2 fields). And there would be no CompanyID in Employees; the join file would handle that connection(s).

FileMaker relationships are really not different from any other relational database. It's not an application-specific thing. It's an implementation of mathematics, set theory (which I vaguely remember from high school; a long time ago). How operations using relationships are implemented is very application-specific however.

[GarrettC]

Thank you both for replies but you might both be overshooting the mark with me. This not a relationships question per say but a question of interface mechanics. I used the example as a way to illustrate my problem I am actually building an asset/content mangement system (I'm actually building 3).

Let me restate. I know that to insure a unique record it is good practice to have a Primary key in one table (A) and then have that number appear in another table (: that is relating to the former table (A). What I am asking is: do I have to create a field in table (: to match the field in table (A)?

And if I need to do that, do I just create a field like any other new field or is there some unique way this should be done? And is there a recommended naming practice when you create these match fields?

Thanks for your imminent patience...

[comment]

do I have to create a field in table (: to match the field in table (A)?

Yes. In LaRetta's example above, there are 2 fields named CompanyID. One of them is in the Company table, and is the primary key there (auto-enter serial number). The other one is in the Employee table, and serves as a foreign key to the Company table (a regular number or text field; it doesn't matter much if it is number or text, as long as both match fields are of the same type).

[GarrettC]

Thanks! Finally I get it. LaRetta's example did show that, for some reason someone else had to point that out to me.

Is it common practice to have the foreign key be the same name as the primary? I know it can be anything but I am trying to have this project make sense to someone else in the future. I figure if I am going to learn this it should be the right way.

[comment]

With so many users of FMP, common practice is difficult to ascertain (or even define). I believe you will find different approaches even here on the forum. I do it the same way as LaRetta's example - except my tables are named Companies and Employees (plural). Others prefer attaching a pk/fk prefix/suffix to the name, and so on. There is no one right way - whatever works for you is right.