Trying to determine if I need a multi-key vs. many-to-many relationship

[James Gill]

I've recently been struggling with a database solution to track calls and pricing information. I'll try to briefly sum it up:

I have 3 main tables; a, b, and c. Table "a" represents a geographic location (usually a city) that my users will be contacting business entities within. Table "b" represents the business entity that the user is contacting. And table "c" represents pricing information that was sent to the business entity.

The way that my relationships currently work is that entity "c" relies on entity "b" and entity "b" relies on entity "a". Entity "a" is used to tally and track a report for all of the calls/pricing information being sent in one area. The problem that I'm having is that occasionally there will be calls made (say they are making calls in a suburb of San Francisco) where I need to be able to track the not only the original call that went out but also another call to same "b" entity from a different "a" entity. Right now because "b" relies on the primary key field of the "a" entity to tally the report I can only have one "a" entity per "b" entity and my report is innacurate.

Before filemaker was used to tally this data I was using what amounted to a multi-valued cell in excel to track this information. It worked pretty well in excel but now I'm trying to decide what would work best in this situation to resolve my dilemma in Filemaker. The Filemaker training manual strongly advises against using multi-valued attributes but I'm having a tough time seeing how a many-to-many relationship solves my problem.

If I didn't do a very good job explaining this problem I apologize and will try to further clarify it, if requsted.

[Ocean West]

From the way I read it 'b' can have appear in multiple 'a' locations?

I'd suggest a join table between 'a' & 'b'

[David McQueen]

From the way I read it 'b' can have appear in multiple 'a' locations?

I'd suggest a join table between 'a' & 'b'

Carrying one from there, b can also have multiple c's - Suggest a table d - a join of all three. Then using self relations one can see multiple locations, multiple contacts or multiple price lists for one b.

It just seems that that would present the greatest reporting opportunity. If I am reading things correctly, it would also represent all the items involved in a communication event and therefore would be a fairly good entity representation.

Edited February 16, 2010 by Guest

[James Gill]

Ok I guess I'm having a hard time conceptualizing this in relational form. I'm looking at the Filemaker training series for building many-to-many relationships and to me it seems as if the big downfall of a many-to-many relationship between the records I want to build is that I could only view once instance of A-B occurring at a time.

If I'm tying the different records together in a join table by using the primary key values of child tables then I can only view one join table record at a time, or am I conceptualizing this all wrong?