[AccessD] Question of process

I think you just need to apply the rules of normalization here.  Build a
'person' table, with information that is common no matter what 'type' they
are.  Then, if you need more information for a particular type, build a
table for the extra information, but give it a foriegn key field back to the
person table.  That way, the person ID will interrelate all types that a
person is.

Drew

-----Original Message-----
From: John Clark [mailto:John.Clark at niagaracounty.com]
Sent: Wednesday, January 28, 2004 9:39 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Question of process


I am beginning a new project. The purpose of this program will be to
track indictment cases for the District Attorney's office. My question
isn't so much technical, like usual, but rather 'technique'.
 
I'll start with a scenario: 
 
Lets say I am a Asst. DA, so I am in the system as such. Then after a
couple of years, I go into private practice and become a defense
attorney; I may now be in the system as the lawyer for a defendant. Now
let's assume that I am assaulted and my attacker is charged, so now I am
in this system as a victim of this case. Finally, I cross over to the
dark side (I know lawyers are already there), and I am indicted for a
crime of my own. I will now be in the system as a defendant, a victim, a
defense attorney, and an ADA...these last two, very probably, several
times.
 
My situation is that I would like to bring up every instance of a
person fitting this description, or similar ones. For Instance a report
of the previous example might produce the following:
 
JOHN W CLARK
 
#2001-023    ADA
#2001-102    ADA
#2001-140    ADA
#2002-334    ATTORNEY
#2003-153    ATTORNEY
#2003-543    VICTIM
#2003-654    ATTORNEY
#2004-030    DEFENDANT
 
I had originally thought to create a table for the 'types' (i.e.
attorney, ADA, victim, etc.), a table with names and personal info, an
indictment table, and link these all via a fourth table, but this soon
seemed insufficient. The average indictment number will have at least
five people connected to it (Defendant, Attorney, ADA, Victim, and
Judge), and possibly many more, with multiple victims and/or
co-defendants.
 
Am I over thinking this? Would it just be as simple as having a table
for each type and searching these tables by indictment number later in
the program?
 
I thought that maybe, by typing this out, it would be clearer in my
mind, by the time I was done, but I am still fuzzy with it!
 
Thanks for any insight or advise you could give me!
 
John W Clark
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com