Design Problem

Hello,

I have a Case File management db. There can be multiple clients in each
case file. Each case file has a primary contact. Each client also can have
separate addresses and address history. It is possible that the Primary
Contact is not a client.

How should set this up that if the Primary Contact is a client if the
address is changed in the client area the primary contact is updated, or if
the address is changed in the primary contact then the address is changed in
the client area.

-TFTH
Bryan

I need to clarify.

What I meant to ask is how should I do this so I am not storing reduntant
information.
"Bryan Hughes" wrote in
message ...
Hello,

I have a Case File management db. There can be multiple clients in each
case file. Each case file has a primary contact. Each client also can
have
separate addresses and address history. It is possible that the Primary
Contact is not a client.

How should set this up that if the Primary Contact is a client if the
address is changed in the client area the primary contact is updated, or
if
the address is changed in the primary contact then the address is changed
in
the client area.

-TFTH
Bryan

"Bryan Hughes" wrote in
:

What I meant to ask is how should I do this so I am not storing
reduntant information.

rant class="screeching"
EEeeeek: the point of R design is not to prevent redundancy -- in fact,
in any non-trivial database there are huge amounts of repeated and
replicated stuff. This is a point that has been entirely lost by acres of
crappy web pages put up by people who just don't know what they are
talking about.
The point of R design is about integrity -- the ability to _prove_ that
information contained is complete and consistent. It's wasteful, slow,
and resource-hungry, but it's *correct*.
/rant

Anyway, to answer your question, you start by identifying the entities
you want to model; then ascertaining their attributes; then defining how
they relate to each other. From your original post, I would be guessing a
minimum of something like:

Cases(*CaseID, EnglishDescription, DateStarted, EstCost, etc)

Contacts(*ContactNumber, FName, LName, CreditLimit, etc)

Addresses(*AddressID, FirstLine, SecondLine, PostCode, Phone)

LiveAt(ContactNumber+, AddressID+, IsPrimary)

Clients(*CaseID, *ContactNumber, IsPrime)


but I would guess the whole thing gets a lot more sophisticated after
that. Sorry for the rant.

Hope it helps


Tim F

Thank you,

That helps immensely.

How do I deal with there is only be one Primary Contact, but multiple
entries for client contact information.

tblCase_File(*CaseID, FN, LN, etc.)
tblPrimary_Contact(*PContactID, CaseID+, etc)
tblClient(*ClientID, CaseID+, FN, LN, etc)
tblClient_Address(*AddressID, ClientID+, etc.)
tblClient_Phone(*PhoneID, ClientID+, etc.)


Should I make a table that holds address and table that holds phone and
connect them with ID information?
Please help slow on the uptake.

"Tim Ferguson" wrote in message
...
"Bryan Hughes" wrote in
:

What I meant to ask is how should I do this so I am not storing
reduntant information.

rant class="screeching"
EEeeeek: the point of R design is not to prevent redundancy -- in fact,
in any non-trivial database there are huge amounts of repeated and
replicated stuff. This is a point that has been entirely lost by acres of
crappy web pages put up by people who just don't know what they are
talking about.
The point of R design is about integrity -- the ability to _prove_ that
information contained is complete and consistent. It's wasteful, slow,
and resource-hungry, but it's *correct*.
/rant

Anyway, to answer your question, you start by identifying the entities
you want to model; then ascertaining their attributes; then defining how
they relate to each other. From your original post, I would be guessing a
minimum of something like:

Cases(*CaseID, EnglishDescription, DateStarted, EstCost, etc)

Contacts(*ContactNumber, FName, LName, CreditLimit, etc)

Addresses(*AddressID, FirstLine, SecondLine, PostCode, Phone)

LiveAt(ContactNumber+, AddressID+, IsPrimary)

Clients(*CaseID, *ContactNumber, IsPrime)


but I would guess the whole thing gets a lot more sophisticated after
that. Sorry for the rant.

Hope it helps


Tim F

"Bryan Hughes" wrote in
:

tblCase_File(*CaseID, FN, LN, etc.)

If FN and LN are names, then surely names belong to people, not to
files? Should not these data belong to the Clients table? You need (a) a
ClientID FK here if each file belongs to one client, or (b) a CaseID fk
in the Clients table if each client has exactly one file, or (c) a new
table if clients can have many files and files can have many clients.

