Re: Table design and Normalization
- From: "Jeff Boyce" <JeffBoyce_IF@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 13 Jun 2005 03:56:34 -0700
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).
--
Good luck
Jeff Boyce
<Access MVP>
"G deady via AccessMonster.com" <forum@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message news:4FC5742F24E40@xxxxxxxxxxxxxxxxxxxx
> 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
> DteBrth
> SocSecurNumb
>
> tbl_witnesses
> -------------
> witnessesID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_expert_witnesses
> --------------------
> expertID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_attorneys
> -------------
>
> attorneysID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_judges
> ----------
>
> judgesID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_police_officers
> -------------------
>
> PolID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_parties
> -----------
>
> PartyID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_defendants
> ---------------
>
> DefID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_magistrates
> ---------------
>
> MagID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_prosecutors
> ---------------
>
> ProsID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_arbitrators
> ---------------
>
> arbID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> DteBrth
> SocSecurNumb
>
> tbl_wc_referees
> ---------------
> wcID(autonumber pk)
> clientID(fk)
> FName
> MI
> LName
> StAddress
> HPhone
> WPhone
> Cell
> Fax
> 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?
>
> --
> Message posted via http://www.accessmonster.com
.
- References:
- Table design and Normalization
- From: G deady via AccessMonster.com
- Table design and Normalization
- Prev by Date: Calendar control and time control
- Next by Date: Re: surveys
- Previous by thread: Table design and Normalization
- Next by thread: RE: Table design and Normalization
- Index(es):
Relevant Pages
|
