Table relationships

[sblanche]

My DB has 4 different fields (Attorney, 2nd Attorney, Supervisor, and 2nd Supervisor) that contain the same type of data. Originally I had one table with the names of all 100 attorneys on staff. The contractor I started working with suggested that I might encounter problems if I have 1 table that 4 fields are pulling information from. (The row source is really a query, not the actual table.) So I now have 4 different tables with the exact same list of people. Is this the correct way to do this, or should I go back to one table of names and one query for the row source for the 4 different fields?

Thank you for any help--

 

[lespaul]

No, you should go back to one table with all the names. That contractor should be shot!!

Here's how to do it and stay in a normalized database:

tblCases
CaseID
DefendantID (assumes you have a defendant table with an ID field)
etc.

tblAttorneys
AttorneyID
AttorneyName

tblAttorneyType
AttorneyTypeID
AttorneyTypeDesc

tblCaseAttorneys
CaseID
AttorneyID
AttorneyTypeID


tblAttorney will have all your attorneys.

tblAttorneyType should have:
ID Desc
1 Attorney
2 2nd Attorney
3 Supervisor
4 2nd Supervisor

then tblCaseAttorneys would have the caseID, which type of attorney (1, 2, 3 or 4) and the AttorneyID assigned.

Check out 'Fundamentals of Relational Database Design' for more information!

Leslie

 

[willir]

sblanche

I am puzled just a bit. By supervisor, do you mean the supervisor of a selected group of lawyers - team leads so to speak?

Is this like an employee and manager situation. You have a table for the employees, and the manager references another employee within the employee table?

Regardless, as soon as I see a design showing tblExample1, tblExample2 I start to think about a many-to-many relationship. In your case Attorny 1 & 2, Supervisor 1 & 2.

Is there a relationship between the lawyer and supervisor?

Information is a little sparse, but I will first use a common example, and see it it works with yours.

For scheduling courses at a school, you have a many-to-many relationship between student and courses...

A student can attend many classes.
A course has many students.

In a one-to-many relationship, the "foreign key", the key unique to related record is always placed on the "many" side. On a M:M, you need to create a joiner or intermediary table that contains the foreign key for both tables - student number + course number type of thing.

Looking at your relationship, ask yourself...
- Can a laywer work with many supervisors?
- Can a supervisor work with many lawyers?
(I am not sure if "work with" is the correct question, but you get the idea.)

If the answer is yes to one but not the other, then you have a one-to-many relationship. If the answer is yes to both, then you have a man-to-many relationship.

...Moving on with M:M
The trick with a M:M is creating the joining table. Using the student example, lets call the joining table a roaster table. It would look something like...

tblRoaster
StudentID
CourseID

The two foreign keys would be used to create the primary key in tblRoaster so that a student can only attend one course once.

But the Roaster table can be used to add more info pertenent to student and course info...

tblRoaster
StudentID
CourseID
RoomNo

Now we know which room host the course the student is attending.

Going back to your example, add any fields pertenent to the Laywer + Supervisor joining table.

Last part is how to display your information.

A typical way is to create a subform based on the joining table. Insert the subform table in the parent form. This way, if you create a form based on thge lawyer table, you can see associated supervisors. If you create a form based on the supervisor, you can see the associated lawyers.


...Now if there is no relationship between the lawyer and the supervisor, then it would seem that you have two one-to-many relationship, and you have forgotten to give us the name of your primary table.

Cheers
Richard