Courtroom Drama

[GoBruins]

Hi all,

I have a DB that tracks court cases. Fairly new to DB design, so here goes:



The first table is "Cases" - primary index is CaseID, which is unique (of course).

The second table is called "Parties", and tracks the parties that were involved in the Cases - primary index is PartyID.

Now if that were the extent of it, I'd create a "juncture" table named "Cases_Parties" that housed CaseID and PartyID. There would be a one-to-many relationship from "Parties" to the juncture table "Cases_Parties", and a one-to-many from "Cases" to the juncture table "Cases_Parties".

So far, so good. But the snag(s) is, a party can be either a plaintiff or a defendant in a Case. Where do I house the info that the party was either a plaintiff or a defendant in the case?

Also, a case can have multiple plaintiffs and/or multiple defendants? How do I handle a situation like that?

Thanks in advance!

[June7]

Have a field in Cases_Parties for party attribute (plaintiff, defendant). Each party will be a separate record.

[orange]

Here's a data model that may help
http://www.databaseanswers.org/data_...ment/index.htm

[GoBruins]

That's exactly what I was pondering! Okay - will have a go. Thanks.

[GoBruins]

Thank you. I'll have a close look.

Here's a data model that may help
http://www.databaseanswers.org/data_...ment/index.htm

[GoBruins]

Have a field in Cases_Parties for party attribute (plaintiff, defendant). Each party will be a separate record.

Another question, if I may...

Sometimes, an expert witness is involved. He/she could represent either side (plaintiff or defendant), and could represent multiple parties (for example, two defendants at the same trial). How does this fit into the grand scheme of things?

I've created a table called "Expert Witnesses", but I"m stumped from there. I suspect it gets tied to a juncture table that contains Legal Cases and Parties involved somehow?

In the end, I'd like to query something like: return all records for Expert Witness named "John Do" where he testitified on behalf of the defense only, along with the Case ID.

Thanks again in advance.

[June7]

In my view, witnesses don't represent the parties (that's what lawyers do), they represent themselves and provide their opinion/observations for jury consideration. Does it matter how many defendents or plaintiffs are participating in the case? If witnesses are tried together, all testimony pertains to all, right? A single case would mean one trial? If defendants separated would there be another case number?

Junction table:
Case_Witnesses
CaseID
WitnessID
SideCalling (Defense, Plaintiff)

[orange]

Could be CasesPartiesWitness. As you said a junction of Cases, Parties and Witness.

You could have a table of WitnessTypes also with entries like

Expert
For the Defense
For the Plaintiff
,,,,


As you indicated a Witness could be associated with more than 1 party on a given Case and act in different Roles (witness type here) in Different Cases.

I think that would handle your

return all records for Expert Witness named "John Do" where he testitified on behalf of the defense only, along with the Case ID.

[GoBruins]

Hi - sometimes it gets a little more complicated for us. Defendant 1 might be represented by Attorney A, Defendant 2 might be represented by Attorney B, and the expert witness can be hired (and more importanty - paid) by only one defendant.

The one kink I could see in the junction table suggested: Once I Choose the defendant, I'd like to only be able to choose cases that the defendant was part of (as an error checking mechanism).

Another question, if I may...

Sometimes, an expert witness is involved. He/she could represent either side (plaintiff or defendant), and could represent multiple parties (for example, two defendants at the same trial). How does this fit into the grand scheme of things?

I've created a table called "Expert Witnesses", but I"m stumped from there. I suspect it gets tied to a juncture table that contains Legal Cases and Parties involved somehow?

In the end, I'd like to query something like: return all records for Expert Witness named "John Do" where he testitified on behalf of the defense only, along with the Case ID.

Thanks again in advance.

[June7]

What is the confusion? With queries that join tables and apply filter should be able to get that output.

[orange]

Can you post a copy of your current tables and relationships?
Do you have a list of Business facts that your model is meant to represent?

[GoBruins]

Thank you. Once I untangle it, I will post.

[GoBruins]

I'm not sure how to resize the image on the post.

So let's take an imaginary case like Superman v. Batman and Robin:

Superman is the plaintiff
Batman is a defendant
Robin is also a defendant

The junction table named "Junction Cases_Parties" ties in the "Party" table to the "Case" table (as per your great suggestions).

So this is where I'm a bit unclear. An expert witness was hired by Robin *only*, and will be paid by Robin only. There is an "Expert" table. I need to tie the expert to the "Superman v. Batman and Robin" case, but only to Robin, *also* noting Robin's role in the case.

Given the above parameters, it feels like I need to tie the Expert to the junction table named "Junction Cases_Parties". Maybe create another junction table that ties "Expert" to the existing "Junction Cases_Parties" table?

Am I on the right path?

[orange]

What is a Case Event?

[GoBruins]

Hi,

Case Event is straightforward table that stores the historical events that pertain to a Case, such as:

- Deposition of witness(es).
- Supporting documents arriving in mail.
- Phone conversation with lead attorney.
- etc.

It's linked to the "Case" table via a SubDataSheet, so that we can quickly eyeball the events pertaining to the case.


Thanks.