Case Magmt App

Thank you, in advance for your help. I need to determine the best
design to implement a case management system. This is my first real
attempt at application design.

This case management system will track a case through the following
steps.
Creation - Assignment (to case worker)-Research -
Decision-Corrospondence-
-Adjustment-Closing.

A case will be created on the system and then it will be assigned to a
case worker to research it . After the research stage a decision will
be made on the status of the case. Depending upon the Decision
corrospondence wil be created. After that the account will be adjusted
or closed.

My idea was to log the case in the case table and and pull in the
relevat information from
the other tables. I would like to see which employee has what case and
there backlog.

I would like some guidence on the relationships and the overall
approach. Any feedback will be appreciated.

I have the following tables:

CASE:

CaseID
Title
Opened By
Opened Date
Assigned To
Assigned To Date
Research
Research Date
Decision
Correspondence
Correspondence Date
Adjustment
Adjustment Date
Closing
Closing Date


EMPLOYEE:

EmpID
Emp First Name
Emp Last Name
Emp Email

CLIENT

ClientID
CompanyName
Company FEIN
Company permit number
Contact First Name
Contact Last Name
Contact SSN
Contact Spuse SSN

TYPE (of case)
Contention
Aggrement
Hearing


CATEGORY (labor hrs)
under 50 hrs
50 to 200 hrs
200 to 1000hrs
over 1000hrs


thanks again for all your help

This case management system will track a case through the following
steps.
Creation - Assignment (to case worker)-Research -
Decision-Corrospondence-
-Adjustment-Closing.

A question to ask during the planning stages, is whether any of these steps
could be repeated for any particular case. Could there be more than one
research, decision, correspondence, adjustment? If there is any chance of
that, then it is better to plan for that from the beginning. You could have
a table that could identify, for instance, multiple correspondence concerning
a particular case, referencing back to your CaseID.

A case will be created on the system and then it will be assigned to a
case worker to research it . After the research stage a decision will
be made on the status of the case. Depending upon the Decision
corrospondence wil be created. After that the account will be adjusted
or closed.

My idea was to log the case in the case table and and pull in the
relevat information from
the other tables. I would like to see which employee has what case and
there backlog.

I would like some guidence on the relationships and the overall
approach. Any feedback will be appreciated.

I have the following tables:

CASE:

CaseID
Title
Opened By
Opened Date
Assigned To
Assigned To Date
Research
Research Date
Decision
Correspondence
Correspondence Date
Adjustment
Adjustment Date
Closing
Closing Date

You should include ClientID in your Case table. You need to know which
client goes with which case. Same with Type (of case), and Category. Use
the primary keys in the other tables as your foreign keys in this table.

EMPLOYEE:

EmpID
Emp First Name
Emp Last Name
Emp Email

CLIENT

ClientID
CompanyName
Company FEIN
Company permit number
Contact First Name
Contact Last Name
Contact SSN
Contact Spouse SSN

TYPE (of case)
Contention
Aggrement
Hearing

You should make this a table, CaseType, and have a primary key. I would
structure it a little different.

tbl_TypeCase
TypeCaseID (PK)
TypeCaseText

CATEGORY (labor hrs)
under 50 hrs
50 to 200 hrs
200 to 1000hrs
over 1000hrs

You should make this a table, Category, and have a primary key. I would
structure it a little different:

tbl_Category
CategoryID (PK)
CategoryHours (then use this field (text) to designate the different hour
spans)

Thank you for the excellent reply. You are correct in that there could
be more than one
research, decision, correspondence, adjustment stage . So how would I
handle something like this??

ALso this is what I gleaned form your reply.

tbl_CASE:
CaseID
CLIENT_ID
Title
Opened By
Opened Date
Assigned To
Assigned To Date
Research
Research Date
Decision
Correspondence
Correspondence Date
Adjustment
Adjustment Date
Closing
Closing Date

