Database Design

[cs_vision]

I'm having the hardest time, I new to Access and using Access 2003. I have a
lot of data that needs to be put into a database for information and
reporting purposes. Its dealing with estate clients. Following are fields:

Responsible Attorney
Client Name
Document Name
Document Date
Vault Number

What I needs to be done is choose each client, classify them (below are some
of the classifications:

Martial Lead
Family Lead
Marital Lead_No
Family Lead_No

Each client will be classified. Then when a classification is given another
form with a questionnaire will be filled out based on their classification.
These forms differ based on the classification they contain questions which
should be answered YES/NO. And if yes, a explanation field must be given.
So basically I see possibly one table with the main client information in it
and a table for each classification. Can anyone help?

 

[TonyT]

Hi cs_vision,

replies in oreder below;

I'm having the hardest time, I new to Access and using Access 2003. I have a
lot of data that needs to be put into a database for information and
reporting purposes. Its dealing with estate clients. Following are fields:

Responsible Attorney
Client Name
Document Name
Document Date
Vault Number

Putting all of these in 1 table assumes that each attorney will only have
one Client, one document & 1 vault and vice-versa for each field, If this is
not the case, then you want to think about separating them into their own
tables.
You need to look through your data and see where you will have one-to-one
relationships (usually kept in 1 table), one-to-many relationships the one
side forming the parent table and the many the child side table and finally
many-to-many relationships that require 3 tables - two one-to-many and one
link table many-to-one (twice).

What I needs to be done is choose each client, classify them (below are some
of the classifications:

Martial Lead
Family Lead
Marital Lead_No
Family Lead_No

These classifications appear to be a prime example of many-to-many
relationships, in that each client will probably have many classifications,
and the same classifications will be used for many clients, thus 3 table to
link client and classification is probably the way to go.

A possibly set of tables and fields is;

tblAttorney
AtttorneyID
Attorney Name
Other Attorney ONLY related Info

tblClient
ClientID
Client Name & all other Client Info
AttorneyID <- foreign key to primary attorney table (assumes each client
only ever has one attorney, otherwise you will need another link table
instead see * below)

tblDocument
DocumentID
Document Name
Document Date
Other Document only info

tblClassification
ClassificationID
Classification Name <- Marital Lead, Family Lead etc as listed as records
Not fields
other Classification related descriptions etc.

tblLinkClient
AttorneyID
ClientID <- * use if Attorney & Client changes with document
DocumentID

tblLinkClassification
ClassificationID
ClientID

Hopefully you begin to see there are numerous ways of placing the data in a
normalised database, and they depend entirely on relationships. Work through
your data and see where relationships exist, then split them into relevant
tables, and join them on a unique key (often ID number), when you come
accross many-to-many relationships resolve the, with a 3rd link table.

Each client will be classified. Then when a classification is given another
form with a questionnaire will be filled out based on their classification.
These forms differ based on the classification they contain questions which
should be answered YES/NO. And if yes, a explanation field must be given.
So basically I see possibly one table with the main client information in it
and a table for each classification. Can anyone help?

Sort out the data relationships first, then create the tables before even
considering form and report designs, and how to bring the information back
together again.

hope that helps and good luck!

TonyT..

 

[John Vinson]

I'm having the hardest time, I new to Access and using Access 2003. I have a
lot of data that needs to be put into a database for information and
reporting purposes. Its dealing with estate clients. Following are fields:

Responsible Attorney
Client Name
Document Name
Document Date
Vault Number

What I needs to be done is choose each client, classify them (below are some
of the classifications:

Martial Lead
Family Lead
Marital Lead_No
Family Lead_No

Each client will be classified. Then when a classification is given another
form with a questionnaire will be filled out based on their classification.
These forms differ based on the classification they contain questions which
should be answered YES/NO. And if yes, a explanation field must be given.
So basically I see possibly one table with the main client information in it
and a table for each classification. Can anyone help?

Storing data - classifications, say - in a table name is a Very Bad
Idea, and it's not how relational databases work.

I'd say you need a different table for each kind of Entity. A Client
is an Entity; a classification is another kind of Entity; a Document
is an Entity. You might want tables like:

Clients
ClientID <Primary Key, autonumber or other datatype>
LastName
FirstName
<other bio information>

Attorneys
AttorneyID
LastName
FirstName
BusinessName
<other contact info>

Classifications
Classification <Text, Primary Key>

ClientClassifications
ClientID <link to Clients, who's being classified>
Classification <link to Classifications>
Explanation

You could use a Form based on Clients, with a subform based on
ClientClassifications, and enter the classification using a combo box
based on the Classifications table; you'ld have the Explanation field
right there.

John W. Vinson[MVP]

 

[cs_vision]

Thanks so much!

Hi cs_vision,

replies in oreder below;


Putting all of these in 1 table assumes that each attorney will only have
one Client, one document & 1 vault and vice-versa for each field, If this is
not the case, then you want to think about separating them into their own
tables.
You need to look through your data and see where you will have one-to-one
relationships (usually kept in 1 table), one-to-many relationships the one
side forming the parent table and the many the child side table and finally
many-to-many relationships that require 3 tables - two one-to-many and one
link table many-to-one (twice).


These classifications appear to be a prime example of many-to-many
relationships, in that each client will probably have many classifications,
and the same classifications will be used for many clients, thus 3 table to
link client and classification is probably the way to go.

A possibly set of tables and fields is;

tblAttorney
AtttorneyID
Attorney Name
Other Attorney ONLY related Info

tblClient
ClientID
Client Name & all other Client Info
AttorneyID <- foreign key to primary attorney table (assumes each client
only ever has one attorney, otherwise you will need another link table
instead see * below)

tblDocument
DocumentID
Document Name
Document Date
Other Document only info

tblClassification
ClassificationID
Classification Name <- Marital Lead, Family Lead etc as listed as records
Not fields
other Classification related descriptions etc.

tblLinkClient
AttorneyID
ClientID <- * use if Attorney & Client changes with document
DocumentID

tblLinkClassification
ClassificationID
ClientID

Hopefully you begin to see there are numerous ways of placing the data in a
normalised database, and they depend entirely on relationships. Work through
your data and see where relationships exist, then split them into relevant
tables, and join them on a unique key (often ID number), when you come
accross many-to-many relationships resolve the, with a 3rd link table.

Sort out the data relationships first, then create the tables before even
considering form and report designs, and how to bring the information back
together again.

hope that helps and good luck!

TonyT..

 

[mscertified]

Personally, I think if your employer is expecting you to develop an Access
application, then they should provide you with some training. You cannot be
expected to develop an application if you have no knowledge of the product.
We can help with specific problems if you get stuck but we cannot develop it
for you.

Dorian

 

[cs_vision]

One more question, why a separate table for Documents? Each document is tied
to a client.

 

[TonyT]

Hi again cs_vision,

Can each Client ever have more than one document? ie will there ever be
repeat clients? if so in time one client will have many documents, hence the
table split.

Nothing worse than thinking in terms of a single action happening, creating
a database for it only to realise later the same action may happen again and
having no where to store the repeat info or duplicating clients and not being
able to find which *copy* of the clent to look for!

TonyT..