|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 13th, 2005, 06:59 AM
|
|||
|
|||
Table design and Normalization
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 
|
|
#2
June 13th, 2005, 11:56 AM
|
|||
|
|||
|
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" wrote in message ... 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
|
|
#3
June 13th, 2005, 02:23 PM
|
|||
|
|||
|
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" wrote: 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
|
|
#4
June 13th, 2005, 02:38 PM
|
|||
|
|||
|
You could combine them all into one table with all the info available but fill inonly the pertinent fields depending on what category they are. You could have a field to designate what category theya re and use an option group on your form to select the value for this field. If there is a chance that a person will show up in more than one category, add fields of categories and make the Yes/No and on the form allow a check in any of the check boxes for these categories. Either way, use SS# as key field.
|
|
#5
June 14th, 2005, 01:00 AM
|
|||
|
|||
|
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. -- Message posted via http://www.accessmonster.com
|
|
#6
June 14th, 2005, 11:57 AM
|
|||
|
|||
|
?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 "G deady via AccessMonster.com" wrote in message ... 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. -- Message posted via http://www.accessmonster.com
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Splitting Large Table into Smaller Tables - How Much is Too Much? | Karl Burrows | General Discussion | 4 | June 2nd, 2005 06:49 AM |
| Splitting Large Table into Smaller Tables - How Much is Too Much? | Karl Burrows | Database Design | 4 | June 2nd, 2005 06:49 AM |
| Help! - Table design: Normalization and subclassing questions | stgpatrick | Database Design | 1 | May 12th, 2005 06:51 PM |
| Table Desing Question | Eddie's Bakery and Cafe' | Database Design | 5 | April 11th, 2005 08:54 PM |
| Table design for permit | Michael DiCostanzo | Database Design | 0 | October 6th, 2004 05:13 PM |
Linear Mode