tblPrimary_Contact(*PContactID, CaseID+, etc)

Don't understand this: it means that each Case_File can have zero or
more Primary_Contacts, which I guess is not what you want.

tblClient(*ClientID, CaseID+, FN, LN, etc)

See above for comments on the CaseID field...

tblClient_Address(*AddressID, ClientID+, etc.)
tblClient_Phone(*PhoneID, ClientID+, etc.)

This works if your clients never share addresses or phones; but with
families, corporate addresses etc you may want to rethink this bit.

How do I deal with there is only be one Primary Contact, but multiple
entries for client contact information.

There is not a good pure-R model for this. Ideally, you would have an
column in the IsAContactFor table called IsPrimary -- the trick is to
make sure that each Case_File has at most one record with this set to
true. In SQL Server you can do this with a trigger, using Jet and ADO
you can set a CHECK constraint, but with Jet/DAO you can only use code
in the form/ module.

Hope this helps


Tim F

Tim,

Thanks
If FN and LN are names, then surely names belong to people, not to
files? Should not these data belong to the Clients table? You need (a) a
ClientID FK here if each file belongs to one client, or (b) a CaseID fk
in the Clients table if each client has exactly one file, or (c) a new
table if clients can have many files and files can have many clients

The tbCase_File has a FN and LN field for the case file name. It is
possible that this name could change (client gets married, etc.).
In the tblClient there is a FK for the CaseFileID. There is one case file
with many clients in it.

Don't understand this: it means that each Case_File can have zero or
more Primary_Contacts, which I guess is not what you want.

Each case file has one Primary Contact, but it is not always a client
(Non-Family Member).
The tblPrimay_Contact has the CaseFileID as a FK. It also has FN, LN and
ClientID field if the primary contact is a client.

This works if your clients never share addresses or phones; but with
families, corporate addresses etc you may want to rethink this bit.

What I have done is create a tblAddress and tblPhone, each entry has a
CaseFileID to contact it to the right case file and each entry has a unique
id.
I am trying to use this for a single table for my Primary Contact address,
Client address, Emergency Contact etc.

Should I create another table that connects this to the correct AddressID to
the correct ID(client, primary, emergency, etc.)?
I know I can link a single address with the case file ID but how can I make
the connection to a particular client, or other type of contact?

-TFTH
Bryan


"Tim Ferguson" wrote in message
...
"Bryan Hughes" wrote in
:

tblCase_File(*CaseID, FN, LN, etc.)

If FN and LN are names, then surely names belong to people, not to
files? Should not these data belong to the Clients table? You need (a) a
ClientID FK here if each file belongs to one client, or (b) a CaseID fk
in the Clients table if each client has exactly one file, or (c) a new
table if clients can have many files and files can have many clients.

tblPrimary_Contact(*PContactID, CaseID+, etc)

Don't understand this: it means that each Case_File can have zero or
more Primary_Contacts, which I guess is not what you want.

tblClient(*ClientID, CaseID+, FN, LN, etc)

See above for comments on the CaseID field...

tblClient_Address(*AddressID, ClientID+, etc.)
tblClient_Phone(*PhoneID, ClientID+, etc.)

This works if your clients never share addresses or phones; but with
families, corporate addresses etc you may want to rethink this bit.

How do I deal with there is only be one Primary Contact, but multiple
entries for client contact information.

There is not a good pure-R model for this. Ideally, you would have an
column in the IsAContactFor table called IsPrimary -- the trick is to
make sure that each Case_File has at most one record with this set to
true. In SQL Server you can do this with a trigger, using Jet and ADO
you can set a CHECK constraint, but with Jet/DAO you can only use code
in the form/ module.

Hope this helps


Tim F

Tim,

This is what I have came up with sor far.

tblFamily_Journal
PK MFIDS AutoNumber
FID Long Increment of 1
FJID Text (Unique ID)
FN
LN
Open_Date

tblFamily_Journal_Additional
PK IDS AutoNumber
FK FJID
FJACID Text (Unique ID)
ContactID Text (Unique ID)
FN
LN
Relationship

