Re: Many to Many Relationships

In message <20011203224457.11238.00001101_at_mb-fi.aol.com>, Adam Steiner <ajsteiner_at_aol.comnospam> writes
>I've posted this in comp.databases.ms-access so if you've seen it, sorry for
>the cross-post.
>---------
>Hello,
>
>I'm designing a database, in A2K, and I understand that in relational databases
>many to many relationships can't be directly implemented and that you need a
>junction table. What I am more concerned with is why.
>
>This is my scenario: I have tblCase and tblLawyer. tblLawyer is a lawyer
>that represents either the buyer, the seller, or the bank. Why wouldn't I be
>able to make three attributes in tblCase, entitled SLaywerID, BLawyerID and
>LLawyerID (Seller, Buyer and Lender, respectively) and have each of those refer
>to a specific record in the tblLawyer table. This would be a many to many
>relationship, each instance of tblCase contains multiple records from tblLawyer
>and each Lawyer in tblLawyer can exist in more than one record of tblCase.
> I know how to implement this by using a junction table, but I am more
>concerned with the why.
>Thanks in advance,

You are assuming several things. First, that there are only three roles that a lawyer can take in a case. Second, that each role requires one and only one lawyer. If both of those assumptions are known to be true in every case then your design is fine. If there are ever any exceptions then you have a problem.

-- 
Bernard Peek
bap_at_shrdlu.com

In search of cognoscenti