Table design and Normalization

[G deady via AccessMonster.com]

My db is very name and address intensive. It is a db for my law firm. Right
now I have seperate tables to save the names and addresses of the following
cateogories of people:
tbl_client_information
clientID(autonumber pk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_witnesses
-------------
witnessesID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_expert_witnesses
--------------------
expertID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_attorneys
-------------

attorneysID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_judges
----------

judgesID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_police_officers
-------------------

PolID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_parties
-----------

PartyID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_defendants
---------------

DefID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_magistrates
---------------

MagID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_prosecutors
---------------

ProsID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_arbitrators
---------------

arbID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb

tbl_wc_referees
---------------
wcID(autonumber pk)
clientID(fk)
FName
MI
LName
StAddress
HPhone
WPhone
Cell
Fax
EMail
DteBrth
SocSecurNumb


I'm wondering if I can eliminate all these tables and just go with two since
the struxture is the same. I would keep clientinfo since it is main table but
combine all the others

I would add a field called Status. If person is witness his status is
witness. If she is a judge status, etc. Is this feasible?

 

[Jeff Boyce]

It seemed to me that you have very similar or identical table structures.
Any time you find yourself embedding data in table names (e.g., "status" or
"role" or ...), you are making your job harder (and making Access work
harder, too).

All these people share the facts that they have FName, LName, ..., so having
a single table is a good idea!

But don't just add a "status" field ... because I can imagine a situation in
which a given person, who is an attorney, who serves as a pro tem judge, is
a witness in one case, while being a defendant in another.

I'd suggest you create a table that associates a PersonID (from your
newly-combined Person table) with a CaseID (from your Case table), with a
Status (I'd call it Role) ID indicating which role(s!) the person plays in
the case (and create a Status/Role lookup table).

 

[Guest]

On top of Jeff's suggestions, I am wondering if this db will be on a computer
that has access to the Internet. If so, you might consider creating a small,
side db (on a non-connected PC), that stores a person's SSN and assign them
some other ID code to keep in this db.

It's something I deal with regularly, and it has become a pet-peave of mine.
=)

Sharkbyte

 

[G deady via AccessMonster.com]

Thanks all. All your suggestions are good. After reading them I think I am
going with two tables. The first will be my clienttable. This is the main
db table and I don't think I want to mix our client names with any other
names. However, the second table will combine all the other names tables into
one table. I still like the idea of a status field. Each name will be a
seperate record. Why is it a problem to have a field that specifies what the
name in that record represent. For example. if the name John Jones is
entered there would be a field where the user would enter "witness" or
"expert" or "prosecutor" or "Judge" etc to indicate what role John Jones had
in the case. Am I missing something? There would be one table and the user
would enter data in a field to indicate if the name was a witness, the judge,
defense attorney, etc.

 

[Jeff Boyce]

?And in the situation that someone plays more than one role?

And when there's more than one case?

I recommend a "junction" table that holds CaseID (pointing back to a Case
table), PersonID (pointing back to a single Person table), and RoleID
(pointing back to a Role lookup table).

Good luck!

Jeff Boyce
<Access MVP>