Ternary relationship?

[Icehousman2]

Ok, I have a problem. I am creating a customer database, with the tables tblRep, tblAgent, and tblOffice. Each agent is assigned to one rep and one office. However, each office can have more then one rep and each rep may have more then one office. Now after saying that, in general an office will only have one rep. However, in some cases an agent from a particular rep will be related to an office that belongs to another rep, hence the many-to-many relationship. When this happens, this is considered an exception and I need to track this. So, I tried to create a junction table called tblException that included all the primary keys from the three main tables, and an extra field ‘exception’. However, when I try and create a new office I am unable to relate this to the rep table, because I do not have the agentID to create a record in the table tblException. The agent may or may not be added at a later date. So I have a problem. It seems to me that I have this set up wrong. Anybody have some advice? I’ve included a screen shot of the relationships. And if you are wondering officecode is part of the primary key in both the tblagent and tbloffice because of updatability between separate instances of the database.

 

[Pat Hartman]

The relationships are not correct.

Remove:
relationship between the agent and rep tables
relationshp between agent and office tables

RepID from the agent table
CustID from the agent table
OfficeID from the rep table
agentID from the relation table
Exception from the relation table

Add:
a new autonumber RepOfficeID to the relation table and make it the pk
Add a unique index on RepID and OfficeID to the relation table to prevent duplicates.
Add RepOfficeID to the agent table.
Create a new relationship between the agent table and the relation table

Change:
Name to OfficeName in office table. Name is the name of a VB and VBA property and will cause problems when used in VB/VBA.

To find exceptions, run a count query on the relation table that counts RepID and select those with a count > 1.

 

[Icehousman2]

Thanks Pat,

I think you pointed me back in the right direction, but I still have some questions. I think I can now delete officeID from the Agent table, but am not sure if I’m missing something? Also I’m not sure how counting repid in the relation table will allow me to pick out the exceptions because a certain repid could be in the relation table hundreds of times without a single exception. I think I still might be missing something. I’ve included a new picture of the relationships after the changes. Thanks for the help. It is greatly appreciated.

 

[Pat Hartman]

You need to remove the relationship between rep and office and remove repID from the office table. repID is related to office in the relation table which you have calledtblExcept. The way you have it now, an office can have only one rep. I know you said that that is the norm but since more than one is possible, you need to use ONLY the relation table to identify the reps for an office. You don't want to create multiple queries, etc because sometimes an office has one rep and sometimes it has more. To count the exceptions, you just run a totals query:

Select RepID, Count(*) as OfficeCount From tblExcep
Group by RepID;

RepID exists in tblExcep once for each office he is associated with.
You still need to remove OfficeCode from tblAgent. Why does tblOffice have a two part pk? If OfficeCode is an old code that you need to retain for compatability, remove it from the pk and add a separate unique index. As you have it now, it can be duplicated.

 

[Icehousman2]

Ok, I may be confused or maybe I’m being confusing. Please bear with me. In the first response you mentioned that I need to make officeID and repID unique indexes in the relation table, which I assume means indexing the field and choosing no duplicates. However, there will need to be duplicates of both fields in this table. So, I indexed them but chose duplicates ok.
As for the officeCode field in both the tblAgent and tblOffice tables, I use that field to merge information with other identical databases (structurally). (The office code is different for every office in which the database is held, not necessarily ever workstation.) That static number, in combination with the primary key, gives me a unique number that I can then use to merge info with the other databases.
Also, just so you know what the end result needs to be. I need to be able to create a report for each rep (the users of this database) that will show them all of the exceptions for their offices. So, an office should be “owned” by a Rep. But, another rep may enter an agent under that particular office, only as an exception. So, as I see the database set up now, when I enter an agent as an exception. The exception table contains a record that includes a unique number to relate to the agent table. The rep that is assigned the agent. And the office that the agent is assigned to. It in no way relates the rep that “owns” the office to this record. So, I don’t see how I could create such a report as described above, with this design.
Thank you for all your help. I don’t know what I would do with out this message board.

 

[Pat Hartman]

Add a unique index on RepID and OfficeID

That means a single index which would be compound since it contains TWO fields. To create a compound index open the index window. The sample picture shows the Switchboard table. It has a two field primary key. I added a two field unique index. The name of the index appears on the first line and is then blank on subsequent lines. Jet supports indexes or primary keys containing up to 10 columns. Other RDBMS' have higher limits.

As for the officeCode field in both the tblAgent and tblOffice tables, I use that field to merge information with other identical databases (structurally). (The office code is different for every office in which the database is held, not necessarily ever workstation.) That static number, in combination with the primary key, gives me a unique number that I can then use to merge info with the other databases.

If the OfficeID is not unique, then the OfficeCode needs to be in the exception table along with the OfficeID, not in the agent table.

If you want to keep RepID in the Office table to indicate ownership, I can live with that (and you should create a relationship from office to the rep table if you do this) but you still need to add a row to the exception table (I would change its name for this reason) even when there is only one rep for the office. I believe the "exception" table should be the table that managea ALL the office/rep relationships to the agent. Try putting some data into the tables to see how it works. It is a much simpler relationship than you started out with and I think it satisfies the business rules.

 

[Icehousman2]

Thanks for all your help Pat. I was obviously more then a little confused, but you really straightened me out. It all seems to be working great. I did keep the relationship between the rep and office tables. Hope that doesn't cause me problems later. I don't think it will. Thanks again.

 

[Pat Hartman]

It will be ok ONLY if you use it ONLY to determine the primary rep for an office.