Multiple Practice Groups for One Person

[Rusdude]

Hello,

I'm creating a simple relational database of all the attorneys our company work with. I'd like to have three tables: "Law Firms" (firm name & other info), "Attorneys" (what firm, personal info, and what practice groups s/he is involved in), and lastly "Practice Areas" (all different practice areas with codes).

Now, my issue is that people are usually involved in multiple practice areas. Thus, I can't just put a code for one practice area in "Attorneys" table, but I can't put multiple codes in either in one field (right?) because it'd mess up the relations.

What I'd like to accomplish is for people to be able to go to our intranet and, using a form (?), select a law firm and a practice group and get a list of attorneys.

Does anyone know of a good way to solve this? It doesn't have to be pretty, since the DB isn't going to be huge (2000-2500 records).

Thank you!

 

[cheuschober]

It all comes down to designing good primary keys.

I can't tell from your post if the Firm is part of the primary key for an attourney. Is it possible, in your db, for the same attourney to work for multiple firms? If it is there is a slightly different issue that needs to be addressed but one similar to this.

What you're describing is the many-to-many relationship.

You need a fourth table with the pk from your attourney table and the pk of the practice code. Both of these fields then become the pk for this table.

Each attourney may be then listed as many times as s/he has unique practice areas.

Regards,
~Chad

 

[Rusdude]

I'm just using AutoNumber feature for PKs for all 3 tables. And, fortunately, it's not possible for one attorney to work at multiple law firms.

You need a fourth table with the pk from your attourney table and the pk of the practice code. Both of these fields then become the pk for this table.

Is that what's referred to as junction table?

Many thanks,
~ Ivan.

 

[wazz]

Is that what's referred to as junction table?

yes. or composite, or intersection...

tblAttorneyPracticeArea
AttorneyID (FK)
PracticeAreaID (FK)

PK = AttorneyID & PracticeAreaID

[sry for butting in...just passing through...]