Fight for civil liberties, help the ACLU!!! DB Design

[Jake M via AccessMonster.com]

hI just started working for the ACLU. To organize our cases, we need to
create a database. The process works as follows: We intake many phone calls
and gather general info (name, addr, nature of claims etc.). We dispose of
the majority of the phone calls by refering them elsewhere. We then evaluate
few seemingly worthy complaints and send some of them to be approved for
representation by our legal committee. If legal committee approves a matter,
it becomes an open case and gets a case number. When we are done with the
case, it becomes a closed case.

Now is the question: How should we design the db? Should there be a table for
initial intakes only and a separate table for cases? How do I link them?
intake_number will become the unique id throughout its life at the
organization. Or should there two tables: one for intitial intakes and one
for the ontakes that became cases? How to I links them etc... Thank you
all for your responses and suggestions!

 

[Rick B]

I don't think Access is capable of holding a list of all the lawsuits the
ACLU has at any given time.

 

[Jake M via AccessMonster.com]

We are just a branch of ACLU and cover half of a state, and access is the
only possible solution now.

I don't think Access is capable of holding a list of all the lawsuits the
ACLU has at any given time.

hI just started working for the ACLU. To organize our cases, we need to
create a database. The process works as follows: We intake many phone calls

[quoted text clipped - 11 lines]

for the ontakes that became cases? How to I links them etc... Thank you
all for your responses and suggestions!

 

[Rick B]

I was being facetious.

I think the ACLU wastes a great deal of time and money fighting cases that
should not be fought. Fighting for atheists who were offended because
someone said "God" near them, or criminals who didn't get a comfy enough
jail cell, or some pervert who thinks it is his civil right to molest a
pre-pubescent boy (NAMBLA) is just not my idea of a worthwhile cause.

--
Rick B

We are just a branch of ACLU and cover half of a state, and access is the
only possible solution now.

I don't think Access is capable of holding a list of all the lawsuits the
ACLU has at any given time.

hI just started working for the ACLU. To organize our cases, we need to
create a database. The process works as follows: We intake many phone

calls
[quoted text clipped - 11 lines]

for the ontakes that became cases? How to I links them etc... Thank you
all for your responses and suggestions!

 

[Hermawih]

1. Person Information should not mix with complaint .
One Person can have many complaints .
2. Complaint and case :
one complaint will have zero case or only one case .

3. Tables :
Person (PersonID,Name,Addr,Phone ....)
Complaint(ComplaintID,PersonID,nature , status:1=Ok;2=Stop )
Case(CaseID,RepresentativeID,ComplaintID ... )

Person.PersonID -------->Complaint.PersonID
(One person can have many complaints)
Person.personID -------->case.RepresentativeID
(One Person can represent many cases)

Connection between Complaint.complaintID and case.complaintID is just
like lookup table which means that do not have to create it in the
relationship diagram . Instead , when inputing case on the form , create
combo box based on the table complaint with status = 1 (complaint which will
become a case )

 

[Jake M via AccessMonster.com]

I think there are a lot of misconceptions about the ACLU. As I am starting to
find out, we only take very, very few cases of all the complaints that come
in. Anyways, this is not the place to discuss ACLU's policies, but I would
appreciate substantive suggestions regarding the db design. Thank you.

I was being facetious.

I think the ACLU wastes a great deal of time and money fighting cases that
should not be fought. Fighting for atheists who were offended because
someone said "God" near them, or criminals who didn't get a comfy enough
jail cell, or some pervert who thinks it is his civil right to molest a
pre-pubescent boy (NAMBLA) is just not my idea of a worthwhile cause.

We are just a branch of ACLU and cover half of a state, and access is the
only possible solution now.

[quoted text clipped - 7 lines]

 

[Jake M via AccessMonster.com]

Thank you! I will look into your suggestions.

1. Person Information should not mix with complaint .
One Person can have many complaints .
2. Complaint and case :
one complaint will have zero case or only one case .

3. Tables :
Person (PersonID,Name,Addr,Phone ....)
Complaint(ComplaintID,PersonID,nature , status:1=Ok;2=Stop )
Case(CaseID,RepresentativeID,ComplaintID ... )

Person.PersonID -------->Complaint.PersonID
(One person can have many complaints)
Person.personID -------->case.RepresentativeID
(One Person can represent many cases)

Connection between Complaint.complaintID and case.complaintID is just
like lookup table which means that do not have to create it in the
relationship diagram . Instead , when inputing case on the form , create
combo box based on the table complaint with status = 1 (complaint which will
become a case )

hI just started working for the ACLU. To organize our cases, we need to
create a database. The process works as follows: We intake many phone calls

[quoted text clipped - 11 lines]

for the ontakes that became cases? How to I links them etc... Thank you
all for your responses and suggestions!

 

[Graham R Seach]

