Design Review and critique

[lespaul]

I'm currently developing an ERD for a civil case management system and don't think that what I've been envisioning is going to work well. Here's a link to the existing ERD (this is the beginning of my first go round with this diagram, go easy on me!)

http://home.comcast.net/~landrews77/civilerd.tif

Here's my issue, how should I best guarantee that I don't end up with duplicate IDs from Party, Interpretors and Attorney. If I have an ID of 100 in each of those tables, I can't do an inner join from the PartyRoles table.

I'm thinking that I may want to put all interpretors and attorneys in the same table with the parties and have an indicator of whether they are an attorney, interpretor, defendant or plaintiff.

Does that sound more feasible?

thanks for any input.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Zathras]

Just off the top of my head...

The function of the PartyRoles table is unclear. The fact that you are worried about ID values from multiple tables needing to be unique is a beg red flag. ID's should only be just that and not have any "intelligence" associated with them.

It looks to me like you would have these primary tables:

Judge
JudicialDivision
Case
Attorney
Interpretor
Party

And then these relationships:

Judge many-to-many with JudicialDivision
(same judge may be in diffrent divisions at different times and same division may have different judges at different times) Implies a JudicialDivisionJudge table with columns for term begin and end dates.

Case many-to-one with JudicialDivision

Attorney many-to-many with Case. Implies a CaseAttorney table possibly with a column for Defense or Plaintiff

Party many to one with Case (probably want a column in Party to indicate Defense or Plaintiff) On second thought perhaps it should be many-to-many to handle those Plaintiffs that bring multiple suits.

Interpretor one-to-many with Case (unless possibility exists for a change of interpretor mid-trial or multiple interpretors for a trial in which case you would need a CaseInterpretor table)

You probably will need a table for Recorder as well in a one-to-many relationship with Case.

 

[harebrain]

In practice, they are all people (as well as are judges.) Some would do what you propose. You will, however, end up working with a lot of reflexive joins.

 

[Zathras]

True, but by having separate tables it should make it harder for an application to assign a defendant as the judge and a court reporter as the defendent in a given case (assuming referential integrity is correctly set up).

 

[harebrain]

So, the prospect of application bugs should drive the system design? That would stop ALL development dead in its tracks.

 

[lespaul]

You have all brought up all the points that I have gone over in my head and I still can't decide which way to go.

You're right, I don't want a defendant assigned as a judge! I guess I'll ponder it a bit more and post back with modifications for critiquing! (but probably not today, have other things to do!!)

les

 

[SQLSister]

While you might not want to design based on possibility for application errors, you do want to consider efficiency. In my one of my past jobs we had a database where the main table included everybody (I did not design this but had no choice but to live with it). It had millions of records and 99 foreign key relationships. Even worse, becasue of the different types of tables it had relationships with, no one record would ever join to more than a 10-15 of those tables, yet any delete from that table required all to be checked which made deletes extremely slow to the point of impossible for more than one record at a time (and even then we got multiple timeouts before the delete worked). Plus virtually every query on the system had to join to this table which made it a locking nightmare. Think very long and hard about how many records your system will eventually contain before deciding to use one main table that everything else will join to.

Questions about posting. See faq183-874

 

[lespaul]

There will be between 15000 and 20000 cases per year. Each case has at least 1 plaintiff and at least 1 defendant (each case could have multiple plaintiffs & defendants); each case could have multiple attorneys (none/one/many for plaintiff;none/one/many for defendant). There could be none/one/many interpretors (if both def and plain need an interpretor, they can't use the same one).

I have to be able to extract all the people on the case and what their role in the case is.

I need to be able to track address changes for plaintiffs & defendants and attorneys.

There will be people from all over the court accessing this data at any time, so record locking needs to be considered as well.

Even if we end up with something that isn't quite normalized, we will still be improving what we are currently using!

thanks for the suggestions, I'll keep at it!

leslie

 

[lespaul]

Ok, so I've made a few modifications to the ERD. Here's the latest:

http://home.comcast.net/~landrews77/civilerd2.tif

With a setup like this, I have a main PERSON table, but can perform all INNER JOINS between that table and the INTERPRETOR, ATTORNEY, ADDRESS and CONTACT tables.

I like the "free form" of the contact table. I'll add a lookup table of valid ContactTypes (email address, home phone, work phone) and then store the data in the ContactInfo field. However it just occurred to me that I might want to break out email from phone numbers so that all the information is in the same format.....what do you think?

A little more additional information: the DB will be developed on an iSeries (AS400) which uses DB2.

Thanks again for any input!

Leslie



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[Thargy]

lespaul,

I think you're heading in the right direction. I believe that you should have a person table, and that it should have a role column, which is an obligatory FK to the role table, which would contain judge, plaintiff, lawyer etc.

Although SQL sister has a valid point, you should normalise first, and then only denormalise when needed. If you start denormalising in anticipation of problems, how do you know when to stop denormalising?

You also appeared to be storing addresses in multiple tables in your original diagram. Have an address table, with all of them held therein. That way, if you ever need to record the details of a strange British person, who doesn't have a zip code but a post code, one modification will do. Usually you only get one bite at the normalisation cherry, so make it a good 'un!

Regards

Tharg

Grinding away at things Oracular

 

[harebrain]

lespaul,

I agree with thargtheslayer; he makes good points.

One thing I don't understand in the new ERD: the relationship between the Divisions entity and Employees. A many-to-one in that direction?

However it just occurred to me that I might want to break out email from phone numbers so that all the information is in the same format.....what do you think?

I'd come down on the side of column/domain integrity, i.e., a phone number isn't an email address and they shouldn't be shoe-horned into the same column. In this business we're often on the horns of an abstract/concrete dilemma. This is a place to favor the concrete.

 

[lespaul]

Currently we have to do lots of "find the judge that was in division 14 on 6/15/2001 and display their name". (we have a horribly designed system, no normalization, no referential integrity, NOTHING!! See Thread655-606833 for a good laugh regarding its design!)

So a division has a judge (who is an employee) assigned to it. I'm envisioning a divisions table where the Division.ID is stored in the Case table so if a new judge is assigned to a division, only the ID field would have to be updated in the Case table.

Division
ID (PK - auto increment)
DivisionNumber (1 - 19)
JudgeID (FK to employees to get the judge name)
BeginDate
EndDate
Courtroom

So, when the judge in division 15 retires and we have a temporary judge take over for a short period of time, we can still update all current cases, but closed cases will still have the previous assigned division's judge information available.

I've also come to the same thoughts as you regarding the email addresses. We'll need a different table for that information. I'm glad I got additional confirmation on that point.

Leslie