tbl_EMPLOYEE:
EmpID
Emp First Name
Emp Last Name
Emp Email

tbl_CLIENT
ClientID
CompanyName
Company FEIN
Company permit number
Contact First Name
Contact Last Name
Contact SSN
Contact Spuse SSN


tbl_TypeCase
TypeCaseID (PK)
TypeCaseText

tbl_Category
CategoryID (PK)
CategoryHours

Could you please guide me on what type of relationships these tables
should have. thanks again for all your help and time.

You are correct in that there could be more than one
research, decision, correspondence, adjustment stage . So how would I
handle something like this??

For example, you have more than one correspondence concerning a case. Set
up a table for correspondence, that includes a foreign key to link over to
the case table.

tbl_Correspondences
CorrespondenceID (PK)
CaseID (FK)
CorrespondenceInfo (this could be a variety of fields, which would clarify
what the correspondence is, such as date, topic, originator of
correspondence, etc.)

One of the advantages of pulling this into a separate table, is that you can
put quite a variety of information that you might not otherwise have placed
into your main table. This could also be a legal advantage, in that if you
are audited, you have an easily accessed record of what has happened.

You then create similar tables about research, decision, and adjustment.
They should each have their own primary key, with a foreign key of CaseID to
link over to the Case table. Each of these tables will have their own fields
for date and whatever is pertinent to that topic.

Since you will be using the CaseID in these peripheral tables, you no longer
need correspondence fields, etc, in your Case Table. However, you should
have the category and type tables linked to the Cast table.

tbl_CASE:
CaseID (PK)
ClientID (FK)
Title
TypeCaseID (FK)
CategoryID (FK)
Opened By (I assume this is linked to EmpID)
Opened Date
Assigned To (again, linked to EmpID)
Assigned To Date
Decision
Closing
Closing Date

tbl_EMPLOYEE:
EmpID
Emp First Name
Emp Last Name
Emp Email

tbl_CLIENT
ClientID
CompanyName
Company FEIN
Company permit number
Contact First Name
Contact Last Name
Contact SSN
Contact Spouse SSN

tbl_TypeCase
TypeCaseID (PK)
TypeCaseText

tbl_Category
CategoryID (PK)
CategoryHours

After you make all of the tables, open the relationships window and connect
from primary keys to foreign keys (most will have the same name). This is a
lot to do, but it will give you a lot of flexibility when you start entering
information. Just start by making sure all of your tables can connect
together.

Thank you again, let me recap one more time.
So have the following tables.


tbl_CASE:
CaseID (PK)
ClientID (FK)
TypeCaseID (FK)
CategoryID (FK)
DecisionID(FK)
EMPID(FK)
Stausid(FK)
Title
Opened By ( linked to EmpID)
Opened Date
Assigned To ( linked to EmpID)
Assigned To Date
Closing
Closing Date

tbl_Decision
DecisionID(PK)
CaseID (FK)
DecisionInfo

tbl_Correspondences
CorrespondenceID (PK)
CaseID (FK)
CorrespondenceInfo

tbl_Research
ResearchID(PK)
CaseID (FK)
ResearchInfo

tbl_EMPLOYEE:
EmpID(PK)
CaseID (FK) ?????
Emp First Name
Emp Last Name
Emp Email

tbl_status
statusID(PK)
CaseID (FK) ??????
status
statustext

tbl_CLIENT
ClientID(PK)
CompanyName
Company FEIN
Company permit number
Contact First Name
Contact Last Name
Contact SSN
Contact Spouse SSN

tbl_TypeCase
TypeCaseID (PK)
TypeCaseText

tbl_Category
CategoryID (PK)
CategoryHours

Can you please guide me a little more on how will I repeat the steps if
I have to revisit any of the steps in the workflow.

Can you also please look at the PK and FK keys.

Also I would like to have an audit history as you mentioned any
suggestions on this too please.

thanks again for all your insight,