Creating many-to-many joins 2

[reutev]

I am designing a small access database for a law firm, I have two tables as of now, a Client and a Trustee. How can I design the relationships so that one Client can have many Trustees. Also, one trustee can have many clients. I want all these Clients and Trustees to have one unique ID. Putting two one to manys back and forth creates Trustee_1. Entering info still needs multiple IDs. Any advice would be most welcome, thanks, Vince

 

[SBendBuckeye]

You need what is called a junction table. Create your client table with a ClientID and your trustee table with a TrusteeID. Then create a third table called ClientTrustee or some such and put 2 foreign key fields in it, ClientID and TrusteeID.

Then create a one to many relationship from the Client table into it via ClientID and a one to many relationship from the Trustee table via TrusteeID. That will allow you to process from either side. When you are done, the one relation ship should be on the Client and Trustee tables and BOTH many relationships should be on your ClientTrustee table.

Good Luck!

 

[reutev]

Thank you Buckeye, I know exactly what you mean. This is what I have tried, but it gives me a one-to-one relationship from the ClientID(Client)to ClientID(ClientTrustee). From TrusteeID(Trustee) to TrusteeID(ClientTrustee)I get the desired one-to-many. Why can I not do both? Am I setting up the third table wrong? I am not declaring any primary keys in it, I shouldn't right? Thanks again for any help, Vince

 

[mikey69]

You should design the junction table so that it has a non-significant primary key and then check that both of the other fields are indexed to allow duplicates.

 

[SBendBuckeye]

Also, when you create your relationships make sure that you always click first on the one side of the relationship. So in the cases above, you would click first on ClientID on the Client table and then drag through the ClientID on the junction table and then repeat the process for the Trustee side by starting with the TrusteeID on the Trustee table.

Good point also by Mikey69 about the insignificant key.

Good Luck!

 

[r937]

i would respectfully like to disagree about the insignificant key

there are only two reasons why you'd need one

in most cases, a junction table simply manifests a many-to-many relationship between two other tables, and as such, it is the child in two separate one-to-many relationships

so the first reason for a separate key is if the junction table is going to act as the parent in a one-to-many parent-child relationship of its own, to a fourth table, not one of its two parents

the second reason for a separate key is if you want to allow multiple occurrences of the same combination of values of the junction table's two foreign keys -- then, yes, you need a separate, surrogate primary key

do you have multiple occurrences of Client X related to Trustee Y? this might actually make sense if each relationship has start and stop dates

if neither of these conditions prevail, then the primary key of the junction table should be a compound key consisting of the two foreign keys

otherwise, the extra "insignificant" key is useless, and, in fact, it's a drag, since it has to have its own index

see Joe Celko's article "Hollywood Couples" at

http://www.intelligententerprise.com/010101/celko.shtml

rudy

http://rudy.ca/

 

[reutev]

Thanks guys, all is well!

Vince