Join Files

[justcosmic]

Just planning a rework of an old solution, and trying to fully understand the need for join files for "Many to many" relationships...i seem to be able to get the same result with just the 2 files & no join file...i suspect that this could lead to impending disaster at some future point so am trying to better understand the subject. If there are any good articles or tutorials i would love to know...

P.S. is it just me, or is the new FMForums much harder to navigate & search?

Many thanks

Justin

Edited November 8, 2005 by Guest

[Ender]

Many-to-many relationships usually do require a join file. In some situations you can skip the join file by using multi-line keys.

If you can explain more about the entities involved in your relationship, we may be able to tell you what you need and why.

[justcosmic]

Thanks for your response...

...I am putting together a small business solution, but the part I'm looking at right now, is just the contact management:I have been debating whether to just put everything in one file(table) in order to make it more compatible with outlook & my PDA, or whether to use seperate files(tables) for contacts, companies, address' and phone numbers & emails...

...it seems better programming to use seperate files...but i would like to be able to have multiple address' per contact & company, and vice versa...

so far i have tried putting a portal on the contact database to display address info(i guess horizontal portal plugin could be useful there)and a portal on the address database to display contact info. simply using ID keys...it seems to display data...but like i said before I'm not quite sure if this will cause other problems.

...I have also been studying some design tutorials, & was intrigued by the idea of have a "viewer" file...i.e. a file with user profiles & lots of globals & layouts through which everything else is viewed...

....anyway i am getting a bit off track...i simply want to make sure i future proof myself a bit and dont have to redo stuff later because my basic design concept wasn't upto it!

many thanks again for any feedback you're able to give...& if you can point me in the direction of a resource to actually understand the need for the join file in Fmp5 (i realise V7 doesn't need it)perhaps that would help me make wise decisions in this area!

OK....sorry for the very bloated reply...

[Ender]

You would do well to "normalize" your data by using separate files/tables for each entity. Although normalizing increases the number of files/tables used, it reduces the overall number of fields and generally improves the efficiency of the database, in terms of file size, data entry tasks, and reporting. You can read more about normalization in various database design websites or books, the principles are the same no matter what database program is being used.

The entities you mentioned could be arranged in a few different configurations, depending on what's required, but here's a typical structure:

A Company might have many Contacts. A Contact might have many Addresses, many Phone Numbers, and many Email Addresses. So:

Company is related to Contact by Company ID,

Contact is related to Address by Contact ID,

Contact is related to Phone Number by Contact ID, and

Contact is related to Email Address by Contact ID.

No many-to-many relationships are needed for this configuration.

BTW: Although FM7 allows multiple tables in a file, the structural considerations are the same (a join table might be used instead of a join file, but the requirement of a join is still there.)

[justcosmic]

Thanks again for the help...

the specific place where i am thinking i might need to use the join is between contacts and address, where i want to have upto 5 address per contact, and similarly several contacts per address...so should i use a join file in that instance?

many thanks again

Justin

[Ender]

It depends.

If we're talking about shipping addresses or something, where two unrelated contacts might happen to have the same address, but are otherwise unrelated, then no; each contact should have it's own list of addresses (a one-to-many relationship.)

If we're talking about describing the situation where a Company has multiple locations, and we want to select which location(s) each Contact is at, then the structure is a little different. An Address file/table would be used to describe all the addresses for a particular Company. And a Contact-Location join file/table would be used to choose the addresses that a particular Contact has at that Company. There are two advantages of the join table here: First, if the Company's addresses are already in there, then the address data entry when adding a Contact is very simple; simply select the location from a pop-up list or portal. Second, a change to a Company's address would only require changing the address once.