Design Problem (Re-Post)

  • Thread starter Thread starter Bryan Hughes
  • Start date Start date
B

Bryan Hughes

Hello,

I posted this question earlier but it is not very well thought out.

I am trying to figure out a normalization problem with some table in my
database.

Then main table in my database is tblCase_File there is a unique ID for each
case file.
Under the case file table are several tables including tblPrimary_Contact
and tblClient.

The tblClient has a Unique ID for each client.
Under the tblClient there are several tables including tblClient_Contact.
The tblClient_Contact can have a separate address for each client if they
are different.
There can also be multiple entries for a client to track address history.


In the Case File there is only 1 primary contact for each case file, the
majority of the time this will be a client, but there will be occasions
where the primary contact is not a client in the case file.

How should I design this to normalize this table, so If the primary contact
is a client I am not creating duplicate entries in the tblPrimary_Contact
and tblClient_Contact?

What I have so far is the tblPrimary_Contact has four fields a PK, Case_File
ID, Primary_Contact ID and a Client ID.
Under this I have a tblPrimay_Contact_Address when the pc is not a client.

If the primary contact is a client the Client ID is entered if nothing is
placed in the tblPrimay_Contact_Address for that case file, instead the code
looks for the matching Client ID in the tblClient_Contact.

It seems that there is a better way to do this that I am missing.

-TFTH
Bryan
 
See my response, to a previous post, as one solution. However, what you
could do would be to create a single table for all personal information (ie.
name, address, etc.). Then have Primary_ContactID and ClientID both
reference the ID of the person, in the first table.

tblPersons
PersonID
Name
Address

tblCase
CaseID
PrimaryContactID (FK to tblPersons.PersonID)
ClientID (FK to tblPersons.PersonID)

HTH

Sharkbyte
 
Back
Top