Design Problem (Re-Post)

[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

 

[Sharkbyte]

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