<<...this is not the place to discuss ACLU's policies>>
I agree, but it is also not the place to make political statements ("Fight
for civil liberties, help the ACLU!!!")

I have no idea who the ACLU are, but I would be more inclined to help had
you not begun with such a Subject line. I very nearly bypassed it, thinking
it was just another off-topic political rant.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
(Currently in Japan)
---------------------------

I think there are a lot of misconceptions about the ACLU. As I am starting
to
find out, we only take very, very few cases of all the complaints that
come
in. Anyways, this is not the place to discuss ACLU's policies, but I would
appreciate substantive suggestions regarding the db design. Thank you.

I was being facetious.

I think the ACLU wastes a great deal of time and money fighting cases that
should not be fought. Fighting for atheists who were offended because
someone said "God" near them, or criminals who didn't get a comfy enough
jail cell, or some pervert who thinks it is his civil right to molest a
pre-pubescent boy (NAMBLA) is just not my idea of a worthwhile cause.

We are just a branch of ACLU and cover half of a state, and access is
the
only possible solution now.

[quoted text clipped - 7 lines]

for the ontakes that became cases? How to I links them etc...
Thank you
all for your responses and suggestions!

 

[Paul Overway]

Hey Rick... your characterizations of the ACLU are FAR from factual. Put
the politics aside or shut up.

--
Paul Overway
Logico Solutions, LLC
www.logico-solutions.com

I was being facetious.

I think the ACLU wastes a great deal of time and money fighting cases that
should not be fought. Fighting for atheists who were offended because
someone said "God" near them, or criminals who didn't get a comfy enough
jail cell, or some pervert who thinks it is his civil right to molest a
pre-pubescent boy (NAMBLA) is just not my idea of a worthwhile cause.

--
Rick B

We are just a branch of ACLU and cover half of a state, and access is the
only possible solution now.

I don't think Access is capable of holding a list of all the lawsuits
the
ACLU has at any given time.

hI just started working for the ACLU. To organize our cases, we need
to
create a database. The process works as follows: We intake many phone calls
[quoted text clipped - 11 lines]
for the ontakes that became cases? How to I links them etc... Thank you
all for your responses and suggestions!

 

[Paul Overway]

Jake:

FWIW, given some people's misconceptions about your organization, you may
find more help by describing your problem in a generic, non-identifying way.
Putting that aside...

The intake part seems fairly straightforward. You'll likely need:

1. A table form entity information (i.e., names, and other entity attributes
like gender, age, etc...if that type of info is useful)
2. A table for entity addresses
3. A table for entity phone numbers
4. A table for entity email addresses?
5. A table used as a case or contact log

Other parts of the database could be somewhat more complex, depending on
specific needs.

1. Could a client be party to more than one case?
2. Could a case have multiple clients?
3. Do you keep contact info concerning defendants (assuming your clients
are always the plaintiff)?
4. Could a case have multiple defendants?
5. Could multiple attorneys be assigned to the same case?
6. Is it important to record information concerning the court and judge the
case was heard in? What about appeals?

Etc.. Hopefully, this stirs some thinking.

 

[Hermawih]

Yes I agree .

Database design for big organization should be like that .
Maybe creating two or more databases will be more efficient :
A. master database (People , Communication , Addresses ..)
B. transaction database .
etc

 

[Jake M via AccessMonster.com]

Thank you all for all of the responses!

hI just started working for the ACLU. To organize our cases, we need to
create a database. The process works as follows: We intake many phone calls
and gather general info (name, addr, nature of claims etc.). We dispose of
the majority of the phone calls by refering them elsewhere. We then evaluate
few seemingly worthy complaints and send some of them to be approved for
representation by our legal committee. If legal committee approves a matter,
it becomes an open case and gets a case number. When we are done with the
case, it becomes a closed case.

Now is the question: How should we design the db? Should there be a table for
initial intakes only and a separate table for cases? How do I link them?
intake_number will become the unique id throughout its life at the
organization. Or should there two tables: one for intitial intakes and one
for the ontakes that became cases? How to I links them etc... Thank you
all for your responses and suggestions!

 

[Joel Whitehouse]

Hey Rick... your characterizations of the ACLU are FAR from factual.

Paul,

I think "factual" is actually a fairly accurate description of Rick's
statements - especially on their defense of NAMBLA:
http://wired-vig.wired.com/news/politics/0,1283,38540,00.html

They also seem to have a pick and choose policy on the sexual
orientations that they will defend... If you get in trouble for wearing
a "Gay Pride" tee shirt, then the ACLU is your best friend - but get
caught at school wearing a "Straight Pride" tee and you have to find
some obscure Christian law firm to cover your back...

 

[Paul Overway]

Not at all factual. The lawsuit wasn't about defending some pervert's
behavior in molesting someone, as Rick implied. The lawsuit was about free
speech....maybe you ought to read your citation again a little more
carefully.

NAMBLA certainly isn't an organization I'd contribute any time or money to,
disgusting as it is, but they have rights just the same as everyone else.
You might view matters a little differently if it were your rights being
abridged. As for Gay vs Straight Pride, I'm not familiar with the case, and
you've failed to cite any article.

In any case, ACLU is neutral in a matters of sex, politics, religion, etc.
ACLU defends constitutional rights...period. Do some reading and you'll
find their defense work covers a very wide range of issues. You might want
to learn a little more about what they actually do and are involved in
before passing judgment....especially considering the fact that all of the
issues concern YOUR constitutional rights. Defend them or you'll lose them.