need help on simple ER design

[bsacco]

Currently I have a relational DB made up of CONTACTS and COMPANY. Both files have key fields such as Company ID and Contact ID in both files that are related to each other. Now that's simple enough...right? Well, When I needed to add a third relational file called PROPOSAL using Company ID and Contact ID as the key fields that will match with the other files, it didn't work.

Can someone tell me what I did wrong.

[Ender]

Tell us about the way the data works (it's not clear from your key fields alone.) Does each Company have one or more Contacts or does each Contact have one or more Companies? Contact databases could be set up either way depending on what you are tracking. If it could be both, then you should change your database structure.

Now about the Proposal file. Do you want a proposal to be linked to a Contact that works for a specific Company, or is the proposal simply related to a Company alone?

[justcosmic]

I believe what you need, is 2 match fields in the Proposal DB: 1 relating to contacts & the other to company. You then have fields to display the data from those related files, which either are related file fields(depending on the relevant relationship) or "lookup" fields.Either way, when your contact id for example, matches the id of a record from the contacts file, the data from the related file should be displayed in the layout you've made in the current"proposals" file....you can display 1 field from that file, or all, depending on what you put on the layout. Hope that is helpful in solving your problems

[bsacco]

My solution includes 3 Files:

COMPANY (Relationship: (COMPANY file) Company ID :: Company ID (CONTACT file)

CONTACTS (Relationship: (CONTACTS file) Company ID :: Company ID (CONTACT file)

and Relationship: (CONTACTS file) Company ID :: Company ID (RELATIONS file)

RELATIONS (Relationship: (RELATIONS file) CONTACTS ID :: CONTACTS ID (CONTACT file)

and (Relationship: (RELATIONS file) COMPANY ID :: COMPANY ID (COMPANY file)

The above is basically it....Now what I want to do is ADD a 4th file called DEAL PROPOSAL. Of course, it will need to relate to the CONTACT(s) and the COMPANY(s)....so, I my guess it's a many-to-many...but I do not understand how to implement it into my solution...anyone have an idea.

Many thanks-bob

[Ender]

You still have not answered my question about which is the one and which is the many in your Company and Contact files. And what is a Relations?

How is the Deal Proposal supposed to work? Can you describe the process?

[bsacco]

Hello,

Thanks for the reply...I'll try to provide the information you request.

"Tell us about the way the data works..... "

First, I'm a salesman whom sells media advertising. I sell to two different types of clients. 1) Clients (IBM, Pottery Barn, K-Mart, etc...) or 2) the advertising agency that handles the advertising for the client.

I need to manage contacts at BOTH the Client and Ad agency. The data related to what Client is related to what AD Agency and contacts that work specifically on that account needs to be tracked.

So, for example, let's say I call Pottery Barn for business. They direct me to Jane Wilson in their marketing department. Jane then tells me that I need to contact Dave Jones at their ad agency (FCB).

Now, after talking to Dave at FCB, he requests that I produce a PROPOSAL for Potter Barn to him.

THe PROPOSAL must be related to the ad agency, Dave Jones, Pottery Barn and Jane Wilson.

I hope this helps...

-bob

[Fenton]

It sounds like you need to enter at least 2 IDs in your proposal file, Client Contact's ID, and the Ad agency's contact's ID. The Client company ID and the Agency ID can be lookups, based on the 2 IDs above. Unless you do one with no contact, in which case you'd just enter the company's ID.

If you look at the file from Clients, you're looking through the Client ID relationship, from Ad agency, through the Ad agency ID.

It's too bad you're stuck in 5, 'cause this would be much easier in 7, which can read "through" intermediate relationships, and it would likely all be in the same file (multiple tables).

[bsacco]

Thanks Fenton,

Matt Petrowsky originally built this for me as a favor way back when (Filemker 4 days)

IF I were to upgrade to FM 7 how would the ER design look like? Would it be a lot easier? I mean could you type it out in an email to me? IF so, I could download a trial 7 ver and try re-building my Sales DB using 7 to see if it will work.

Yeah, I wondered if FM 7 would make it easier, but I've heard that in other ways it's not an upgrade....

[transpower]

The advantage of FM 7 is that you can see the relationships between the tables/files graphically. Lines link the primary and foreign keys. You can also use non-equijoin relationships, as well as equijoin (=) relationships. So for your problem you will be able to see all the tables and see how they're all related.

[Fenton]

I think version 7 is simpler, because you don't have to follow convoluted connections and procedures through several files. Yes, there are multiple tables instead, but they're easier to get to. This is especially true in scripts that cross tables; at least you can see it all in one script.

It really makes a difference when you have, as you do, 2 main entities, each with its own "contacts" sub-entity (psuedo word).

It is a little easier to get confused also though; it's kind of like the relationships of both files become available, but only some make sense. FileMaker tries to let you know which really make no sense, as "unrelated" Table Occurrences (TO), but it's still hard to tell just from the names which is which. That's where the Relationship Graph comes in. If you know where you are (critical), you can see what is available to you.

In your case the complexity would come not so much from the basic relationships, but from those needed to "filter" data entry, value lists for example. Otherwise choosing an Agency or a Client first, then trying to choose one of their contacts is not going to be a pleasant experience, as there could be many total, and you could easily mismatch.

ClientAgencyAds.zip