tblFamily_Journal_Emergency
PK IDS AutoNumber
FK FJID
FJECID Text (Unique ID)
ContactID Text (Unique ID)
FN
LN
Relationship

tblFamily_Journal_Primary
PK IDS AutoNumber
FK FJID
FJPCID Text (Unique ID)
ContactID Text (Unique ID)
FN
LN
Relationship

tblPersonal_Journal
PK IDS AutoNumber
FK FJID
CNID Interger (Used to create the PJID)
PJID Text (Uniquie ID)
ContactID Text (Unique ID)
FN
LN
Primary Boolean
etc.

tblFamily_Journal_Addresses
PK IDS AutoNumber
FK FJID
FJADID Text (Unique ID)
Address1
Address2
City
State
ZipCode
Type
Date_Add
Current Boolean

tblFamily_Journal_Phones
PK IDS AutoNumber
FK FJID
FJPNID Text (Unique ID)
Phone
Type
Date_Add
Current Boolean

Again there needs to be One Primary Contact either a client in the Personal
Journal Table or a external individual with FN and LN entered in the Primary
Table.
How do I connect the address table and phone table? Should I create another
table that holds the addressID and ContactID and link them that way, or is
there a better way?

-TFTH
Bryan

The tbCase_File has a FN and LN field for the case file name. It is
possible that this name could change (client gets married, etc.).

.... so surely the file name has to update to reflect that change? In
other words, the names belong to the client rather than the file. Please
don't tell me that you identify the file by name alone... "Hello, I am
Mrs Lizzy Reardon, but my file is called Elizabeth Smith -- no, not that
Elizabeth Smith, the uptown one..."

I see you have a CaseID and presumably this matches the _actual_ file
identifier: stick to the Real World wherever possible.

Each case file has one Primary Contact, but it is not always a client
(Non-Family Member).

Okay -- we have a new entity type now, the ContactWhoIsNotAClient. This
sounds like a candidate for sub-classing (more later). What are the
differences between a Client and a NonClient?

What I have done is create a tblAddress and tblPhone, each entry has a
CaseFileID to contact it to the right case file and each entry has a
unique id.

As said above, this is theoretically fine as long as contacts never share
phone numbers and addresses. You are leaving yourself open to errors like
Mr Long getting to live at 12 Railway Cuttings whilst Mrs Long is still
at Flat 13b Nelson Mandela Court. If exactly one address belongs to
exactly one person, why not just put the address data in the Contacts
table anyway? If you have lots of addresses for a particular contact, how
will you know which one to use in any particular context?

Should I create another table that connects this to the correct
AddressID to the correct ID(client, primary, emergency, etc.)?

It depends -- what real-world entity is this new table modelling?


----

Stepping back a little, it seems to me that you are designing by
function, rather than by entity. I suggest you need to sit back with a
blank piece of paper and write down all the entities that you have to
model. For starters, what you have mentioned already include:-

Files
People
Clients
NonClients
Addresses
PhoneNumbers

Hope it helps

Tim F

Tim,

This is what I have came up with so far.

tblFamily_Journal
PK MFIDS AutoNumber
FID Long Increment of 1
FJID Text (Unique ID)
FN
LN
Open_Date


tblFamily_Journal_Contact
PK IDS AutoNumber
FK FJID
FJCTID Text (Unique ID)
FN
LN
Type (Type of Contact, Primary, Emargency, Additional)
Relationship


tblPersonal_Journal
PK IDS AutoNumber
FK FJID
CNID Interger (Used to create the PJID)
PJID Text (Uniquie ID)
FN
LN
Primary Boolean
etc.

tblFamily_Journal_Addresses
PK IDS AutoNumber
FK FJID
FJADID Text (Unique ID)
Address1
Address2
City
State
ZipCode
Type
Date_Add
Current Boolean

tblFamily_Journal_Phones
PK IDS AutoNumber
FK FJID
FJPNID Text (Unique ID)
Phone
Type
Date_Add
Current Boolean

Please don't tell me that you identify the file by name alone...
The Family ID (FJID) is the main file identifier. There is also a aka table
that tracks name change history for clients


