Help with Table/DB Design

[Eleanore]

Hi everyone. I am trying to create a database; based on the responses I've
gotten/read, mine is poorly designed. So, I'm game to start all over again,
with a better design/foundation, and hopefully all will be good.

The database is meant to track contacts with an office for specific cases.
There are up to 10 parties in each case (parents, children, etc.), as well
as an attorney for each party, a mediator (one of 9), an investigator (one
of 5), and a judge. There are up to 20 events to track (each event with
different investigators for each event), as well as 9 possible required
evaluations for some of the parties. Most cases are one of 6 types, but a
few could be some other type.

Initially, I had a single table, with 3 look-up tables for judge, mediator,
and investigator. Through the NG, I've learned that all the names should be
stored in a single table, so I created a Names table, an Events table (with
event Ddate and event Person), an Evaluation table, and a Results table, but
then someone else in the NG indicated that was poor design. Other than
tracking the data, I need to get a count of each event per investigator, as
well as a count of the final outcomes.

Can the learned readers of the NG please suggest how many/what tables I
should optimally use for good design, to preclude problems later? I
certainly appreciate any help anyone will give me. Thank you in advance.

 

[John W. Vinson]

Hi everyone. I am trying to create a database; based on the responses I've
gotten/read, mine is poorly designed. So, I'm game to start all over again,
with a better design/foundation, and hopefully all will be good.

The database is meant to track contacts with an office for specific cases.
There are up to 10 parties in each case (parents, children, etc.), as well
as an attorney for each party, a mediator (one of 9), an investigator (one
of 5), and a judge. There are up to 20 events to track (each event with
different investigators for each event), as well as 9 possible required
evaluations for some of the parties. Most cases are one of 6 types, but a
few could be some other type.

Initially, I had a single table, with 3 look-up tables for judge, mediator,
and investigator. Through the NG, I've learned that all the names should be
stored in a single table, so I created a Names table, an Events table (with
event Ddate and event Person), an Evaluation table, and a Results table, but
then someone else in the NG indicated that was poor design. Other than
tracking the data, I need to get a count of each event per investigator, as
well as a count of the final outcomes.

Can the learned readers of the NG please suggest how many/what tables I
should optimally use for good design, to preclude problems later? I
certainly appreciate any help anyone will give me. Thank you in advance.

If you're using one field per party, or one field per mediator ....

STOP.

You're guilty of Committing Spreadsheet Upon a Database, a misdemeanor
punishable by being required to read the Database Design 101 links at Jeff
Conrad's resources page:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html

"Fields are expensive, records are cheap". You need to identify each type of
Entity (real-life person, thing or event) of importance to your database. Each
type of entity (Parties, Attorneys, Investigators, Judges, Events, probably
Hearings, Trials, Cases, etc.) should have its own table. Not knowing just
what you did with the multiple tables I don't know why it was called "poor
design" - on the face of it, it's much better than the wide-flat single table
solution. For one thing, you don't want (say) ten Party fields - often many of
them will be NULL; you have to search across all ten fields to find out who's
involved in a case; you will someday need *eleven* and you'll be in trouble!

If you want counts, use a Query to count records; you certainly will *not*
store counts in any table.

Without knowing more about just how your data elements are related, I hesitate
to post detailed table design suggestions; but as a VERY ROUGH first go
consider:

People
PersonID <Autonumber Primary Key>
LastName
FirstName
<other biographical data>

Judges
PersonID <Long Integer Primary Key> <one to one relationship to People>
<any fields about this judge as a judge, e.g. which court>

Attorneys
PersonID <Long Integer Primary Key> <one to one relationship with People>
<any fields about this person as an attorney>

<ditto for Investigators>

Courts
CourtID
CourtName <e.g. Metropolis Superior Court>

Cases
CaseNo <probably assigned by the court??>
CourtID
JudgeID <link to Judges.PersonID> <assuming only one judge>
.... <undoubtedly other fields>

CaseParties
CaseNo <link to Cases>
PersonID <link to People> <one record for each person involved>
Role <defendent, plaintiff, ... ??? again I don't know your data>

PartyAttorneys
PersonID <link to People, who is represented by...>
AttorneyID <link to Attorneys.PeopleID, representing attorney>
Role <e.g. chief counsel, assistant counsel, pro bono representative>

There'll be lots more. This could be a complex app, but Access is well suited
to handling it... IF you get a really good grounding in database design
techniques, or hire somebody who has one.

John W. Vinson [MVP]