Design Problem

[Bryan Hughes]

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]

I need to clarify.

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

 

[Tim Ferguson]

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]

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]

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

 

[Bryan Hughes]

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

 

[Bryan Hughes]

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

 

[Tim Ferguson]

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

 

[Bryan Hughes]

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

 

[Tim Ferguson]

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?

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.


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

 

[Bryan Hughes]

Tim,

I am sorry I have been unclear.

Here are the main entities.

Family (Case File)
Family Members (Clients)
Family Contacts (Non-Clients)
Addresses
Phones

... 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.

Yes this is what I mean.

I should have clarified better from the begnning.

The main purpose of the db is to track Families (Case File). Within each
family they want to track Family Members (Clients-which they are calling
Personal Journals will leave that out for now).
Basically they (the people I am building the db for) want the Family as the
first enity, and then within the Family the want to track the Family Member
(Client) as a unique enity (A file within a file). The contacts are
strictly contected to the family entity.

I hope this helps and does not muddle it more.

-TFTH
Bryan

 

[Bryan Hughes]

Tim,

This is what I have now

Family
MFIDS PK (Auto Number)
FID Long
FJID TEXT (MAIN ID for table)

People
IDS PK (Auto Number)
PJID PK
FN
LN
MI
Type (family member, or type of non-family member)
Primary (Use as primary contact)
Main (Use as main family name)

Personal (Client)
IDS PK (Auto Number)
PJID PK
DOB
etc.,

Addresess
IDS PK (Auto Number)
FJADID PK
FJID FK
Address1
Address2
City
etc.,

Resides
IDS PK (AutoNumber)
PJID FK
FJADID FK


Phone
IDS PK (Auto Number)
FJPNID PK
FJID FK
Phone
Type
Primary

Call
IDS PK (Auto Number)
PJID FK
PJPNID FK
Primary

Does this look right? That way there can be one address for many people. I
can check for a address for a Person (Client) and if there is non found then
a query can find the primary address for the Family File.

-TFTH
Bryan

 

[Tim Ferguson]

The main purpose of the db is to track Families (Case File). Within
each family they want to track Family Members (Clients-which they are
calling Personal Journals will leave that out for now).

Can I make a plea -- that you think really hard about your naming
strategies? In six months' time, you (or, even worse, somebody else) will
be staring at acronyms like PJID PK and wondering what on earth it means.
Typing a little bit more now will save a huge amount of effort later. If
that means PersonalJournalID then call it that. And don't call it
Families sometimes and Files another time. In my opinion, it's better to
stick to simple words like People and Addresses and LivesAt for the same
reason, and because also they are free from implications about
functionality. I agree that that is a matter of personal style and taste,
though.

DB design is all about semantics: the best solution often comes down to
questions like "What exactly is an address (for mailing? visiting?
identifing? liability for services?)" Don't worry about going back to
your people and asking picky questions like "what _exactly_ are you going
to use this DateOfBirth for?" It makes a difference.

This is what I have now

Family
MFIDS PK (Auto Number)
FID Long
FJID TEXT (MAIN ID for table)

Phrases like "Main ID for table" don't help. If it's a unique and stable
identifier, then it's the primary key; if it's not then it's just a field
like all the other ones. You don't give any clue about the use or meaning
of MFIDS or FID so I can't comment.

People
IDS PK (Auto Number)
PJID PK
FN
LN
MI
Type (family member, or type of non-family member)
Primary (Use as primary contact)
Main (Use as main family name)

If only clients can be Primary Contacts, then the Primary field belongs
in the Clients table. I don't see any foreign key pointing at the Files
(sorry, Family) table: how do you know which file this Person belongs to?
You have two fields labelled PK -- you can't have two PKs in a table, and
there is no point in using an autonumber as part of a compound PK,
because an autonumber is already stable and unique. Is a Main Family Name
an attribute of the person, or of the Family (and thus of all the People
in that Family)?

Personal (Client)
IDS PK (Auto Number)
PJID PK
DOB
etc.,

Hmmm: my original suggestion made this a subtype of the People table, so
the PK would be a Long Integer that matches the autonumber PK of the
People table. You can't point an autonumber at another autonumber because
you can't choose what to put in it.

Addresess
IDS PK (Auto Number)
FJADID PK
FJID FK
Address1
Address2
City
etc.,

I still don't understand what all the fields labelled PK are for. If this
is a straightforward Addresses table, then you just need an autonumber PK
and the text fields.

Phone
IDS PK (Auto Number)
FJPNID PK
FJID FK
Phone
Type
Primary
Ditto

Resides
IDS PK (AutoNumber)
PJID FK
FJADID FK

All you really need in this table is two fields: a Long Integer pointing
at the People table and a Long Integer pointing at the Addresses table.
You need some kind of AddressType indication somewhere. If a particular
address is always (R)esidential or (W)ork etc, then you can keep it in
the Addresses table. On the other hand, if Jones resides at Tolpuddle
Flats, while Smith works there, then it's an attribute of the Resides
table. If you see what I mean.

Call
IDS PK (Auto Number)
PJID FK
PJPNID FK
Primary

Ditto: I assume this is the table that tells you which PhoneNumber
belongs to which Person.

There is quite a lot there. I get the impression that you need to
understand more about keys and what Primary Keys do. It's not just a
question of stuffing an autonumber field in all the tables: you need to
have some conception of what you need to achieve.

All the best


Tim F