Okay -- we have a new entity type now, the ContactWhoIsNotAClient. This
sounds like a candidate for sub-classing (more later). What are the
differences between a Client and a NonClient?
A Client is a family member receiving of benefiting form services porvided
to the family (demographic and other information is collected for clients).
A Non-Client is a external contact for a family but is not a family member
(only name and contact information is collected).

As said above, this is theoretically fine as long as contacts never share
phone numbers and addresses. You are leaving yourself open to errors like
Mr Long getting to live at 12 Railway Cuttings whilst Mrs Long is still
at Flat 13b Nelson Mandela Court. If exactly one address belongs to
exactly one person, why not just put the address data in the Contacts
table anyway? If you have lots of addresses for a particular contact, how
will you know which one to use in any particular context?

I have a addrerss table and a phone table that collects address information
for the entire family.
One Family Member (client) in the family can have multiple addresses,
another can have no addresses entered so they would default to the primary
contact address for that family file.

How can I connect the addresses to the Family Members (clients) or Family
Contacts (Non-Clients)?

The main entities for this db are
Familiy (Main)
Family Members (Clients)
Family Contacts (Non-Clients)
Address
PhoneNumbers

-TFTH
Bryan

"Bryan Hughes" wrote in
:

I have reordered some parts of the post in order to try to impose some
logical progression. Unfortunately, I am not clear exactly how your
message maps onto itself: you might be causing some confusion by using
different language for the same things.

The main entities for this db are
Familiy (Main)
Family Members (Clients)
Family Contacts (Non-Clients)
Address
PhoneNumbers

....
This is what I have came up with so far.

tblFamily_Journal
tblFamily_Journal_Contact
tblPersonal_Journal
tblFamily_Journal_Addresses
tblFamily_Journal_Phones
tblFamily_Journal_Phones

and what happened to the files?

What are
the differences between a Client and a NonClient?

A Client is a family member receiving of benefiting form services
porvided to the family (demographic and other information is collected
for clients). A Non-Client is a external contact for a family but is
not a family member (only name and contact information is collected

The best way of mapping this is probably to have a single entity for
Contacts (I would call it people, but as long as we are both describing
the same thing it doesn't matter much). This would contain "name and
contact information":

ContactID PRIMARY KEY
FirstName
LastName

You would have a second table called Clients linked in this way:

ContactID PRIMARY KEY
FOREIGN KEY REFERENCES Contacts
BirthDate
IncomeInDollars
ProvisionLevel
etc

Note that the linkage means that you can have a Contact with or without a
Client record, but you can't have a Client who isn't a Contact.


If you have lots of addresses for a
particular contact, how will you know which one to use in any
particular context?

You never answered this.


tblFamily_Journal_Addresses
PK IDS AutoNumber
FK FJID
FJADID Text (Unique ID)
Address1
Address2
City
State
ZipCode
Type
Date_Add
Current Boolean

I don't know what a FJADID is, but I am usually highly suspicious of non-
primary unique indexes. Yes there are reasons for them, but it's about as
rare as 1:1 relationships. I assume that FJID references the
Family_Journal table but you said that addresses belonged to people not
to files -- it should point to (my) Contacts table so that clients and
nonclients can have somewhere to live.

Addresses (
AddressID PRIMARY KEY
BelongsTo FOREIGN KEY REFERENCES Contacts
IsPrimary
IsCurrent
StreetName
CityName
etc )

although you can join Addresses to Clients when you need to write
reports, queries, mailmerges etc. Make the same arrangement for the phone
numbers.

One Family Member (client) in the family can have multiple addresses,
another can have no addresses entered so they would default to the
primary contact address for that family file.

"... Address for that family file..." This is the first time you have
mentioned addresses belonging to a file rather than to a person. If you
mean that there should be one person attached to a file with primacy, and
that there should be one address linked to that person with primary, then
we are back on track. Otherwise, we have a different scenario involving
one supertype called "ThingsThatCanHaveAddresses" and subtypes called
"Files", "Contacts", "Clients" and so on.


Files --- Contacts ---- Clients
| |
| +---- Addresses
+------- Phones



This sums up where we are at the moment, and I think it addresses what I
understand of your needs. Some of the queries will not be pretty, but you
only have to write them once! I am still worried about this defaulting
address stuff: I'll think about that over the weekend and post back if it
becomes any clearer.

B wishes


Tim F