Database schema for Assignment Website - Is this a Many-to-Many Relationship?

Question

I am creating an asp.net website (through Expression Web 4 and Visual Web Developer 2010 Express), using a database created via Sql Server 2008. I am however stuck with regards to the database schema, which feeds into the website.

The website being created is to allow legal cases to be created, with the type of case being recorded and then have Lawyer(s) and/or Advocates and/or Support Staff recorded as working on the case.

So a case called 'Kramer v Kramer' will be a divorce case with 2 lawyers, 1 advocate and 4 support staff all working on the case. However another case 'The People v Larry Flynt' may be a prosecution case with 4 lawyers, no advocate and 10 support staff.

What I have done so far is that I have a table called 'Case Table' which contains the following columns:

Case_ID (this being the Primary Key for the table); Case_Type_ID; Lawyer_ID; Advocate_ID, and Support_Staff_ID. The last 4 columns in the Case Table are Foreign Keys.

The Case_Type Table simply has two columns: 'Case_Type_ID' (PK) and 'Area_of_law'. Case_Type_ID is simply numbers (001,002...) and 'Area_of_law' is char(30) (e.g. litigation, divorce, property, criminal). So far, so good.

The problem is (and this applies to the Advocates and Support Staff), if I create the Lawyer Table to simply have three columns:

Columns = Lawyer_ID (as the primary key) | First Name | Last Name

record 1 = 001 | Tom | Hanks

record 2 = 002 | Tom | Cruise

record 3 = 003 | Daniel | Craig

record 4 = 004 | Nicole | Kidman

how do I then assign multiple Lawyers to the case?

Within the Case Table, in order to comply with the normalisation rules, presumably I would need to add columns to the Case Table as described above so it records Lawyer_ID_1 (FK)| Lawyer_ID_2 (FK)| Lawyer_ID_3 (FK) | Lawyer_ID_4 (FK).

So it could be 004 | 002 | Null | Null to show Nicole Kidman and Tom Cruise were working on the case. If I only had one Lawyer_ID column, in order to show Nicole Kidman and Tom Cruise working on the case, it would have to be '004,002', which is a big no-no from what I have read on this website.

By recording all the lawyers in a separate table, I am able to to create a databound checkboxlist (bound to the Lawyer Table) in visual web developer 2010 express because it populates the list with the names of the lawyers. So I am pleased with this approach.

But if then had 50 lawyers within the Lawyer Table, I would need to have 50 columns in the Case Table assigned to just lawyers (if a Case ended up with 50 lawyers working on it). This approach is problematic as the Case Table may get skewed if lawyers are removed or added because the number of Lawyer columns become affected. Clearly this is a bad approach, so is anyone able to help?

Is there a database schema that would allow one 'Case' record to be related to multiple 'Lawyer' records, without numerous columns? (hopefully the schema can also be applied to the Support Staff Table and the Advocate Table).

Your help will be much appreciated. I am new to web development and sql database work and I've tried to look up the solution, but I probably don't know the keywords for my problem.

Answer 1

This is probably a many-to-many relationship. Allow me to explain the 3 relationships a little in your case:

If each case has only one lawyer, and each lawyer can only ever work on one case, then you have a one to one relationship.

If each case has only one lawyer, but each lawyer can work on multiple cases, then you have a one to many relationship. Likewise, if each case can have multiple lawyers, but each lawyer can only work on one case, then you still have a one to many relationship.

If each case can have multiple lawyers, and each lawyer can work on multiple cases, then you have a many to many relationship.

The typical solution to a many to many relationship is to have a third table to describe the relationship:

create table casesALawyerWorksOn (case_id, lawyer_id)

If @albert, @brad, and @christine work on a case @peopleVflynt, then:

insert into casesALawyerWorksOn (case_id, lawyer_id) values
    (@albert, @peopleVflynt)
    (@brad, @peopleVflynt)
    (@christine, @peopleVflynt)

Thus, all the cases that a lawyer @fred works on are:

select case_id from casesALawyerWorksOn where lawyer_id = @fred

and all the lawyers that work on a case @kramerVkramer are:

select lawyer_id from casesALawyerWorksOn where case_id = @kramerVkramer