Question about joins and relationships

[lanceomni]

I have a DB containing

JOBS

CONTACTS

BUSINESSES

One JOBS can have many CONTACTS

One JOBS can have many BUSINESSES

One CONTACT can have many JOBS

One CONTACT can have many BUSINESSES

One BUSINESS can have many JOBS

One BUSINESS can have many CONTACTS

Several people have recommended that I combine contacts with businesses however I would prefer to keep them separate if at all possible.

My old DB used joins. For example the following

Table:

JOBS

JOB_Contacts

CONTACTS

Along with a second table occurrence of CONTACTS labeled JOB_Contacts_Contacts

This seemed like over kill but im not sure the best way of going about it.

[comment]

Going by your description alone, I'd say you need a join table in-between every pair of your core tables.

However, it depends on what is the purpose of tracking all this information, and what does a join represent in real life. For example, if a contact is associated with a business only through a specific job, then a single "star" join table might be better suited.

[lanceomni]

Thank you for your quick response

Well JOBS would be the primary purpose for this DB however I may create a record of a business and several records of contacts for that business who are not associated with a job.

The issue I run into is that when I create the join tables I make a full loop and I get an error.

I will upload the basic table structure so you can see what i am talking about.

Its been a while since I have used FM but it is coming back to me quickly.

My previous DB is becoming a bit dated and I hate adding fields such as Phone 1 Phone 2 and so on. This new DB may be a bit over kill but Id rather cover all the bases.

PhotoDB.zip

[comment]

I'm afraid I cannot analyze your file in a reasonable amount of time. In general, to implement multiple join tables between the same set of tables, you need to use multiple occurrences of some tables.

The exact implementation depends primarily on your intended workflow, IMHO - but this is a highly controversial subject in which personal preferences can play a large role - see (for example)

http://developer.filemaker.com/content/technet/pdf/approaches_to_graph_modeling_en.pdf

[lanceomni]

No problem I appreciate your help. I will follow up with an explanation of my fix and/or the direction I took.

[aislinn]

I agree with comment. A while ago, I had the same problem: relationship spaghetti (basing all relationship on a single set of linked table occurences).

You first need to clean up your relationship graph and implement best practices, such as trying to set up a different relationship set for each "point of entry table" (the table on which the layout you're using is based).

After that, you should get a much clearer picture of what needs to be done. It definitely helped me loads.

[bruceR]

You have multiple instances of unnecessary duplicated TOs at the right end of the graph.

Why two instances of Locations?

Why two instances of web pages?

Why two instances of email addresses?

etc.

[lanceomni]

I am still reading the PDF that Comment (The user named Comment) provided a link for which explains database relationships.

I am updating an older database because I don't want to keep adding additional fields for data that should be in its own table.

[color:red][Jobs] this table which is the main focus of the database. A job can have one or several structures and addresses related to it.

[color:red][structures] this stores information about a particular building including an address and contacts.

[color:red][Locations] this stores address information for the [Contacts], [businesses] & [structures] tables. I kept running into situations where I needed to add additional addresses for a contact and was tired having to create new fields (Address1, Address2)

[color:red][Contacts] contact information stored. Does not necessarily relate to any [Jobs]. It can have many [businesses], [Locations], [PhoneNumbers]...

[color:red][businesses] business information stored. Does not necessarily relate to any [Jobs]. It can have many [Contacts], [Locations], [PhoneNumbers]...

It may seem like a bit over kill however I want to store information such as addresses and phone numbers in a separate table to eliminate redundant entries of information and to keep from having to expand fields in the future.

I know there is an easier approach to this while still allowing me to relate these items.

Any good reading such as the PDF posted in the reply above, an example DB where relationships like this exist or a helpful nudge in the right direction would be wonderful.

I have attached the latest version of my DB. I know its probably going in the wrong direction.

ArchPhotoDB.zip

Edited June 14, 2009 by Guest

[bruceR]

I'm not disagreeing with your design intent; but your previous had some unnecessary duplication.

But I see that you have eliminated the duplicated table ocurrences.

See attached for some appearance mods to the graph.

ArchPhoto_DB_0.3.1.fp7.zip

[comment]

I am still reading the PDF that Comment (The user named Comment) posted which explains database relationships.

Just to clarify: I am not the author of the document.

[lanceomni]

@Comment: I edited the post to clarify the situation. It was a good read. Thanks

@BruceR

So far this method is working fine however now for example if I want a portal in [PhoneNumbers] which relates back to multiple [Contacts] I can't as the relationship is with [Join Contacts_PhoneNumbers] and not [PhoneNumbers].

I could create a relationship or eliminate the join occurrence however somewhere down the line I would come full circle and FM will not let you do that.

I could have a layout based on [Join Contacts_PhoneNumbers] instead of the main [PhoneNumbers] table but that would cause problems too.

Any suggestions as to get around this or should I go in a completely different direction?

[lanceomni]

Okay if anyone is still following this post I have a new idea.

Lets say [Jobs] is my master table. Each [Job] can have many [Contacts] each [Contact] can have many [Locations]

[Contact] can be individual or business and a client or not a client. Ill have all of the fields for these and will just create separate layouts which hide non relevant information (So if its an individual it will hide all fields dedicated to a business.

I can create relationships between contacts and then just do the sorting in the portal as to if its a business or individual.

Does this seem like the right path to take? I will start cooking an example file up.

Best Regards,

Justin

[lanceomni]

I have redesigned my tables and relationship graph combining several tables to simplify things.

As of now I have [Jobs] < [Contacts] < [Locations]

A contact can be an individual or a business. I have a field signifying this. My idea was to have separate portals in [Jobs] displaying only those which are businesses and those which are individuals.

A Business [Contact] can have several related [Contacts]

When I create a portal or use a join table to reference these relationships I get multiple references to the same contact. So John Doe gets:

John Doe

John Doe

John Doe

three times in the portal.

In my last try

I had:

[Jobs] - basic info, can have many CONTACTS, BUSINESSES & STRUCTURES

[Contacts] - Can have many related BUSINESSES

[businesses] - Can have many related CONTACTS

[Addresses]

[structures] - Basically a single ADDRESS with detailed information for the photo shoot.

I wanted the capability to have portals in most of the layouts for these tables which would reference back to their relationships.

[color:green]040 is the new version with the slimmed down tables.

[color:red]031 is the old version with all the extra tables

ArchPhoto_DB_040.zip

ArchPhoto_DB_031.zip

Edited June 17, 2009 by Guest
merged topics

[lanceomni]

SOLVED by strictly following the anchor-buoy method.

[bruceR]

SOLVED by strictly following the anchor-buoy method.

Not necessary. Just display a portal to Contacts on the Phones layout.

Edited June 20, 2009 by Guest

[bruceR]

@Comment: I edited the post to clarify the situation. It was a good read. Thanks

@BruceR

So far this method is working fine however now for example if I want a portal in [PhoneNumbers] which relates back to multiple [Contacts] I can't as the relationship is with [Join Contacts_PhoneNumbers] and not [PhoneNumbers].

You're making this too difficult. Just display a portal to Contacts. You already HAVE the relationship. Nothing else is needed.

See attached.

ArchPhoto.zip

Edited June 20, 2009 by Guest